Criteria = X But Not Y

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

Hi,


Format( Now(), "mmmm" ) or Format(Now(), "mmm")


Hoping it may help,
Vanderghast, Access MVP


"Ben Johnson" wrote in message
...
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

Michael,

I think this post was meant to be a response to the message
after mine. Shame - I was all excited!

Cheers,
Ben.


-----Original Message-----
Hi,


Format( Now(), "mmmm" ) or Format(Now(), "mmm")


Hoping it may help,
Vanderghast, Access MVP


"Ben Johnson" wrote
in message
...
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


.

Hi,

--- well, well, well. Thanks to point the problem. I reposted
appropriately.

Have you tried a Total query, with a criteria on the COUNT(*)?


SELECT ReportID, InspectorID
FROM myTable
GROUP BY ReportID, InspectorID
HAVING COUNT(*) = 1


( or HAVING COUNT(*) 1 for those that are multiple) ?



Hoping it may help,
Vanderghast, Access MVP




"Ben Johnson" wrote in message
...
Michael,

I think this post was meant to be a response to the message
after mine. Shame - I was all excited!

Cheers,
Ben.


-----Original Message-----
Hi,


Format( Now(), "mmmm" ) or Format(Now(), "mmm")


Hoping it may help,
Vanderghast, Access MVP


"Ben Johnson" wrote
in message
...
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


.

Use Access query wizard to create unmatched query. Look
for Build or Pest records that are unmatched in the query
that finds both Pest and Build.

Joel
-----Original Message-----
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
.

Hi Michael,

I've tried that idea and it works for the "dual category"
inspections fine - as no differentiation between the
category types is necessary. The main problem is that it
cannot differentiate between "Build" and "Pest" Category
types AS WELL AS return records that have a Count of only
1. At least, I haven't been able to figure out a way to
make it work.

Regards,
Ben Johnson

-----Original Message-----
Hi,

--- well, well, well. Thanks to point the problem. I
reposted
appropriately.

Have you tried a Total query, with a criteria on the COUNT(*)?


SELECT ReportID, InspectorID
FROM myTable
GROUP BY ReportID, InspectorID
HAVING COUNT(*) = 1


( or HAVING COUNT(*) 1 for those that are multiple) ?



Hoping it may help,
Vanderghast, Access MVP




"Ben Johnson" wrote
in message
...
Michael,

I think this post was meant to be a response to the message
after mine. Shame - I was all excited!

Cheers,
Ben.


-----Original Message-----
Hi,


Format( Now(), "mmmm" ) or Format(Now(), "mmm")


Hoping it may help,
Vanderghast, Access MVP


"Ben Johnson" wrote
in message
...
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


.



.