Count function is duplicating data 1

[JwithaL]

I am creating a query to show how many customers have bought a part number. However when I run it, it counts every transaction.

So if only 4 different companies bought the part... The query says 25 because the part has been bought 25 different times and there is a company name for each of those sales.

Is there any way to do this

 

[TheAceMan1]

How are ya JwithaL . . . . .

Post the SQL of the query!

See Ya! . . . . . .

 

[JwithaL]

SELECT [Product Categories].[Item Number], Count(Customers.[Customer Name]) AS [CountOfCustomer Name]
FROM [Product Categories] INNER JOIN (Customers INNER JOIN [Sales History] ON Customers.[Customer Number] = [Sales History].[Customer Number]) ON [Product Categories].[Item Number] = [Sales History].[Part Number]
GROUP BY [Product Categories].[Item Number];

 

[TheAceMan1]

JwithaL . . . . .

Your SQL has no criteria to seperate out a specific part number, so add some:

[blue]SELECT [Product Categories].[Item Number], Count(Customers.[Customer Name]) AS [CountOfCustomer Name]
FROM [Product Categories] INNER JOIN (Customers INNER JOIN [Sales History] ON Customers.[Customer Number] = [Sales History].[Customer Number]) ON [Product Categories].[Item Number] = [Sales History].[Part Number]
GROUP BY [Product Categories].[Item Number] [purple]WHERE ([Sales History].[Part Number]=[b]YouTypeInPartNumber[/b]);[/purple][/blue]

Also . . . I query the following join:

[blue]ON [Product Categories].[[purple][b]Item Number[/b][/purple]] = [Sales History].[[purple][b]Part Number[/b][/purple]][/blue]

[purple]Item Numbers[/purple] that equal [purple]Part Numbers[/purple] . . . is this logical to you?

See Ya! . . . . . .

 

[JwithaL]

Hey thank you very much AceMan, I was double checking and I think I made an initial mistake in the first place. Cuz where your solution works I'm not sure if it will work for my use. This query will go into a much larger query/report to display for every part number how many customers bought that specific part number I will attach a different code that is much simpler because I have both customer names and part number in one table. If you could help me out with this it would be much appreciated or anyone else out there in code land.

SELECT [Sales History].[Part Number], Count([Sales History].[Customer Number]) AS [CountOfCustomer Number]
FROM [Sales History]
GROUP BY [Sales History].[Part Number];

 

[PHV]

JetSQL lacks the Count(DISTINCT) aggregate function.
One workaround is to use an embedded view:
SELECT D.[Part Number], Count(*) AS CountOfCustomer
FROM (SELECT DISTINCT [Part Number], [Customer Number] FROM [Sales History]) AS D
GROUP BY D.[Part Number];


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

 

[JwithaL]

I cannot thank you enough for that... Works like a charm. Do you happen to have any reading suggestions to learn little tricks as this

 

[JwithaL]

Is it possible to add a date criteria in this formula. I have been trying to play around with the above code that was given, but everytime I change something and try and save I get a "Invalid bracketing of name 'SELECT DISTINCT [Part Number'.

The field name [Date] is also in the [Sales History] table.

Goal: show count of customers that bought a specific part number since a date to be specified.

 

[PHV]

Double check this:
FROM [highlight]([/highlight]SELECT DISTINCT ... History][highlight])[/highlight] AS D

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

 

[JwithaL]

Where would I add the [Date] field in the code

 

[PHV]

Add a WHERE clause in the embedded view.

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

 

[JwithaL]

I think I got it to work... I will double check it by hand now. Thank you so much for your help.