Monthly Total

[kalensk]

I inherited this database. Each ID may or may not have errors. I need
to find the Total Errors/ID for each month. Each ID is unique

There are two tables as follows:
tbl1 contains ID and Date
tbl2 contains ID and Errors

If I display tbl1.ID it will show all ID's logged into the database:
ID1 Date
ID2 Date
ID3 Date

If I show tbl2.ID it will show repeated IDs for each error. Such as:
ID1 Date Error
ID1 Date Error
ID1 Date Error
ID3 Date Error

Therefore:
ID1 had 3 errors
ID2 had 0 errors
ID3 had 1 error

Is there an easy way to count the number of errors per month over each
ID (each ID is unique), and count the number of IDs logged into the
database in order to find the percentage of Errors/ID for each month
given a start and end date.

 

[Allen Browne]

Can we assume there is relationship between tbl1 and tbl2 based on the ID,
i.e. one record from tbl1 could have many matching records in tbl2?

If so:

1. Create a query using the 2 tables.

2. Double-click the line joining the 2 tables in the upper pane of table
design. Access opens a dialog offering 3 options. Choose the one that says:
All records from tbl1, and any matches from tbl2.
This ensures it still counts the IDs that have no errors.

3. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the grid.

4. Drag the ID field from tbl1 into the grid.
Accept Group By in the Total row under this field.

5. In the next column, in the Field row, enter:
TheYear: Year([tbl1].[Date])
Accept Group By.

6. In the next column, in the Field row, enter:
TheMonth: Month([tbl1].[Date])
Accept Group By.

7. Drag Errors from tbl2 into the grid.
In the Total row under this field, choose: Sum

8. Drag the Date field from tbl1 into the grid.
In the Total row choose: Where
In the Criteria row, enter the date range you want, e.g.:
Between #7/1/2006# And #10/31/2006#

9. Test. You should see a count for each ID for each month.
But, there's a blank where there were on errors.
To fix that:
o Change the query to SQL View (View Menu)
o Locate:
Sum(tbl2.Errors) AS SumOfErrors
o Add the Nz() and CLng() functions around the expression, so it is:
CLng(Nz(Sum(tbl2.Errors),0)) AS SumOfErrors

That gives you the monthly count. To get the percentage or averages, use the
domain aggregate functions such as DSum() or DAvg().

Hopefully your field is not really called "Date", since that's a reserved
word in both JET and VBA, and will give problems. For a list of the names to
avoid, see:
Problem names and reserved words in Access
at:
http://allenbrowne.com/AppIssueBadWord.html