Query to find the Count (1 Viewer)

JithuAccess

Member
Local time
Today, 07:46
Joined
Mar 3, 2020
Messages
297
Guys,

I am very new to Access. I have a Table called BasicData and 5 fields FName, SName, Country, ZIP and Remarks. I have total about 500 Records In the country Field I have 3 Values USA, Mexico and Canada. I want to create a query which shows How many Records for USA, Canada and Mexico. In my Query I want show like USA : 180 Canada: 120 and Mexico: 200

Could you guys kindly direct me. I have created a query but I am not getting the result which I am looking for.

Thanks in Advance
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! First, you could try using a Totals query. Do you know how to do that? If not, we could try to give you a step-by-step instruction.
 

JithuAccess

Member
Local time
Today, 07:46
Joined
Mar 3, 2020
Messages
297
Hi. Welcome to AWF! First, you could try using a Totals query. Do you know how to do that? If not, we could try to give you a step-by-step instruction.
Thanks a lot for your reply. Could you kindly guide me please
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Try the following steps:
  1. From the Ribbon, select Create > Query Design
  2. From the Show Table dialog screen, highlight your table and click on the Add button, then click Close
  3. From your table, drag the fields you want to display on your query result down to the query grid - one field per column
  4. From the Ribbon > Design tab, click on the Totals button (with Sigma icon)
  5. In the Total: row in the query grid, you can change the Group By into Sum for the columns you want to add
Run the query and adjust as needed.
 

JithuAccess

Member
Local time
Today, 07:46
Joined
Mar 3, 2020
Messages
297
Try the following steps:
  1. From the Ribbon, select Create > Query Design
  2. From the Show Table dialog screen, highlight your table and click on the Add button, then click Close
  3. From your table, drag the fields you want to display on your query result down to the query grid - one field per column
  4. From the Ribbon > Design tab, click on the Totals button (with Sigma icon)
  5. In the Total: row in the query grid, you can change the Group By into Sum for the columns you want to add
Run the query and adjust as needed.
Million thanks for your reply.

I have dragged the fields in to the query and in the Country field (the field which I want to know the total) I changed Group By in to Sum all other fields are Group By.

Unfortunately I am getting an error message like "Data type mismatch in criteria expression"

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Million thanks for your reply.

I have dragged the fields in to the query and in the Country field (the field which I want to know the total) I changed Group By in to Sum all other fields are Group By.

Unfortunately I am getting an error message like "Data type mismatch in criteria expression"

Thanks
That usually means there's an empty value (null) in the table for that field.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Thanks a lot. There I no Null values in the "Country" Field
To help you further, you may have to post a sample copy of your db without sensitive data. (Or at least post some screenshots to show us what you're seeing.)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 28, 2001
Messages
26,999
"Data type mismatch" usually means you have a string where you needed a number OR a number where you needed a string. In other words, be aware of the data type of the field involved in the criteria and match your comparison value type to the field value type.
 

JithuAccess

Member
Local time
Today, 07:46
Joined
Mar 3, 2020
Messages
297
To help you further, you may have to post a sample copy of your db without sensitive data. (Or at least post some screenshots to show us what you're seeing.)

Thanks a lot. I did it in a very stupid method. Created 3 queries for finding the total of each countries. Like put the Criteria as "USA" and saved that Query as "USA Count" and so on then create another query "Total Count" and bring these 3 queries and drag and drop the fields into that "Total Count" and it's working.
 

JithuAccess

Member
Local time
Today, 07:46
Joined
Mar 3, 2020
Messages
297
"Data type mismatch" usually means you have a string where you needed a number OR a number where you needed a string. In other words, be aware of the data type of the field involved in the criteria and match your comparison value type to the field value type.
Thanks a lot
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
Thanks a lot. I did it in a very stupid method. Created 3 queries for finding the total of each countries. Like put the Criteria as "USA" and saved that Query as "USA Count" and so on then create another query "Total Count" and bring these 3 queries and drag and drop the fields into that "Total Count" and it's working.
Hi. That's one way of doing it. I wouldn't call it stupid. Good luck with your project.
 

JithuAccess

Member
Local time
Today, 07:46
Joined
Mar 3, 2020
Messages
297
I have another question, can we give different Record source in a Report. I have 3 Tables without any connection and I want to display a particular fields of these 3 Tables in the Text Boxes of that Report. But Access Report allowing me to select only 1 Record Source. To solve this issues, I have created 3 Sub Reports and give the Record Source of those 3 Tables in these 3 Sub Reports. So just wondering whether there is an easy and simple way to do this.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:46
Joined
Oct 29, 2018
Messages
21,357
I have another question, can we give different Record source in a Report. I have 3 Tables without any connection and I want to display a particular fields of these 3 Tables in the Text Boxes of that Report. But Access Report allowing me to select only 1 Record Source. To solve this issues, I have created 3 Sub Reports and give the Record Source of those 3 Tables in these 3 Sub Reports. So just wondering whether there is an easy and simple way to do this.

Thanks
Hi. Without any relationships between the tables, you have done the next best thing and created separate reports for each table and use them as subreports to display all the info on one report. You would do the same thing if you wanted to display all the data on one form - you would use unrelated subforms. You're doing a good job. Cheers!
 

JithuAccess

Member
Local time
Today, 07:46
Joined
Mar 3, 2020
Messages
297
Hi. Without any relationships between the tables, you have done the next best thing and created separate reports for each table and use them as subreports to display all the info on one report. You would do the same thing if you wanted to display all the data on one form - you would use unrelated subforms. You're doing a good job. Cheers!
Oh! Thanks, I thought that was really a stupid idea. Thanks a lot for your great support Sir
 

isladogs

MVP / VIP
Local time
Today, 13:46
Joined
Jan 14, 2017
Messages
18,186
Hi
Going back to your totals query as described in post #4, change the Group By to Count (not Sum) as you want to count the records for each country.
Your 'stupid method', as you described it, is unnecessary
 

Users who are viewing this thread

Top Bottom