Query Count

[Guest]

Hi,

I have created a query that gives me a column called books that is repeated.
And another column that lists an Id number.

It looks something like this:

Book ID
0083 T0044
0083 K0085
0084 K0086
0084 T0046
0084 T1353

What I would like to do is get a count per book and a count of ID's listed
by a T and another count by K ber book.
Ex: 0083 has 2 books and 1 T ID and 1 K ID.

Is this possible in Access, if so how do I go about doing this?

 

[Guest]

Hi Miranda,

Maybe something like this.

SELECT DISTINCT Table1.Book, DCount("Book","Table1","Book = '" & [Book] & "'
AND ID Like 'T*'") AS [T ID], DCount("Book","Table1","Book = '" & [Book] & "'
AND ID Like 'K*'") AS [K ID]
FROM Table1;

Hope this helps.

 

[John Vinson]

Hi,

I have created a query that gives me a column called books that is repeated.
And another column that lists an Id number.

It looks something like this:

Book ID
0083 T0044
0083 K0085
0084 K0086
0084 T0046
0084 T1353

What I would like to do is get a count per book and a count of ID's listed
by a T and another count by K ber book.
Ex: 0083 has 2 books and 1 T ID and 1 K ID.

Is this possible in Access, if so how do I go about doing this?

Possible, and even simple if you know the secret handshake <g>.

Create a Query based on your Table. Change it to a Totals query by
clicking the Greek Sigma icon (summation sign, looks like a sideways
W).

Leave the default Group By on Book alone; change it to Count on ID.

Open the query and you'll see two rows:

Book CountOfID
0083 2
0084 3

John W. Vinson[MVP]

 

[Guest]

Hi John,

I tried this and it did not work. It doesn't cound how many books I have,
for ex if I have 0083 3 times I want it to display 0083 and then give me a
count (3) as for the ID based on whether the ID starts with T or K, give me a
count of all the T's for book 0083 and all the ID's starting with K

so 0083 (3), T (2) K (1)
0084 (8), T(3) K (8)

 

[Guest]

Hi,

Where would I add this select statement to my query? Is there some view.

Hi Miranda,

Maybe something like this.

SELECT DISTINCT Table1.Book, DCount("Book","Table1","Book = '" & [Book] & "'
AND ID Like 'T*'") AS [T ID], DCount("Book","Table1","Book = '" & [Book] & "'
AND ID Like 'K*'") AS [K ID]
FROM Table1;

Hope this helps.

Hi,

I have created a query that gives me a column called books that is repeated.
And another column that lists an Id number.

It looks something like this:

Book ID
0083 T0044
0083 K0085
0084 K0086
0084 T0046
0084 T1353

What I would like to do is get a count per book and a count of ID's listed
by a T and another count by K ber book.
Ex: 0083 has 2 books and 1 T ID and 1 K ID.

Is this possible in Access, if so how do I go about doing this?

 

[Guest]

Hi Miranda,

When you in Query Design View, you can also choose SQL View under "View".
All you need to do then is just copy and paste and change the table name and
appropriate field name.

If you are still having problem, let me know what is the table name and
field name. I will change the SQL, then do copy and paste again.

Help this helps.

Hi,

Where would I add this select statement to my query? Is there some view.

Hi Miranda,

Maybe something like this.

SELECT DISTINCT Table1.Book, DCount("Book","Table1","Book = '" & [Book] & "'
AND ID Like 'T*'") AS [T ID], DCount("Book","Table1","Book = '" & [Book] & "'
AND ID Like 'K*'") AS [K ID]
FROM Table1;

Hope this helps.

Hi,

I have created a query that gives me a column called books that is repeated.
And another column that lists an Id number.

It looks something like this:

Book ID
0083 T0044
0083 K0085
0084 K0086
0084 T0046
0084 T1353

What I would like to do is get a count per book and a count of ID's listed
by a T and another count by K ber book.
Ex: 0083 has 2 books and 1 T ID and 1 K ID.

Is this possible in Access, if so how do I go about doing this?

 

[John Vinson]

Hi John,

I tried this and it did not work. It doesn't cound how many books I have,
for ex if I have 0083 3 times I want it to display 0083 and then give me a
count (3) as for the ID based on whether the ID starts with T or K, give me a
count of all the T's for book 0083 and all the ID's starting with K

so 0083 (3), T (2) K (1)
0084 (8), T(3) K (8)

Sorry! Read your question too hastily.

One problem is that you are violating normal form by storing two
pieces of information - your letter code and a number - in one field.
This makes things MUCH more difficult since you need to extract the
letter each time; consider using two fields, a one-character text
field and an integer, and concatenating them for display purposes
instead.

You'll need a couple of calculated fields in the query: JL has part of
the answer, but unfortunately his Count will count records rather than
counting matches. In two vacant Field cells in this query, type

CountT: IIF(Left([ID], 1) = "T", 1, 0)
CountK: IIF(Left([ID], 1) = "K", 1, 0)

On the Totals line under this field put Sum. This will add 1 for each
T and 0 for each K in CountT, and vice versa in K - giving you the
desired count.

John W. Vinson[MVP]

 

[Guest]

Ok thanks.

Another question. I have developed a query that displays a group number
for ex: 37 and it is repeated several times.
The field beside it is called book and lists all the books pertaining to
that group.
ex: Group 37 contains book 2523,2525,2526,2523
Is there a way to only show the group once then just give me a count of the
# of books that aren't duplicated that pertain to that group.
ex: So with group 37 above it would display as Group 37 - 3 as 2523 is
repeated twice.

Hi John,

I tried this and it did not work. It doesn't cound how many books I have,
for ex if I have 0083 3 times I want it to display 0083 and then give me a
count (3) as for the ID based on whether the ID starts with T or K, give me a
count of all the T's for book 0083 and all the ID's starting with K

so 0083 (3), T (2) K (1)
0084 (8), T(3) K (8)

Sorry! Read your question too hastily.

One problem is that you are violating normal form by storing two
pieces of information - your letter code and a number - in one field.
This makes things MUCH more difficult since you need to extract the
letter each time; consider using two fields, a one-character text
field and an integer, and concatenating them for display purposes
instead.

You'll need a couple of calculated fields in the query: JL has part of
the answer, but unfortunately his Count will count records rather than
counting matches. In two vacant Field cells in this query, type

CountT: IIF(Left([ID], 1) = "T", 1, 0)
CountK: IIF(Left([ID], 1) = "K", 1, 0)

On the Totals line under this field put Sum. This will add 1 for each
T and 0 for each K in CountT, and vice versa in K - giving you the
desired count.

John W. Vinson[MVP]

 

[John Vinson]

Ok thanks.

Another question. I have developed a query that displays a group number
for ex: 37 and it is repeated several times.
The field beside it is called book and lists all the books pertaining to
that group.
ex: Group 37 contains book 2523,2525,2526,2523
Is there a way to only show the group once then just give me a count of the
# of books that aren't duplicated that pertain to that group.
ex: So with group 37 above it would display as Group 37 - 3 as 2523 is
repeated twice.

This needs a "Count Distict" operation which unfortunately is not
available in Access. You'll need a Query based on another Query.

First create a Query selecting the group and book fields. In the
query's Properties set the "Unique Values" property to true - this
will give you

37 2523
37 2525
37 2526
39 3104
....

A Totals query will then count the (distinctified) books.

John W. Vinson[MVP]