Query by distinct personal id (1 Viewer)

amir0914

Registered User.
Local time
Yesterday, 18:22
Joined
May 21, 2018
Messages
151
Hi all, I have two columns in table that first column is personal id and second column is product which they buy to our company. it's sample of the table :

personal id ...... product

....10 .................... A
....10 .................... B
....20 .................... B
....35 .................... A
....10 .................... A
....35 .................... C
....10 .................... A
....20 .................... B
....35 .................... C
....20 .................... B

And I want to create query by same as below :

Personal id ..... Count of A ..... Count of B .... count of C
..... 10 ................ 3 ................. 1 ................. 0
..... 20 ................ 0 ................. 3 ................. 0
..... 35 ................ 1 ................. 0 ................. 2

Can someone give me a way for do this??

Thanks in advanced.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:22
Joined
Oct 29, 2018
Messages
21,358
Hi. Sure, try using a crosstab query.
 

isladogs

MVP / VIP
Local time
Today, 01:22
Joined
Jan 14, 2017
Messages
18,186
Your table should have a primary key field so each record is unique
A simple aggregate query will get you the values with a count:
Code:
SELECT Table1.PersonalID, Count(Table1.Product) AS CountOfProduct
FROM Table1
GROUP BY Table1.PersonalID;

PersonalID CountOfProduct
10 4
20 3
35 3

It won't show the zero values.
To do that you need a crosstab query but that requires a minimum of three fields
Row/Column & Value... so you would need to add a dummy field if its that important to have that exact layout
 

amir0914

Registered User.
Local time
Yesterday, 18:22
Joined
May 21, 2018
Messages
151
Your table should have a primary key field so each record is unique
A simple aggregate query will get you the values with a count:
Code:
SELECT Table1.PersonalID, Count(Table1.Product) AS CountOfProduct
FROM Table1
GROUP BY Table1.PersonalID;



It won't show the zero values.
To do that you need a crosstab query but that requires a minimum of three fields
Row/Column & Value... so you would need to add a dummy field if its that important to have that exact layout

Hi, Thank you for reply, yes my table has a unique primary key, but your code give total count of product whereas I want to get count of each product in the field for every one,
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:22
Joined
Oct 29, 2018
Messages
21,358
Thanks, what's crosstab? I don't know access a lot.
Here's an example of an SQL statement you could try.

Code:
TRANSFORM Count(TableName.product) AS CountOfproduct
SELECT TableName.[personal id]
FROM TableName
GROUP BY TableName.[personal id]
PIVOT TableName.product;
Just make sure you use the correct names for your table and fields.
 

isladogs

MVP / VIP
Local time
Today, 01:22
Joined
Jan 14, 2017
Messages
18,186
Hi, Thank you for reply, yes my table has a unique primary key, but your code give total count of product whereas I want to get count of each product in the field for every one,

Yes I understood that but as already stated you need a minimum of three fields to run a crosstab query. Your data only has two fields. So it needs another field
 

amir0914

Registered User.
Local time
Yesterday, 18:22
Joined
May 21, 2018
Messages
151
Here's an example of an SQL statement you could try.

Code:
TRANSFORM Count(TableName.product) AS CountOfproduct
SELECT TableName.[personal id]
FROM TableName
GROUP BY TableName.[personal id]
PIVOT TableName.product;
Just make sure you use the correct names for your table and fields.

Thank you so much for crossing tab , I learned cross tab and did it myself, it was very easy and useful. :cool:
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:22
Joined
Oct 29, 2018
Messages
21,358
Thank you so much for crossing tab , I learned cross tab and did it myself, it was very easy and useful. :cool:
Hi. You're welcome. Glad to hear you got it sorted out. Colin and I were happy to assist. Good luck with your project.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:22
Joined
May 7, 2009
Messages
19,169
your query (query1).
 

Attachments

  • crossTabSample.zip
    18.7 KB · Views: 58
  • Capture_2019_08_15_504_2.png
    Capture_2019_08_15_504_2.png
    78.9 KB · Views: 56
  • Capture_2019_08_15_988.png
    Capture_2019_08_15_988.png
    64.4 KB · Views: 56

Users who are viewing this thread

Top Bottom