Combine fields from one table without duplicates

[KevinSlater]

Hello,

In access 2003 i have an external Oracle table named EXT-COM_EXTRA_DATA_FIELDS
in this table I have the following fields: COM_DATA_KEY, COM_DATA_FIELD_NO, COM_DATA_VALUE

From this I have created a query named: LOOKUP-BARCODES-10 this looks at the above table by filtering the
the records on the field: COM_DATA_FIELD_NO where this =10.

Also I have created a second query named: LOOKUP-BARCODES-20 this looks at the above table by filtering the records on the
field: COM_DATA_FIELD_NO where this =20

Below is sample data in the table:

COM_DATA_KEY; COM_DATA_FIELD_NO; COM_DATA_VALUE
009A/s 10 03010303165001
009A/s 20 04350309115002
010B/P 10 05150909116001
010B/P 20 06150909116002
011C/X 10 07281809217003
011C/X 20 N/A


The problem is i would like to combine the numbers in the COM_DATA_VALUE field for the same product (ie the same COM_DATA_KEY
record) on the same rows without any duplicate records as shown below:

COM_DATA_KEY; COM_DATA_VALUE; COM_DATA_VALUE (2)
009A/s 03010303165001 04350309115002
010B/P 05150909116001 06150909116002
011C/X 07281809217003 N/A

As you can see from above some records only have data in one COM_DATA_VALUE (like 011C/x) where COM_DATA_FIELD_NO = 10 but no
data for the same product where the COM_DATA_VALUE = 20.

Im thinking maybe i can create a new join query that looks at the above two query’s and combines the values in the COM_DATA_VALUE for
the same product onto the same record row but im not sure how to go about this without creating duplicates & its been some time since ive done joined queries. Any help would be great?

ive attached a cut down version of the database with the table imported from oracle rather than being linked to oracle if this helps.

Thanks

Kevin

 

[Jon K]

Try this query:-

SELECT [LOOKUP-BARCODES-10].COM_DATA_KEY, [LOOKUP-BARCODES-10].COM_DATA_VALUE, [LOOKUP-BARCODES-20].COM_DATA_VALUE AS [COM_DATA_VALUE (2)]
FROM [LOOKUP-BARCODES-10] LEFT JOIN [LOOKUP-BARCODES-20] ON [LOOKUP-BARCODES-10].COM_DATA_KEY = [LOOKUP-BARCODES-20].COM_DATA_KEY;


Since the Product Key in your [LOOKUP-BARCODES-10] is unique without duplicates and the Product Key in [LOOKUP-BARCODES-20] is a subset of the Product Key in [LOOKUP-BARCODES-10] without duplicates, the JOIN in the above query will not produce duplicates.
.

 

[KevinSlater]

Hi Jon,

Thats great, this works a treat & thanks for some explanation.

Thanks!

Kevin