How to Create a Count Query

[UNC_Access]

Hi!

Basically, here is my situation.

I have a table with the following fields:

ID
Amount

Here is a sample table:

ID, Amount
A, 1
B, 2
C, 3
D, 4
E, 4

I want to create a query with a new field called "CountOfAmount".

I need this field to count the number of occurrences of "Amount". For example:

ID, Amount, CountOfAmount
A, 1, 1
B, 2, 1
C, 3, 1
D, 4, 2
E, 4, 2

That is all! If my explanation is confusing please let me know and I will rephrase!

 

[John Big Booty]

Have a look at the options that become available when you turn your query in to a Totals Query.

 

[UNC_Access]

Hi John!

Thanks for the link.

I actually tried this but failed to realize I had grouped by the primary key in my query.

Got to start somewhere!

Appreciate it!

 

[UNC_Access]

Wait. I just realized one problem:

I didn't include the ID field. Therefore my table appears as follows (using my original example):

Amount, CountOfAmount
1, 1
2, 1
3, 1
4, 2

The problem is that I need the ID field included. But if I include the ID field and select Group By, the CountOfAmount field is "1" for all records.

 

[John Big Booty]

OK, you wont be able to do this in a single query, it's going to take two queries.

In the first one you will need to have the Amount in the query builder grid twice. You will Group By the first occurrence and Count the second.

You will then use this in your second query, along with your original table, you will make a link between Amount in both tables. Then put the ID, Amount and Count Of Amount in your grid;

and the result should look like;

 

[UNC_Access]

Perfect! Thanks a lot!

These basic bits of information are helping me learn effectively!