AnsweredAssumed Answered

Portal and Duplicates

Question asked by Wicktor on Feb 8, 2017
Latest reply on Feb 9, 2017 by Wicktor

I have been asked to take over a retiring colleague and to solve some problems of a previously developed solution.

The solution is complex but this particular Table is a big issue for me.

 

A PRODUCTS table is structured as follows:

Client_Name

Date

Product1_name

Product1_details1 (list of values)

Product1_details2 (list of values)

Product1_details3 (list of values)

Product1_key (text calculated: Product1 & Details1 & Details2 & Details3)

Product2_name

Product2_details1 (list of values)

Product2_details2 (list of values)

Product2_details3 (list of values)

Product2_key (text calculated: Product2 & Details1 & Details2 & Details3)

 

The Table has some 300.000 records and a significant number of duplicates, i.e. different Clients may have purchased many Product1 and/or Product2 with various values in each of Details fields.

 

Of course my first observation was that the Table is not well structured and the funny aspect is that Product1 and Product2 might also have the same name and same details.

 

I have been asked to produce a portal which shows only unique values of Products and related Details.

I have already tagged duplicates adding a Serial Number as PrimaryKey, two new self join relationship.

PRODUCTS <-> PRODUCTS_key1

PRODUCTS <-> PRODUCTS_key2

and a new field If (PrimaryKey = PRODUCTS_key1::PrimaryKey or PrimaryKey = PRODUCTS_key2::PrimaryKey ; ""; "Duplicate")

I think I am “almost there” but I miss how to show a unique list of Product/Detail1/Detail2/Detail3

 

Before suggesting to totally destroy this Table and create a new one with correct structure I wonder if there is a faster solution to the problem.

 

Thanks for any help,

Victor

Outcomes