Software

Ask something
Discussion Locked

Counting in Criteria

0 helpful
13 replies

by borginva

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a query where:
Field is Name
Table is Org

And I want to count the total of all sites named SITE1.

So I have:
Total is Count
Criteria is LIKE "*SITE1*"

I get an error.

What am I doing wrong?

My ultimate goal is to have this query count and give me the total of sites with other names. I need it to give me the total number of sites named SITE, SITE2, SITE3. Not the total of ALL sites, but the total of each named site.

Thanks.

Also, I do not know much of Access.

13 replies

by dklippert

Use two duplicate Fields. In the first SITE field use Like "*Site1*" This will bring up all entries that contain Site1 as part of the string. In the second SITE field just use Count as the Total without any criteria.

by borginva

That counted each site named SITE1, SITE1a, SITE1b, etc. But I need it to count *SITE1* as stated above.

by dklippert

The * is a wild card. If you only want SITE1 entries, don't use it.
Just enter SITE1 in the criteria box and run the Query.

by borginva

If I use just SITE1, using what you recommended above, it will count only SITE1. It will not add into the total SITE1A, SITE1B, etc. Using the * (wildcard) it will count each individual site (SITE1 - 15, SITE1A - 4, SITE1B - 9, etc.
I need it to give me the total of every site with SITE1 in the name. I need: SITE1 - 67. The total of them all.
If I am not making it understandable, please let me know and I will try to give me information.

by borginva

What I am trying to do ultimately is have a report that will show me the total number of sites with that name:
# of sites
SITE1 7
SITE2 56
SITE3 25

And SITE1 (and the other SITE#) needs to include any site with SITE1 in it:
SITE1a, SITE1b, SITE1, BosSITE1, etc.

To make a creaer example, if I wanted to count all sites with Microsoft in it, I would want the figure of 3 as the total of sites with Microsoft in it:
The Microsoft Corp, Microsoft Co., Sam's Microsoft Stop.

by jim94539

Set up your query for summation (in the Access query design window, turn on the button with the summation sign). Set two query fields with the same table field "Name" for the query fields. Set the total control to "Group By" for the first Name field and "Count" for the second. If you run this query with no criteria, it will group all site names and give you a count of how many times they appear in your table. If you want to limit the number of site names to a smaller subset, set the criteria for the first "Name" field to: Like "*site*". Then the query will group and count only names that contain the word site.

by borginva

Still give mes a list of each site's totals (even if I hide the names) for example:

Microsoft 1
The Microsoft Corp 4
Building Microsoft 7
Microsoft2 3
The Microsoft Site 1

Iam looking for the total of the criteria I choose. My answer should be 16. Nothing but "16." I want to have it give me the total of every site with, say *microsoft in the name. If it has the name, it gets counted.

Microsoft
The Microsoft Corp
The Microsoft Corpa
The Microsoft Corpb
The Microsoft Corpc
Dell 1
Building Microsoft
Building qMicrosoft
Building Microsoftq
Building qqMicrosoft
Dell Me Too
Building Microsoftqq
Building qqqMicrosoft
Building Microsoftqqq
Compaq
Microsoft2
Microsoft21
Microsoft22
The Microsoft Site

I want the query (or report) to be able to count all sites with Microsoft in the name and give me this for my one and only answer: 16. I want the total of it all, not the total of each site.

I hope I am being more descriptive and I still do appreciate the answers. I really need to see if this can be done.

by borginva

jim94539,
I can not find such a button. I tried searching HELP but, of course, could not find it.
If this procedure will give me the total of all sites that share one word, for example SITE1, then please let me know what this button looks like.

by jim94539

The button looks like an E. However, you can also activate the totals function by selecting View then Totals from the menu bar. When this function is activated, the query form will display a new row called "Total:". This row gives you many different ways to summarize your data. For example, groups, sums, counts, etc.

Good luck,
Jim Smith

by borginva

Still give mes a list of each site's totals (even if I hide the names) for example:

Microsoft 1
The Microsoft Corp 4
Building Microsoft 7
Microsoft2 3
The Microsoft Site 1

Iam looking for the total of the criteria I choose. My answer should be 16. Nothing but "16." I want to have it give me the total of every site with, say *microsoft in the name. If it has the name, it gets counted.

Microsoft
The Microsoft Corp
The Microsoft Corpa
The Microsoft Corpb
The Microsoft Corpc
Dell 1
Building Microsoft
Building qMicrosoft
Building Microsoftq
Building qqMicrosoft
Dell Me Too
Building Microsoftqq
Building qqqMicrosoft
Building Microsoftqqq
Compaq
Microsoft2
Microsoft21
Microsoft22
The Microsoft Site

I want the query (or report) to be able to count all sites with Microsoft in the name and give me this for my one and only answer: 16. I want the total of it all, not the total of each site.

I hope I am being more descriptive and I still do appreciate the answers. I really need to see if this can be done.

by jim94539

Assuming the data table is called "Sites" and the field to be count is called "Name", your query should be set up with 3 columns as follows:

Column 1
Field: Site:"Microsoft"
Table:
Total: Expression
Sort:
Show: Yes
Criteria:

Column 2
Field: Count:Name
Table: Sites
Total: Count
Sort:
Show: Yes
Criteria:

Column 3
Field: Name
Table: Sites
Total: Where
Sort:
Show: No
Criteria: Like "*Microsoft*"

This will give you a query with two coulmns (fields). The first is just an expression to show what the crieria filter is: "Microsoft", and the second is the count of how often a site contains that filter.

I have a small demo database to show this arrangement and can email it to you if you'd like.

Good luck, Jim Smith

This discussion is closed to new replies.