Counting records

  • Thread starter Thread starter Mike J
  • Start date Start date
M

Mike J

I have one query using two tables that gives me name,
adrs, etc. I then have another table that keeps track of
the time and date they used this clents card. The linking
is done with the 'id'(primary index}.
How do I do a report that gives the id, name, etc and then
the number of times the card was used -- all on one line.
 
Mike,

Use a totals query on your second table (client cards
table). Group on ID and count on a populated field. In
your main query use your name/address table and this new
query joined by the ID field as before. This will give you
a record source with name and address details and a count
of the card usage.

HTH,

Terry
 
-----Original Message-----
Mike,

Use a totals query on your second table (client cards
table). Group on ID and count on a populated field. In
your main query use your name/address table and this new
query joined by the ID field as before. This will give you
a record source with name and address details and a count
of the card usage.

HTH,

Terry

.
Thx for the reply. I tried your sug and it worked
Click to expand...
however when I join the two, it limits the records to the
ones that used the card(the card use table only records
the id and date of use if used). If the card hasn't been
used yet, that id is not in the table.
I need the report to list all the card holders even if the
card was not used. Thanks in advance.
 
Mike,

In the query double click your join line and alter the
join to present all records from your client table and
matching ones from the totals query.
Depending on how you want your display on the report to
look you want to put the following in your control (rather
than the total from the query. =if(nz([querytotal],0)=0,0,
[querytotal) This should populate your report with a zero
where the card has not been used. - hope I've got the
syntax right.

HTH,

Terry
 
Back
Top