Yes / No query problem

[Serendipity]

* I have a qry that uses the information from 2 other queries with 2
calculated fields.

* The "base" queries are pulling thousands of records to just inspections
from 2009 rather than 5 years of data across multiple tables.

*The new query has fields from qry 1 with the first calculated field looking
to the qry 2 and calculating the difference in days from the inspection
date(qry 2) vs scheduled date(qry 1).
Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])

* The second calculated field gives me a yes or no based upon the first
calculated field.
Inspection Received: IIf([form received] Between -40 And 60,"yes","no")

* Here is my problem: For an inspection scheduled I am getting multiple yes
and no's as I may have received more than one report for each location or
scheduled date which then plays have with the % received / not received
report. IE I have 1641 inspections scheduled, but I am getting 2185 yes / no
answers. How can I limit the query to not return the extras? example: WO
1234-12345 has a scheduled date of 07-02-09. I received inspection reports
for 07-31-09, 08-15-09 and 03-12-09 so I am getting 2 yes's and 1 no. I
would like it to stop comparing dates and returning additional yes / no's
once it returns yes, thus leaving me with the original 1641.

Thanks for all your help and assistance.

 

[vanderghast]

Since Yes is -1 and No is 0,

SELECT inspection, MIN(inspectionResult)
FROM ...
GROUP BY inspection



will return -1 if there is at least a yes, 0 otherwise, for any given
inspection. You could also have use SUM( inspectionResult) <> 0, since the
SUM will be equals to 0 if there is only NO.


Vanderghast, Access MVP

 

[Serendipity]

I am confused... easy to do! Thanks for your help...

I believe you want me to make another calculated field? Or am I lost???

Then in Field type: Select ????, MIN(inspectionResult)
Table type: Form Received {current calculated column
returning yes / no}
Group By: Inspection???

Is inspection a new name? Or are we talking using the WO # Issued Field Name
that I don't want multiple results matching up to?

FYI this qry is then used to make a chart of % received by property manager
based on Yes / No.

Current
Field Names:
AddressID
PropertyManager
Equip#
WO #
ScheduledDate
Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])
Roof Inspection Received: IIf([form received] Between -42 And 60,"yes","no")

Table:
QryWO#'SAssigned
QryWO#'SAssigned
QryWO#'SAssigned
QryWO#'SAssigned
Blank
Blank

Total:
Group By on all

No criteria

Since Yes is -1 and No is 0,

SELECT inspection, MIN(inspectionResult)
FROM ...
GROUP BY inspection



will return -1 if there is at least a yes, 0 otherwise, for any given
inspection. You could also have use SUM( inspectionResult) <> 0, since the
SUM will be equals to 0 if there is only NO.


Vanderghast, Access MVP

* I have a qry that uses the information from 2 other queries with 2
calculated fields.

* The "base" queries are pulling thousands of records to just inspections
from 2009 rather than 5 years of data across multiple tables.

*The new query has fields from qry 1 with the first calculated field
looking
to the qry 2 and calculating the difference in days from the inspection
date(qry 2) vs scheduled date(qry 1).
Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])

* The second calculated field gives me a yes or no based upon the first
calculated field.
Inspection Received: IIf([form received] Between -40 And 60,"yes","no")

* Here is my problem: For an inspection scheduled I am getting multiple
yes
and no's as I may have received more than one report for each location or
scheduled date which then plays have with the % received / not received
report. IE I have 1641 inspections scheduled, but I am getting 2185 yes /
no
answers. How can I limit the query to not return the extras? example: WO
1234-12345 has a scheduled date of 07-02-09. I received inspection reports
for 07-31-09, 08-15-09 and 03-12-09 so I am getting 2 yes's and 1 no. I
would like it to stop comparing dates and returning additional yes / no's
once it returns yes, thus leaving me with the original 1641.

Thanks for all your help and assistance.

 

[vanderghast]

You make a brand new query, based not on a table but on your existing saved
query (bring your saved query as 'table').

Change the query type to a total query.

Bring the field(s) which defines your 1641 unique different records you
want in the end, bring that field(s) in the grid, keep the proposed GROUP
BY.

Bring the field with the "yes"/"no", in the grid, change the GROUP BY to MAX
(assuming you have litteral values, "yes" and "no", rather than Boolean
values, -1 and 0 ).


That makes a query with your 1641 records, each of them with a "yes" if
there is one record, from your saved query, with a "yes", otherwise, a "no".

If you need additionnal fields, bring them in the grid and change the GROUP
BY to LAST.


Vanderghast, Access MVP

I am confused... easy to do! Thanks for your help...

I believe you want me to make another calculated field? Or am I lost???

Then in Field type: Select ????, MIN(inspectionResult)
Table type: Form Received {current calculated column
returning yes / no}
Group By: Inspection???

Is inspection a new name? Or are we talking using the WO # Issued Field
Name
that I don't want multiple results matching up to?

FYI this qry is then used to make a chart of % received by property
manager
based on Yes / No.

Current
Field Names:
AddressID
PropertyManager
Equip#
WO #
ScheduledDate
Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])
Roof Inspection Received: IIf([form received] Between -42 And
60,"yes","no")

Table:
QryWO#'SAssigned
QryWO#'SAssigned
QryWO#'SAssigned
QryWO#'SAssigned
Blank
Blank

Total:
Group By on all

No criteria

Since Yes is -1 and No is 0,

SELECT inspection, MIN(inspectionResult)
FROM ...
GROUP BY inspection



will return -1 if there is at least a yes, 0 otherwise, for any given
inspection. You could also have use SUM( inspectionResult) <> 0, since
the
SUM will be equals to 0 if there is only NO.


Vanderghast, Access MVP

* I have a qry that uses the information from 2 other queries with 2
calculated fields.

* The "base" queries are pulling thousands of records to just
inspections
from 2009 rather than 5 years of data across multiple tables.

*The new query has fields from qry 1 with the first calculated field
looking
to the qry 2 and calculating the difference in days from the inspection
date(qry 2) vs scheduled date(qry 1).
Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])

* The second calculated field gives me a yes or no based upon the first
calculated field.
Inspection Received: IIf([form received] Between -40 And 60,"yes","no")

* Here is my problem: For an inspection scheduled I am getting multiple
yes
and no's as I may have received more than one report for each location
or
scheduled date which then plays have with the % received / not received
report. IE I have 1641 inspections scheduled, but I am getting 2185 yes
/
no
answers. How can I limit the query to not return the extras? example:
WO
1234-12345 has a scheduled date of 07-02-09. I received inspection
reports
for 07-31-09, 08-15-09 and 03-12-09 so I am getting 2 yes's and 1 no.
I
would like it to stop comparing dates and returning additional yes /
no's
once it returns yes, thus leaving me with the original 1641.

Thanks for all your help and assistance.

 

[Serendipity]

Thank you so much!!!! Awesome directions. Thanks for breaking it down into
baby steps for me. It allowed me to understand why and how rather than just
doing it along with now being able to apply it to future queries. I wish more
responses were this clear on this forum.

Serendipity...

You make a brand new query, based not on a table but on your existing saved
query (bring your saved query as 'table').

Change the query type to a total query.

Bring the field(s) which defines your 1641 unique different records you
want in the end, bring that field(s) in the grid, keep the proposed GROUP
BY.

Bring the field with the "yes"/"no", in the grid, change the GROUP BY to MAX
(assuming you have litteral values, "yes" and "no", rather than Boolean
values, -1 and 0 ).


That makes a query with your 1641 records, each of them with a "yes" if
there is one record, from your saved query, with a "yes", otherwise, a "no".

If you need additionnal fields, bring them in the grid and change the GROUP
BY to LAST.


Vanderghast, Access MVP

I am confused... easy to do! Thanks for your help...

I believe you want me to make another calculated field? Or am I lost???

Then in Field type: Select ????, MIN(inspectionResult)
Table type: Form Received {current calculated column
returning yes / no}
Group By: Inspection???

Is inspection a new name? Or are we talking using the WO # Issued Field
Name
that I don't want multiple results matching up to?

FYI this qry is then used to make a chart of % received by property
manager
based on Yes / No.

Current
Field Names:
AddressID
PropertyManager
Equip#
WO #
ScheduledDate
Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])
Roof Inspection Received: IIf([form received] Between -42 And
60,"yes","no")

Table:
QryWO#'SAssigned
QryWO#'SAssigned
QryWO#'SAssigned
QryWO#'SAssigned
Blank
Blank

Total:
Group By on all

No criteria

Since Yes is -1 and No is 0,

SELECT inspection, MIN(inspectionResult)
FROM ...
GROUP BY inspection



will return -1 if there is at least a yes, 0 otherwise, for any given
inspection. You could also have use SUM( inspectionResult) <> 0, since
the
SUM will be equals to 0 if there is only NO.


Vanderghast, Access MVP



* I have a qry that uses the information from 2 other queries with 2
calculated fields.

* The "base" queries are pulling thousands of records to just
inspections
from 2009 rather than 5 years of data across multiple tables.

*The new query has fields from qry 1 with the first calculated field
looking
to the qry 2 and calculating the difference in days from the inspection
date(qry 2) vs scheduled date(qry 1).
Form Received: DateDiff("d",[Scheduled Date],[InspectionDate])

* The second calculated field gives me a yes or no based upon the first
calculated field.
Inspection Received: IIf([form received] Between -40 And 60,"yes","no")

* Here is my problem: For an inspection scheduled I am getting multiple
yes
and no's as I may have received more than one report for each location
or
scheduled date which then plays have with the % received / not received
report. IE I have 1641 inspections scheduled, but I am getting 2185 yes
/
no
answers. How can I limit the query to not return the extras? example:
WO
1234-12345 has a scheduled date of 07-02-09. I received inspection
reports
for 07-31-09, 08-15-09 and 03-12-09 so I am getting 2 yes's and 1 no.
I
would like it to stop comparing dates and returning additional yes /
no's
once it returns yes, thus leaving me with the original 1641.

Thanks for all your help and assistance.