If there isn't any data...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working on a query that counts how often certain types of contacts
happened between staff and clients, how many hours were spent on those
contacts, and how many clients were involved. For example, let's say, in
Feb. there were 10 visits between 7 clients. Those visits came to 13 hours
total. So, the query shows:

Visits Clients Hours
10 7 13

as the result.

There are several different types of contacts to calculate in this way and
in the end, I need to pull it all together into a final query, then a report.
The problem occurs when a particular type of contact doesn't happen in the
given time span. For example, a court appearance might not happen, so the
query result for that is empty. If I tie that into a final query for the
report, it makes the entire result set empty. How can I make it so that the
fields come back with 0's?

FYI: Due to the table design, there are zero's in the table fields that
have no numerical data, but I have to filter those out at the beginning so
that the count function in the query is correct.

Thanks!
 
Tara said:
I'm working on a query that counts how often certain types of contacts
happened between staff and clients, how many hours were spent on those
contacts, and how many clients were involved. For example, let's say, in
Feb. there were 10 visits between 7 clients. Those visits came to 13 hours
total. So, the query shows:

Visits Clients Hours
10 7 13

as the result.

There are several different types of contacts to calculate in this way and
in the end, I need to pull it all together into a final query, then a report.
The problem occurs when a particular type of contact doesn't happen in the
given time span. For example, a court appearance might not happen, so the
query result for that is empty. If I tie that into a final query for the
report, it makes the entire result set empty. How can I make it so that the
fields come back with 0's?

FYI: Due to the table design, there are zero's in the table fields that
have no numerical data, but I have to filter those out at the beginning so
that the count function in the query is correct.
Click to expand...


This depends on how your table(s?) are designed. A bad
design where each type of contact is a separate field in a
contacts table will not have a resonable solution to your
question. A properly normalized table design where a
contacts table has one field for the type of contact will
yeild a simple solution without the problems you are
describing in your question.
 
I know what you're getting at. And no, it's not normalized. Unfortunately,
it was specifically requested by the manager of the program that is using
this database that the contacts table design be left alone (I built a new
database and imported existing data from an older one). Since the design had
worked in the past for them, I relented. I shouldn't have. I knew it would
eventually cause problems for me.

I'm not sure I know what your solution would have been, but could I do a
union query to combine the fields from the table in question, then do a
make-table query to get a normalized table, then pull the data from that???

I'll deal with getting the actual table normalized in the very near future.
 
Use the UNION ALL approach to simulate a normalized table.
Then you can use that as the base of a Totals type query to
count or sum each type of contact.

After you get each query working, you can base a report on
the Totals query and go from there.

If you need further assistance, please post a Copy/Paste of
the queries you are using for this.
 
Back
Top