Get total even when no data to add

[Guest]

Main table = AutoNum ; Date ; Track ; ReqAudits ;
Audit table = Num ; Score ; Findings

These tables are filled from a form (and subform).

A Report shows how many audits were done on any given track; the number of
required audits; the average score; and % of audits done; by date.

I need it to show the total required audits; by date, even if they don't do
any audits.
Any help or advice would be appreciated.

 

[Allen Browne]

Presumably your report is based on a query that contains both tables.

In query design view, double-click the line joining the 2 tables.
Access pops up a dialog offering 3 choices.
Choose the one that says:
All records from Main, and any matches from Audit.

If you don't do that, the inner join (the default) gives you only those
records that match in both tables.

 

[Guest]

I'm sorry, I didn't ask the right question. When the auditors key in their
audits, they may not have done any audits on, say, one track. On the report I
need to show that this many audits were required but that they did none. Do I
need to put another table in that the Main table can look at (say by date)
and automatically will put a required # audits for that track so the report
will show "2" audits required, and "0" done. the number of audits required
for a track one day may be different for that track the next day. The boss
could put in what he expects for each day.

I hope this explains it better.

 

[Allen Browne]

So, your Main table records what audits need to be done, with fields:
AutoNum = primary key;
Track = a foreign key to something else;
Date = a date/time field;
ReqAudits = the number of audits required on that date for that track.

Then your Audit table records each audit as it is done.
Presumably Num is a foreign key to AutoNum.

1. Create the query into both tables, and change to outer join as
instructed.

2. In query design, depress the Total button on the toolbar.
Access adds a Total row to the grid, and enters Group By under the fields.

3. In the Total row under the Score field, choose Avg.
This yields the average score.

4. Drag the primary key from the Audit table into the grid.
In tht Total row under this field, choose Count.

The query displays:
- all records from the Main table
- the average score (blank if none done).
- the count of audits done (zero if none).

That's the data you need for your report?

 

[Guest]

Thanks Allen, that is exactly what I needed to know. I was trying to make it
harder than it was.