Total Records less Duplicates

[Basshopper]

I have a table

that lists records that would have some duplicated records that I still need in the table. What would a query look like or 2 joined to count the total records less the dupicates in 1 of the fields [Line #] to give me a total number of Line items. This is in reference to a Bill of Material. Trying to get to the basic number of main line item count less the alternative parts.4 fields are required in the query

[customer Id], [assmebly #],

, [Line #]


thanks in advance for your help

 

[PHV]

Something like this ?
SELECT [customer Id], [assmebly #],

, Count([Line #])
FROM (SELECT DISTINCT [customer Id], [assmebly #],

, [Line #] FROM yourTable) D
GROUP BY [customer Id], [assmebly #],

;


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

 

[Basshopper]

what does the D stand for???

FROM yourTable) D

so if I have 10 line # and 3 Line # are dups this code will give me a total records of 8 ???

Line # 1,2,3,3,3,4,5,6,7,9

 

[PHV]

D is an alias for the unnamed query in the FROM clause.
You must have ac2k or above (in fact Jet 4 I think).
Have you tested my suggestion ?

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

 

[Basshopper]

oops doing this in access 97 tried it and getting a error in the from statement

 

[PHV]

doing this in access 97
Create a saved query named, say, qryNoDup:
SELECT DISTINCT [customer Id], [assmebly #],

, [Line #] FROM yourTable;
Then the total records query may be like this:
SELECT [customer Id], [assmebly #],

, Count([Line #])
FROM qryNoDup
GROUP BY [customer Id], [assmebly #],

;


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

 

[Basshopper]

Worked Great thanks for your help