CODEWITHSUNDEEP
sqldatabasems-access
Craig Mazzotta
Craig Mazzotta

Reputation: 13

Microsoft Access Count unique values per id

I have an access database that has an id referring to a customer who has trucks of different sizes. currently the table looks something like this:

id.....tire size
1......30
1......30
1......31
1......31
2......32

What I want to achieve is something like this:

id.....30.....31.....32
1......2......2......0
2......0......0......0

where it counts the number of occurrences of a specific tire size and inputs it into the respective tire size column.

Upvotes: 1

Views: 1251

Answers (3)

Kelewan
Kelewan

Reputation: 33

Use the 'GROUP BY' aggregator

You'll need something like this:

SELECT 
tyre_size,
count(id) 
FROM tablename 
GROUP BY
tire_size

Upvotes: 0

random_answer_guy
random_answer_guy

Reputation: 856

In order to display the data as you have written it, you will need to do a crosstab query. The code below should achieve what you want

TRANSFORM Nz(Count([YourTable].[Tire Size]),0) AS [CountOfTire Size]
SELECT [YourTable].[ID]
FROM [YourTable]
GROUP BY [YourTable].[ID]
PIVOT [YourTable].[Tire Size];

Upvotes: 2

Mike Robinson
Mike Robinson

Reputation: 8945

The first step would be a query like:

select tire_size, COUNT(id) from mytable
GROUP BY tire_size

(I put the "special magic" parts of that query in UPPER CASE for emphasis.)

In the MS-Access query-builder, grouping features are accessed by clicking a button that looks vaguely like an "E" (actually, a Greek "epsilon" character), if I recall correctly. This adds a new "grouping" row to the query-builder grid.

This will produce (as you will quickly see) a row-by-row result with tire-size and the count of id's for that tire-size.

Many other variations of this are possible. Read the MS-Access on-line help which discusses this feature: they did a very good job with it.

The essential idea is the GROUP BY clause: this says that each distinct value of tire_size forms a "group." (Yes, you can GROUP BY more than one column, in which each unique combination of values forms one group.) Then, you specify so-called "domain aggregate functions, such as COUNT(), AVG(), SUM(), to produce summary statistics for each group.

Every GROUP BY column must appear in the SELECT clause, and every other item that appears there must be a domain aggregate function. (Which, if you think about it, makes perfect sense ...)

(Fortunately, MS-Access's query builder does a good job of "hiding" all that. You can build a grouping-query interactively, thanks to that "epsilon" button. But it's useful then to look at the "SQL View" to see what it did in SQL terms.)

Upvotes: 0

Related Questions