Solved Create a query to find number of items in a table

[Sepp1945]

I have a table (see below) which has field "Product_ID", ( numbers 1 to 7).
I would like to create a query to give me in seven field the count of each product.
I tried but failed.
Thank you
Sepp

 

[theDBguy]

Why 7 fields? Try a crosstab query.

 

[Sepp1945]

Thank you

 

[theDBguy]

Thank you

You're welcome.

PS. Welcome to AWF!

 

[Sepp1945]

Thank you

Sorry does not work

 

[arnelgp]

you can join the product table (if you have) and do as advised in post#2.
this one does not have a product table:

TRANSFORM Count(tblPayments.Product_ID) AS CountOfProduct_ID
SELECT "Total Count" AS Remarks
FROM tblPayments
GROUP BY "Total Count"
PIVOT tblPayments.Product_ID In (1,2,3,4,5,6,7);

 

[Sepp1945]

This is perfect just what i needed,

Many, many thanks
Sepp

 

[theDBguy]

This is perfect just what i needed,

Many, many thanks
Sepp

Glad to hear you got the crosstab query to work. Thanks to @arnelgp for the assist.

Good luck with your project.

 

[Pat Hartman]

Sorry does not work

Welcome but just FYI and to enable you to actually get help in the future, that is a meaningless statement. If you want us to help you, you need to show us what YOU did. Just saying our suggestion didn't work is unhelpful since as you can see, the crosstab query WAS the correct solution. You just didn't build it correctly.

When you can't write SQL, then use the QBE to build it using the wizards and the point and click at least for the basics. You may need to later switch to SQL view to tweak it if that is awkward with the QBE.