How to count the Total from Query Result

[rukk]

Hi all
I need a small help in my Query. I am running a query that brings results from 3 tables.
Eg:
For ProductID 1 there are more details in the other two tables. So, when i run this query i am getting the ProductID 1 around 200 times.
I want to show the total of Products by counting the distinct ProductId's on the form. How to count them from query result.

 

[sucoyant]

I'm a bit confused. Are you saying that you want to show the total number of all products? Example:

Products
----
350

Or do you want it to look like this?

ProdID Count
------ -------
Product1 50
Product2 100

 

[SteveR77]

Based on the fact that you said you wanted a count by unique ProductID. It sounds like you are looking for the Group By Option for your query.

The SQL statement would look something like this -

SELECT Table1.ProductID, Count(Table1.ProductID) AS CountOfProductID
FROM Table1
GROUP BY Table1.ProductID;


The Result will give you -

ProductID CountofProductID

123 6
456 1
789 10

 

[dhookom]

I too am a bit confused. I think I know what you are getting at: for instance if you have a table of classes being offered at a school and a table of student registrations (linked to classes by ClassID), you want to count the number of unique classes, not the total number of students enrolled in all classes.

I would suggest that the solution depends on if you want the results displayed in a form or report. If it's for a report, then my answer would differ significantly from other answers.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[rukk]

Hello,
"sucoyant" as you said that i need to get the total as

Products
--------
350

Hello, "SteveR77"
How can i can write that SQL statement because i want to count the ProductID from a query result not from the table.Because in my table i do have many more ProductID's. But i need to get the total of those ProductID which matches with my query criteria.

Hello "dhookom"
As you said i want to get the Total number of unique classes. Let me explain this a bit wide.
I am creating a form with combo boxes to give the user choice to run a query to match that criteria. And the rusult will be displayed as query result(i.e. DataSheetView). At the top of the form i need to get the total no of unique Products that are in the query result.

Hope now you guys have a little more idea about this.

 

[dhookom]

rukk,
If you display the results in a datasheet view, then how/where do you find "the top of the form"?
I assume you have a query that is filtered by your form/control selections. Use the query as the basis for a totals query that groups by only the ProductID. Then, you can use DCount():
=DCount("*","[qtotMyTotalsQuery]&quot

Duane
MS Access MVP
Find out how to get great answers faq219-2884.