B
Ben Johnson
Problem:
I need to create a report for the purpose of allocating $$
to Inspectors based upon the work they've done. I have a
table called tblReports, one called tblInspectors and
another called tblLink_InspectorsToReports. The
relationship between Inspectors and Reports is
Many-to-Many, hence the linking table. The info in the
linking table is [ReportID], [InspectorID] and [Category] -
where Category can be one of "Build" or "Pest".
An Inspector can be in more than one Category in any single
Report.
The Inspectors comission is based upon the final costing of
the job rather than how many individual Categories of
Inspection they did per Report.
I need to write a query (or queries) that will allow me to
determine in which ReportID's the Inspector did both
"Build" and "Pest", another which returns the ReportID's in
which the Inspector ONLY did "Build" and another which
returns the ReportID's in which the Inspector ONLY did
"Pest". The need to differentiate between "single
category" inspections and "dual category" inspections is so
the Inspectors don't get paid twice for the "dual category"
inspections. Also, the client wants to see it reflected on
the report which type of inspection was done - "B", "P" or
"B&P".
I've been able to write a query that gives me records where
the inspector has done "dual category" inspections, but not
one that will give me exclusively the "single category"
inspections.
Some example output from the linking table:
ReportID Category InspectorID
------------------------------------
1111 Build 67
1111 Pest 67
2222 Build 67
3333 Build 68
4444 Pest 67
From the above example, my first query will pick up
ReportID "1111" as a "dual inspection" report, but when I
try to make the query return the "Build" category
inspections that don't also have a "Pest" category with the
same ReportID something is not working - I still get
ReportID "1111" in the recordset.
Many thanks in advance for any help offered.
Regards,
Ben Johnson
I need to create a report for the purpose of allocating $$
to Inspectors based upon the work they've done. I have a
table called tblReports, one called tblInspectors and
another called tblLink_InspectorsToReports. The
relationship between Inspectors and Reports is
Many-to-Many, hence the linking table. The info in the
linking table is [ReportID], [InspectorID] and [Category] -
where Category can be one of "Build" or "Pest".
An Inspector can be in more than one Category in any single
Report.
The Inspectors comission is based upon the final costing of
the job rather than how many individual Categories of
Inspection they did per Report.
I need to write a query (or queries) that will allow me to
determine in which ReportID's the Inspector did both
"Build" and "Pest", another which returns the ReportID's in
which the Inspector ONLY did "Build" and another which
returns the ReportID's in which the Inspector ONLY did
"Pest". The need to differentiate between "single
category" inspections and "dual category" inspections is so
the Inspectors don't get paid twice for the "dual category"
inspections. Also, the client wants to see it reflected on
the report which type of inspection was done - "B", "P" or
"B&P".
I've been able to write a query that gives me records where
the inspector has done "dual category" inspections, but not
one that will give me exclusively the "single category"
inspections.
Some example output from the linking table:
ReportID Category InspectorID
------------------------------------
1111 Build 67
1111 Pest 67
2222 Build 67
3333 Build 68
4444 Pest 67
From the above example, my first query will pick up
ReportID "1111" as a "dual inspection" report, but when I
try to make the query return the "Build" category
inspections that don't also have a "Pest" category with the
same ReportID something is not working - I still get
ReportID "1111" in the recordset.
Many thanks in advance for any help offered.
Regards,
Ben Johnson