Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-14-2019, 07:56 AM   #1
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 27
Thanks: 27
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Query by distinct personal id

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.

amir0914 is offline   Reply With Quote
Old 08-14-2019, 07:58 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,117
Thanks: 45
Thanked 964 Times in 946 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query by distinct personal id

Hi. Sure, try using a crosstab query.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
amir0914 (08-14-2019)
Old 08-14-2019, 08:06 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query by distinct personal id

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;
Quote:
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
amir0914 (08-14-2019)
Old 08-14-2019, 12:29 PM   #4
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 27
Thanks: 27
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Re: Query by distinct personal id

Quote:
Originally Posted by theDBguy View Post
Hi. Sure, try using a crosstab query.
Thanks, what's crosstab? I don't know access a lot.
amir0914 is offline   Reply With Quote
Old 08-14-2019, 12:39 PM   #5
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 27
Thanks: 27
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Re: Query by distinct personal id

Quote:
Originally Posted by isladogs View Post
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,
amir0914 is offline   Reply With Quote
Old 08-14-2019, 12:40 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,117
Thanks: 45
Thanked 964 Times in 946 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query by distinct personal id

Quote:
Originally Posted by amir0914 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
amir0914 (08-14-2019)
Old 08-14-2019, 12:47 PM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query by distinct personal id

Quote:
Originally Posted by amir0914 View Post
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
amir0914 (08-14-2019)
Old 08-14-2019, 07:43 PM   #8
amir0914
Newly Registered User
 
Join Date: May 2018
Posts: 27
Thanks: 27
Thanked 0 Times in 0 Posts
amir0914 is on a distinguished road
Re: Query by distinct personal id

Quote:
Originally Posted by theDBguy View Post
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.
amir0914 is offline   Reply With Quote
Old 08-14-2019, 07:47 PM   #9
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,117
Thanks: 45
Thanked 964 Times in 946 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query by distinct personal id

Quote:
Originally Posted by amir0914 View Post
Thank you so much for crossing tab , I learned cross tab and did it myself, it was very easy and useful.
Hi. You're welcome. Glad to hear you got it sorted out. Colin and I were happy to assist. Good luck with your project.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-14-2019, 07:48 PM   #10
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,967
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query by distinct personal id

your query (query1).
Attached Images
File Type: png Capture_2019_08_15_504_2.png (78.9 KB, 6 views)
File Type: png Capture_2019_08_15_988.png (64.4 KB, 6 views)
Attached Files
File Type: zip crossTabSample.zip (18.7 KB, 3 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] A query with Distinct against a SQL view with Distinct truncates string Rx_ SQL Server 2 05-05-2015 08:13 AM
Linking DISTINCT & NON DISTINCT Queries vdanelia Reports 5 10-30-2011 11:14 AM
Select DISTINCT columns along with a NON-DISTINCT column losdamianos Queries 13 12-13-2010 01:27 AM
need distinct records of whle table but distinct on one field ukdata Queries 1 09-16-2006 11:22 PM
Distinct rows from non-distinct data? eaymmus Queries 1 10-11-2005 05:42 AM




All times are GMT -8. The time now is 01:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World