how can I count different types of records ?

[Ro477]

I have Access2002 on WinXP. I have a report detailing different transactions
for a nominated period via a Query. During the period there can be 3 types
of transactions, each having its own data record in the same table. My
report prints details of each transaction/record, and in the report summary
I need to indicate the total number of transactions for each of the
respective transaction types A, B and C.

I can count the total number of records in the report using Count (*), but
how can I count the different types. In the data table each type has its own
field as they each have unique data in that field. I have been trying to
figure out how to count the different types in an extra query then link it
to the report as another query .. but can't figure it out. There must be an
easier way that what I'm trying to do ...Help please !!!!!! .. Roger

 

[Jeff Boyce]

Create a new query in design view. Add the table.

Add the row ID, add the "type" field.

Click the Totals button (looks like a sideways "M").

"Group By" the type field, "Count" the rowID.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ro477]

Thanks for your reply. Yes, that is the approach I took at first, but I need
to have the TransactionDate field in there as well so I get only the
transactions for the last 30 days (ie a fixed nominated period of time), and
.... PROBLEM #1 ... when I use Count on the row ID field I get a total for
each date, ie the number of transactions on each different date within the
30 day period concerned and not for the total 30 day period.

Also, PROBLEM #2 ... I can use the rowID field for type A, but I can't use
the same query for type B, and then type C transactions which are in their
own separate fields. I guess can overcome this by having 3 queries to get
the data (assuming I can get it to count for the whole period and not just
on a day basis), but can I link 3 different queries to fields in the same
report footer section (remembering also, that I already have a main query
for the data for each transaction record (or row) in the report detail
section ?

thanks for your help ... Roger

 

[Jeff Boyce]

Hold on, if you want the count, there is no "transaction date".

If you want to limit to "the last 30 days", use that field in your totals
query, but set it to WHERE and provide the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ro477]

Sorry, I must be a bit dense. Usually I can work these things out, but on
this one I can't.

I have done what you have said. Only thing is now I have made a QUERY2 which
refers to QUERY1. Query1 asks for the period dates and filters the records
for the detail of the report. My query 2 then doesn't have to ask again for
the dates (thus no need for a WHERE condition anywhere), so that is now gone
! In query2 I have the fields concerned which need to be counted. I have the
total line with COUNT being the condition.

Can I still use the criteria line ? In one of the columns [FREE], which is a
yes/no field, in the criteria line I have the word "yes" (without the
quotes) meaning that I want to know the count for the yes records in the
[FREE] field. But it doesn't seem to work. I just get the total number of
records, not a count for the "yes" records ? What have I done wrong now !

 

[Ro477]

Thanks for your help, but I have discovered another way of doing it. In the
footer of the report which is basically what I wanted, not to do it via
another query.
For other readers see http://support.microsoft.com/kb/209505/en-us

best wishes .. Roger

 

[Jeff Boyce]

Try <>0 instead of 'yes'.

An Access Yes/No field stores a 0 for no, and a -1 for yes. Other databases
store a 1 for yes. So you should be safe if you provide a criterion
equivalent to "not no" (i.e., <>0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, I must be a bit dense. Usually I can work these things out, but on
this one I can't.

I have done what you have said. Only thing is now I have made a QUERY2
which refers to QUERY1. Query1 asks for the period dates and filters the
records for the detail of the report. My query 2 then doesn't have to ask
again for the dates (thus no need for a WHERE condition anywhere), so that
is now gone ! In query2 I have the fields concerned which need to be
counted. I have the total line with COUNT being the condition.

Can I still use the criteria line ? In one of the columns [FREE], which is
a yes/no field, in the criteria line I have the word "yes" (without the
quotes) meaning that I want to know the count for the yes records in the
[FREE] field. But it doesn't seem to work. I just get the total number of
records, not a count for the "yes" records ? What have I done wrong now !

Hold on, if you want the count, there is no "transaction date".

If you want to limit to "the last 30 days", use that field in your totals
query, but set it to WHERE and provide the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP