J
Joost de Vries
In a report I'm creating for a quality control database I'm experiencing some
difficulties calculating the totals on group- and report levels.
I'm currently selecting records based on a LEFT JOINT query where all
inspections are selected and, based on the related Errors, a quality
percentage per case is calculated in the report (The report is grouped first
by employee, second by case no.). In the schemes at the bottom of this post
I've drawn a simple scheme of how the DB and report (will have to) work.
-----
I've got the following questions:
1. I'm currently using a lot of DLookup, DCount and DSum functions in the
detail section because I can't include the indexes for employee, inspector,
category and item in the LEFT JOINT SQL Query. Is there a way around this?
2. I can't get the calculations for number of checked items (on empl.
level) and total number of errors (on case and empl. level) to work
correctly. Is there a way to
a. First calculate these based on the detail section (count errors on
case level) and
b. Secondly calculate the employee totals based on the case-level totals?
I'm always trying to be as complete as possible in these questions so the
may also help other developers looking for answers. Please let me know if you
need more information.
============
REPORT SCHEME:
============
|- LEVEL 1: Employee & Date (ie "John Doe, Cases inspected on 23-2-2007")
|- Contains multiple records from 'inspections' selected on 'employee' and
'date'.
|- Output vars in report: date, employee, inspector, no. of inspected items
(total), no. of items containing errors (total), quality of inspected work
(total, calculated).
|
|---- LEVEL 2: Case (ie "Case 1234567")
|---- Contains one record from 'inspections'.
|---- Output vars in report: date, employee, inspector, no. of inspected
items (total), no. of items containing errors (total), quality of inspected
work (total, calculated).
===============
TABLES (summarized):
===============
Related columns are indicated by *.
Inspections | Errors | Employees
----------------------------------------------------------------
id *A | id | id *B
date | check | firstname
employee *B | date_err | lastname
inspector | date_inspection | departement
case no. | category |
no. of items | item |
employee *B |
Of course there are more tables, like an index of all items, all
catergories, all inspectors etc.
difficulties calculating the totals on group- and report levels.
I'm currently selecting records based on a LEFT JOINT query where all
inspections are selected and, based on the related Errors, a quality
percentage per case is calculated in the report (The report is grouped first
by employee, second by case no.). In the schemes at the bottom of this post
I've drawn a simple scheme of how the DB and report (will have to) work.
-----
I've got the following questions:
1. I'm currently using a lot of DLookup, DCount and DSum functions in the
detail section because I can't include the indexes for employee, inspector,
category and item in the LEFT JOINT SQL Query. Is there a way around this?
2. I can't get the calculations for number of checked items (on empl.
level) and total number of errors (on case and empl. level) to work
correctly. Is there a way to
a. First calculate these based on the detail section (count errors on
case level) and
b. Secondly calculate the employee totals based on the case-level totals?
I'm always trying to be as complete as possible in these questions so the
may also help other developers looking for answers. Please let me know if you
need more information.
============
REPORT SCHEME:
============
|- LEVEL 1: Employee & Date (ie "John Doe, Cases inspected on 23-2-2007")
|- Contains multiple records from 'inspections' selected on 'employee' and
'date'.
|- Output vars in report: date, employee, inspector, no. of inspected items
(total), no. of items containing errors (total), quality of inspected work
(total, calculated).
|
|---- LEVEL 2: Case (ie "Case 1234567")
|---- Contains one record from 'inspections'.
|---- Output vars in report: date, employee, inspector, no. of inspected
items (total), no. of items containing errors (total), quality of inspected
work (total, calculated).
===============
TABLES (summarized):
===============
Related columns are indicated by *.
Inspections | Errors | Employees
----------------------------------------------------------------
id *A | id | id *B
date | check | firstname
employee *B | date_err | lastname
inspector | date_inspection | departement
case no. | category |
no. of items | item |
employee *B |
Of course there are more tables, like an index of all items, all
catergories, all inspectors etc.