How to represent a record not found

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Just created my first Access database and it works great - sort of.

I have a table that contains records of individuals of whom a select
few I'm monitoring via the report I created. The report lists these
names from a query that searches the table for these few names.

The report does display their names and displays a count representing
the number of records that contain the name in a name field. However,
if one of the individuals has never actually been entered into the
database, obviously that name does not appear on the report. Those
running the report need to be aware of a name being monitored even if
there is a zero count for that name.

How can I derive from the query or table that this specific individual
does not yet exist so that my report will display their name with a
zero as the count?

My query is looking for any of the names in the name field. I suppose
if I was more specific and created separate criteria, each criteria
handling only one name, then I'd get an error if that name was not
found and I can use some sort of error trapping to generate a line on
the report to indicate a zero count for that name? Just not sure if
that would be the best way to approach this.

Access is great! I wish I had started working with it years ago.

Thanks,

Don
 
Don

A couple approaches ...

One would be to use a "cross-tab query" and force a category for each.

Another approach would be to start with a list (a query listing only those
you want) and use a join that you modify to take all from the list, plus any
from your "count" query that match.

You may want to check into using the Nz() function as well -- it let's you
convert a Null to a Zero.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff,

I left the database at work so I'll give these a try when I return.

Since it's my first database and I'm learning as I build it, I'll have
to learn about the cross-tab query and Nz() function you're referring
to. The latter sounds like it might solve another problem I had with
errors displaying on the report if it didn't find a record. Sounds
like I can put that error to good use with Nz() if it's what I think
it is.

But that second approach you offer sounds interesting. I'll probably
try all three of your suggestions just to get a better feel for Access
anyway.

I was also thinking of maybe writing some VB code to create a
conditional statement that if any of the names I'm monitoring aren't
found, then I simply add that to the bottom of the report and show
that it equals zero. Not sure where to do that, but on the other
hand, that sort of approaches what you're suggesting as your 2nd
option.

Thanks,

Don.
 
Back
Top