No Duplicates in Make Table Query

[scaifea]

Access 2000:
I have a Make Table query that takes data from two tables.
In essence, I end up with a column named CustomerNumber and one named CustomerName.

I was to dedupe CustomerName, so it only appears once, and potentially halve the size of the resulting table (from 21k records to 10k).

Any ideas how I can do this?

 

[PHV]

What is the actual SQL code of your MakeTable query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scaifea]

SELECT [MPANs by SIC].SupplypointRef, [MPANs by SIC].CustomerName, [MPANs by SIC].Description INTO tblHighValueSICCodes
FROM Figures INNER JOIN [MPANs by SIC] ON Figures.SIC1 = [MPANs by SIC].SIC1
WHERE (((Figures.[avg usage band id])>30) AND ((Figures.[avg spend band id])="m" Or (Figures.[avg spend band id])="n" Or (Figures.[avg spend band id])="o" Or (Figures.[avg spend band id])="p" Or (Figures.[avg spend band id])="q" Or (Figures.[avg spend band id])="r"))
ORDER BY [MPANs by SIC].CustomerName;

 

[PHV]

Which SupplypointRef and/or Description would you keep in the dedup process ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scaifea]

To be honest, I'm not bothered which record I save out of the dupes, I should be able to do some manual consolidation down the line.

Thanks for your help

 

[PHV]

You may try this:
SELECT First([MPANs by SIC].SupplypointRef) AS SupplypointRef
, [MPANs by SIC].CustomerName
, First([MPANs by SIC].Description) AS Description
INTO tblHighValueSICCodes
FROM Figures INNER JOIN [MPANs by SIC] ON Figures.SIC1 = [MPANs by SIC].SIC1
WHERE Figures.[avg usage band id] > 30
AND Figures.[avg spend band id] IN ("m","n","o","p","q","r")
ORDER BY [MPANs by SIC].CustomerName;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scaifea]

Thanks for that ... just got the error ..

You tried to execute a query that does not include the specified expression CustomerName as part of an aggregate function.

... when I tried to execute it!

 

[PHV]

OOps, sorry for the typo:
SELECT First([MPANs by SIC].SupplypointRef) AS SupplypointRef
, [MPANs by SIC].CustomerName
, First([MPANs by SIC].Description) AS Description
INTO tblHighValueSICCodes
FROM Figures INNER JOIN [MPANs by SIC] ON Figures.SIC1 = [MPANs by SIC].SIC1
WHERE Figures.[avg usage band id] > 30
AND Figures.[avg spend band id] IN ("m","n","o","p","q","r")
[!]GROUP BY [MPANs by SIC].CustomerName[/!]
ORDER BY [MPANs by SIC].CustomerName;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scaifea]

Ah brilliant, full KUDOS to you. Many Thanks