multiple duplicate pages in report

  • Thread starter Thread starter DanG
  • Start date Start date
D

DanG

I have a report that is based upon a form that filters the results. I choose
the name of an office as well as start date and end date. The report totals
the info properly and gets all the boxes filled out good. HOWEVER, it
creates 1 page per record in the report, so if there are 3 records within the
chosen criteria there ends up being 3 pages that are identical in this report.

Also, I have an "all" selection for the office criteria (it is a dropdown
box). When I choose this the report generates the info correctly, but it
breaks it out per office, it doesnt total the numbers up under "All" heading.

Please help me figure this out. Thanks
 
Are you using a subreport? Is the record source query of your report
reasonable?
 
Not using subreport. Record Source query is a bit complex, but I dont know
exactly what you mean when you say reasonable.
 
By reasonable, I mean take a look at the record source in datasheet view to
see if there are duplicates or whatever.
 
I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
HAVING (((tblCenterList.CenterName) Like "*") AND ((tblMAIN.Date) Between
#1/1/1904# And #1/1/2175#));
 
Why is the query a totals query when you don't seem to be calculating any
aggregate values (Sum, Avg, Count,...)?

How does your "form that filters the results" work?

Your table structure seems highly un-normalized. Can I assume much of the
fields are calculated in a crosstab or other type of totals query?


--
Duane Hookom
Microsoft Access MVP


DanG said:
I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
HAVING (((tblCenterList.CenterName) Like "*") AND ((tblMAIN.Date) Between
#1/1/1904# And #1/1/2175#));


Duane Hookom said:
By reasonable, I mean take a look at the record source in datasheet view to
see if there are duplicates or whatever.
Click to expand...
Click to expand...
 
I dont know why it set up as a totals query, though since everything was
'Group By' did it really have an effect? I changed it back and the outcomes
seemed the same.
This query is a roll up of other totals queries.

The filters are based from a form that gives the user the opportunity to
choose the center name (or the All option) via a dropdown box and then
start/end date.





Duane Hookom said:
Why is the query a totals query when you don't seem to be calculating any
aggregate values (Sum, Avg, Count,...)?

How does your "form that filters the results" work?

Your table structure seems highly un-normalized. Can I assume much of the
fields are calculated in a crosstab or other type of totals query?


--
Duane Hookom
Microsoft Access MVP


DanG said:
I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
HAVING (((tblCenterList.CenterName) Like "*") AND ((tblMAIN.Date) Between
#1/1/1904# And #1/1/2175#));


Duane Hookom said:
By reasonable, I mean take a look at the record source in datasheet view to
see if there are duplicates or whatever.

--
Duane Hookom
Microsoft Access MVP


:

Not using subreport. Record Source query is a bit complex, but I dont know
exactly what you mean when you say reasonable.

:

Are you using a subreport? Is the record source query of your report
reasonable?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is based upon a form that filters the results. I choose
the name of an office as well as start date and end date. The report totals
the info properly and gets all the boxes filled out good. HOWEVER, it
creates 1 page per record in the report, so if there are 3 records within the
chosen criteria there ends up being 3 pages that are identical in this report.

Also, I have an "all" selection for the office criteria (it is a dropdown
box). When I choose this the report generates the info correctly, but it
breaks it out per office, it doesnt total the numbers up under "All" heading.

Please help me figure this out. Thanks
Click to expand...
Click to expand...
Click to expand...
 
I realize the users can enter criteria values into controls on a form, but
you haven't stated how these values are used to filter the report.

--
Duane Hookom
Microsoft Access MVP


DanG said:
I dont know why it set up as a totals query, though since everything was
'Group By' did it really have an effect? I changed it back and the outcomes
seemed the same.
This query is a roll up of other totals queries.

The filters are based from a form that gives the user the opportunity to
choose the center name (or the All option) via a dropdown box and then
start/end date.





Duane Hookom said:
Why is the query a totals query when you don't seem to be calculating any
aggregate values (Sum, Avg, Count,...)?

How does your "form that filters the results" work?

Your table structure seems highly un-normalized. Can I assume much of the
fields are calculated in a crosstab or other type of totals query?


--
Duane Hookom
Microsoft Access MVP


DanG said:
I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
HAVING (((tblCenterList.CenterName) Like "*") AND ((tblMAIN.Date) Between
#1/1/1904# And #1/1/2175#));


:

By reasonable, I mean take a look at the record source in datasheet view to
see if there are duplicates or whatever.

--
Duane Hookom
Microsoft Access MVP


:

Not using subreport. Record Source query is a bit complex, but I dont know
exactly what you mean when you say reasonable.

:

Are you using a subreport? Is the record source query of your report
reasonable?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is based upon a form that filters the results. I choose
the name of an office as well as start date and end date. The report totals
the info properly and gets all the boxes filled out good. HOWEVER, it
creates 1 page per record in the report, so if there are 3 records within the
chosen criteria there ends up being 3 pages that are identical in this report.

Also, I have an "all" selection for the office criteria (it is a dropdown
box). When I choose this the report generates the info correctly, but it
breaks it out per office, it doesnt total the numbers up under "All" heading.

Please help me figure this out. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Sorry. I am still a little new to Access and these boards.

Here is the module that is linked to the command button.

Public Function Report()
Dim db As Database
Dim qdf As QueryDef
Dim SQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryTOTAL_Y_N_NA")
Form_ParamForm.Start_Date.SetFocus
startDate = Form_ParamForm.Start_Date.Text
Form_ParamForm.End_Date.SetFocus
If (Form_ParamForm.End_Date.Text = "") Then
endDate = Date
Else
endDate = Form_ParamForm.End_Date.Text
End If
Form_ParamForm.Choose_Center.SetFocus
If Form_ParamForm.Choose_Center.Text = "(All)" Then
Center = "*"
Else
Center = Form_ParamForm.Choose_Center.Text
End If

SQL = "SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes, " & _
" qryQUAL_COMBINED.SumOfQual_Cleaned_No ,
qryQUAL_COMBINED.SumOfQual_Cleaned_NA, qryQUAL_COMBINED.SumOfQual_Color_Yes,
qryQUAL_COMBINED.SumOfQual_Color_No, qryQUAL_COMBINED.SumOfQual_Color_NA,
qryQUAL_COMBINED.SumOfQual_Disc_Yes, qryQUAL_COMBINED.SumOfQual_Disc_No, " & _
" qryQUAL_COMBINED.SumOfQual_Disc_NA , qryQUAL_COMBINED.SumOfQual_Frame_Yes,
qryQUAL_COMBINED.SumOfQual_Frame_No, qryQUAL_COMBINED.SumOfQual_Frame_NA,
qryQUAL_COMBINED.SumOfQual_ICAR_Yes, qryQUAL_COMBINED.SumOfQual_ICAR_No,
qryQUAL_COMBINED.SumOfQual_ICAR_NA, qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA, " & _
" qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes ,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No ,
" & _
" qryREIN_COMBINED.Rein_Standards_Yes , qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID " & _
" GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes, " &
_
" qryQUAL_COMBINED.SumOfQual_Disc_No , qryQUAL_COMBINED.SumOfQual_Disc_NA, "
& _
" qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, " & _
" qryREIN_COMBINED.SumOfRein_Action_No ,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" HAVING tblCenterList.CenterName Like """ & Center & """ AND tblMAIN.Date
Between #" & startDate & "# And #" & endDate & "#;"

qdf.SQL = SQL
Set qdf = Nothing
Set db = Nothing

End Function


Duane Hookom said:
I realize the users can enter criteria values into controls on a form, but
you haven't stated how these values are used to filter the report.

--
Duane Hookom
Microsoft Access MVP


DanG said:
I dont know why it set up as a totals query, though since everything was
'Group By' did it really have an effect? I changed it back and the outcomes
seemed the same.
This query is a roll up of other totals queries.

The filters are based from a form that gives the user the opportunity to
choose the center name (or the All option) via a dropdown box and then
start/end date.





Duane Hookom said:
Why is the query a totals query when you don't seem to be calculating any
aggregate values (Sum, Avg, Count,...)?

How does your "form that filters the results" work?

Your table structure seems highly un-normalized. Can I assume much of the
fields are calculated in a crosstab or other type of totals query?


--
Duane Hookom
Microsoft Access MVP


:

I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
HAVING (((tblCenterList.CenterName) Like "*") AND ((tblMAIN.Date) Between
#1/1/1904# And #1/1/2175#));


:

By reasonable, I mean take a look at the record source in datasheet view to
see if there are duplicates or whatever.

--
Duane Hookom
Microsoft Access MVP


:

Not using subreport. Record Source query is a bit complex, but I dont know
exactly what you mean when you say reasonable.

:

Are you using a subreport? Is the record source query of your report
reasonable?

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is based upon a form that filters the results. I choose
the name of an office as well as start date and end date. The report totals
the info properly and gets all the boxes filled out good. HOWEVER, it
creates 1 page per record in the report, so if there are 3 records within the
chosen criteria there ends up being 3 pages that are identical in this report.

Also, I have an "all" selection for the office criteria (it is a dropdown
box). When I choose this the report generates the info correctly, but it
breaks it out per office, it doesnt total the numbers up under "All" heading.

Please help me figure this out. Thanks
Click to expand...
Click to expand...
Click to expand...
 
WOW, first, the code doesn't have to set focus in order to grab a value from
a control
=== snippet =====
'Form_ParamForm.Start_Date.SetFocus commented out
startDate = Form_ParamForm.Start_Date.Value
'Form_ParamForm.End_Date.SetFocus commented out
If IsNull(Form_ParamForm.End_Date) Then
endDate = Date
Else
endDate = Form_ParamForm.End_Date.Value
End If
'Form_ParamForm.Choose_Center.SetFocus
If Form_ParamForm.Choose_Center.Value = "(All)" Then
Center = "*"
Else
Center = Form_ParamForm.Choose_Center.Value
End If
=== end snippet ====

I still think the query is returning too many records or something based on
the joins in the SQL.
--
Duane Hookom
Microsoft Access MVP


DanG said:
Sorry. I am still a little new to Access and these boards.

Here is the module that is linked to the command button.

Public Function Report()
Dim db As Database
Dim qdf As QueryDef
Dim SQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryTOTAL_Y_N_NA")
Form_ParamForm.Start_Date.SetFocus
startDate = Form_ParamForm.Start_Date.Text
Form_ParamForm.End_Date.SetFocus
If (Form_ParamForm.End_Date.Text = "") Then
endDate = Date
Else
endDate = Form_ParamForm.End_Date.Text
End If
Form_ParamForm.Choose_Center.SetFocus
If Form_ParamForm.Choose_Center.Text = "(All)" Then
Center = "*"
Else
Center = Form_ParamForm.Choose_Center.Text
End If

SQL = "SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes, " & _
" qryQUAL_COMBINED.SumOfQual_Cleaned_No ,
qryQUAL_COMBINED.SumOfQual_Cleaned_NA, qryQUAL_COMBINED.SumOfQual_Color_Yes,
qryQUAL_COMBINED.SumOfQual_Color_No, qryQUAL_COMBINED.SumOfQual_Color_NA,
qryQUAL_COMBINED.SumOfQual_Disc_Yes, qryQUAL_COMBINED.SumOfQual_Disc_No, " & _
" qryQUAL_COMBINED.SumOfQual_Disc_NA , qryQUAL_COMBINED.SumOfQual_Frame_Yes,
qryQUAL_COMBINED.SumOfQual_Frame_No, qryQUAL_COMBINED.SumOfQual_Frame_NA,
qryQUAL_COMBINED.SumOfQual_ICAR_Yes, qryQUAL_COMBINED.SumOfQual_ICAR_No,
qryQUAL_COMBINED.SumOfQual_ICAR_NA, qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA, " & _
" qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes ,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No ,
" & _
" qryREIN_COMBINED.Rein_Standards_Yes , qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID " & _
" GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes, " &
_
" qryQUAL_COMBINED.SumOfQual_Disc_No , qryQUAL_COMBINED.SumOfQual_Disc_NA, "
& _
" qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, " & _
" qryREIN_COMBINED.SumOfRein_Action_No ,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" HAVING tblCenterList.CenterName Like """ & Center & """ AND tblMAIN.Date
Between #" & startDate & "# And #" & endDate & "#;"

qdf.SQL = SQL
Set qdf = Nothing
Set db = Nothing

End Function


Duane Hookom said:
I realize the users can enter criteria values into controls on a form, but
you haven't stated how these values are used to filter the report.

--
Duane Hookom
Microsoft Access MVP


DanG said:
I dont know why it set up as a totals query, though since everything was
'Group By' did it really have an effect? I changed it back and the outcomes
seemed the same.
This query is a roll up of other totals queries.

The filters are based from a form that gives the user the opportunity to
choose the center name (or the All option) via a dropdown box and then
start/end date.





:

Why is the query a totals query when you don't seem to be calculating any
aggregate values (Sum, Avg, Count,...)?

How does your "form that filters the results" work?

Your table structure seems highly un-normalized. Can I assume much of the
fields are calculated in a crosstab or other type of totals query?


--
Duane Hookom
Microsoft Access MVP


:

I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
HAVING (((tblCenterList.CenterName) Like "*") AND ((tblMAIN.Date) Between
#1/1/1904# And #1/1/2175#));


:

By reasonable, I mean take a look at the record source in datasheet view to
see if there are duplicates or whatever.
Click to expand...
Click to expand...
Click to expand...
 
I tried using the change you suggested and it returns a blank report now.

Any ideas on what I should be looking for as far as the joins in there?

And I assume that WOW was how impressed you were with my work. :)



Duane Hookom said:
WOW, first, the code doesn't have to set focus in order to grab a value from
a control
=== snippet =====
'Form_ParamForm.Start_Date.SetFocus commented out
startDate = Form_ParamForm.Start_Date.Value
'Form_ParamForm.End_Date.SetFocus commented out
If IsNull(Form_ParamForm.End_Date) Then
endDate = Date
Else
endDate = Form_ParamForm.End_Date.Value
End If
'Form_ParamForm.Choose_Center.SetFocus
If Form_ParamForm.Choose_Center.Value = "(All)" Then
Center = "*"
Else
Center = Form_ParamForm.Choose_Center.Value
End If
=== end snippet ====

I still think the query is returning too many records or something based on
the joins in the SQL.
--
Duane Hookom
Microsoft Access MVP


DanG said:
Sorry. I am still a little new to Access and these boards.

Here is the module that is linked to the command button.

Public Function Report()
Dim db As Database
Dim qdf As QueryDef
Dim SQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryTOTAL_Y_N_NA")
Form_ParamForm.Start_Date.SetFocus
startDate = Form_ParamForm.Start_Date.Text
Form_ParamForm.End_Date.SetFocus
If (Form_ParamForm.End_Date.Text = "") Then
endDate = Date
Else
endDate = Form_ParamForm.End_Date.Text
End If
Form_ParamForm.Choose_Center.SetFocus
If Form_ParamForm.Choose_Center.Text = "(All)" Then
Center = "*"
Else
Center = Form_ParamForm.Choose_Center.Text
End If

SQL = "SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes, " & _
" qryQUAL_COMBINED.SumOfQual_Cleaned_No ,
qryQUAL_COMBINED.SumOfQual_Cleaned_NA, qryQUAL_COMBINED.SumOfQual_Color_Yes,
qryQUAL_COMBINED.SumOfQual_Color_No, qryQUAL_COMBINED.SumOfQual_Color_NA,
qryQUAL_COMBINED.SumOfQual_Disc_Yes, qryQUAL_COMBINED.SumOfQual_Disc_No, " & _
" qryQUAL_COMBINED.SumOfQual_Disc_NA , qryQUAL_COMBINED.SumOfQual_Frame_Yes,
qryQUAL_COMBINED.SumOfQual_Frame_No, qryQUAL_COMBINED.SumOfQual_Frame_NA,
qryQUAL_COMBINED.SumOfQual_ICAR_Yes, qryQUAL_COMBINED.SumOfQual_ICAR_No,
qryQUAL_COMBINED.SumOfQual_ICAR_NA, qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA, " & _
" qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes ,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No ,
" & _
" qryREIN_COMBINED.Rein_Standards_Yes , qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID " & _
" GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes, " &
_
" qryQUAL_COMBINED.SumOfQual_Disc_No , qryQUAL_COMBINED.SumOfQual_Disc_NA, "
& _
" qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, " & _
" qryREIN_COMBINED.SumOfRein_Action_No ,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" HAVING tblCenterList.CenterName Like """ & Center & """ AND tblMAIN.Date
Between #" & startDate & "# And #" & endDate & "#;"

qdf.SQL = SQL
Set qdf = Nothing
Set db = Nothing

End Function


Duane Hookom said:
I realize the users can enter criteria values into controls on a form, but
you haven't stated how these values are used to filter the report.

--
Duane Hookom
Microsoft Access MVP


:

I dont know why it set up as a totals query, though since everything was
'Group By' did it really have an effect? I changed it back and the outcomes
seemed the same.
This query is a roll up of other totals queries.

The filters are based from a form that gives the user the opportunity to
choose the center name (or the All option) via a dropdown box and then
start/end date.





:

Why is the query a totals query when you don't seem to be calculating any
aggregate values (Sum, Avg, Count,...)?

How does your "form that filters the results" work?

Your table structure seems highly un-normalized. Can I assume much of the
fields are calculated in a crosstab or other type of totals query?


--
Duane Hookom
Microsoft Access MVP


:

I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
Click to expand...
Click to expand...
Click to expand...
 
My suggested code replaced a section of your code. My point was that you
don't need to set focus and update the "text" property of a control. Just
change the value without setting the focus. Did you check the query after the
SQL was changed?

--
Duane Hookom
Microsoft Access MVP


DanG said:
I tried using the change you suggested and it returns a blank report now.

Any ideas on what I should be looking for as far as the joins in there?

And I assume that WOW was how impressed you were with my work. :)



Duane Hookom said:
WOW, first, the code doesn't have to set focus in order to grab a value from
a control
=== snippet =====
'Form_ParamForm.Start_Date.SetFocus commented out
startDate = Form_ParamForm.Start_Date.Value
'Form_ParamForm.End_Date.SetFocus commented out
If IsNull(Form_ParamForm.End_Date) Then
endDate = Date
Else
endDate = Form_ParamForm.End_Date.Value
End If
'Form_ParamForm.Choose_Center.SetFocus
If Form_ParamForm.Choose_Center.Value = "(All)" Then
Center = "*"
Else
Center = Form_ParamForm.Choose_Center.Value
End If
=== end snippet ====

I still think the query is returning too many records or something based on
the joins in the SQL.
--
Duane Hookom
Microsoft Access MVP


DanG said:
Sorry. I am still a little new to Access and these boards.

Here is the module that is linked to the command button.

Public Function Report()
Dim db As Database
Dim qdf As QueryDef
Dim SQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryTOTAL_Y_N_NA")
Form_ParamForm.Start_Date.SetFocus
startDate = Form_ParamForm.Start_Date.Text
Form_ParamForm.End_Date.SetFocus
If (Form_ParamForm.End_Date.Text = "") Then
endDate = Date
Else
endDate = Form_ParamForm.End_Date.Text
End If
Form_ParamForm.Choose_Center.SetFocus
If Form_ParamForm.Choose_Center.Text = "(All)" Then
Center = "*"
Else
Center = Form_ParamForm.Choose_Center.Text
End If

SQL = "SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes, " & _
" qryQUAL_COMBINED.SumOfQual_Cleaned_No ,
qryQUAL_COMBINED.SumOfQual_Cleaned_NA, qryQUAL_COMBINED.SumOfQual_Color_Yes,
qryQUAL_COMBINED.SumOfQual_Color_No, qryQUAL_COMBINED.SumOfQual_Color_NA,
qryQUAL_COMBINED.SumOfQual_Disc_Yes, qryQUAL_COMBINED.SumOfQual_Disc_No, " & _
" qryQUAL_COMBINED.SumOfQual_Disc_NA , qryQUAL_COMBINED.SumOfQual_Frame_Yes,
qryQUAL_COMBINED.SumOfQual_Frame_No, qryQUAL_COMBINED.SumOfQual_Frame_NA,
qryQUAL_COMBINED.SumOfQual_ICAR_Yes, qryQUAL_COMBINED.SumOfQual_ICAR_No,
qryQUAL_COMBINED.SumOfQual_ICAR_NA, qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA, " & _
" qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes ,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No ,
" & _
" qryREIN_COMBINED.Rein_Standards_Yes , qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID " & _
" GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes, " &
_
" qryQUAL_COMBINED.SumOfQual_Disc_No , qryQUAL_COMBINED.SumOfQual_Disc_NA, "
& _
" qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, " & _
" qryREIN_COMBINED.SumOfRein_Action_No ,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall " & _
" HAVING tblCenterList.CenterName Like """ & Center & """ AND tblMAIN.Date
Between #" & startDate & "# And #" & endDate & "#;"

qdf.SQL = SQL
Set qdf = Nothing
Set db = Nothing

End Function


:

I realize the users can enter criteria values into controls on a form, but
you haven't stated how these values are used to filter the report.

--
Duane Hookom
Microsoft Access MVP


:

I dont know why it set up as a totals query, though since everything was
'Group By' did it really have an effect? I changed it back and the outcomes
seemed the same.
This query is a roll up of other totals queries.

The filters are based from a form that gives the user the opportunity to
choose the center name (or the All option) via a dropdown box and then
start/end date.





:

Why is the query a totals query when you don't seem to be calculating any
aggregate values (Sum, Avg, Count,...)?

How does your "form that filters the results" work?

Your table structure seems highly un-normalized. Can I assume much of the
fields are calculated in a crosstab or other type of totals query?


--
Duane Hookom
Microsoft Access MVP


:

I dont see anything wrong here, but I figured I'd post it just to get extra
eyes on it.

When I select individual office I get the results I desire, when I select
All I want it to add all the office #'s together in one report.

Thanks

SELECT tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
qryQUAL_COMBINED.SumOfQual_Color_NA, qryQUAL_COMBINED.SumOfQual_Disc_Yes,
qryQUAL_COMBINED.SumOfQual_Disc_No, qryQUAL_COMBINED.SumOfQual_Disc_NA,
qryQUAL_COMBINED.SumOfQual_Frame_Yes, qryQUAL_COMBINED.SumOfQual_Frame_No,
qryQUAL_COMBINED.SumOfQual_Frame_NA, qryQUAL_COMBINED.SumOfQual_ICAR_Yes,
qryQUAL_COMBINED.SumOfQual_ICAR_No, qryQUAL_COMBINED.SumOfQual_ICAR_NA,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_Yes,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_No,
qryQUAL_COMBINED.SumOfQual_Nec_Parts_NA,
qryQUAL_COMBINED.SumOfQual_Order_Timely_Yes,
qryQUAL_COMBINED.SumOfQual_Order_Timely_No,
qryQUAL_COMBINED.SumOfQual_Order_Timely_NA, qryQUAL_COMBINED.[SumOfQual_Part
Price_Yes], qryQUAL_COMBINED.[SumOfQual_Part Price_No],
qryQUAL_COMBINED.[SumOfQual_Part Price_NA], qryQUAL_COMBINED.[SumOfQual_R &
I_Yes], qryQUAL_COMBINED.[SumOfQual_R & I_No], qryQUAL_COMBINED.[SumOfQual_R
& I_NA], qryQUAL_COMBINED.SumOfQual_Refinish_Yes,
qryQUAL_COMBINED.SumOfQual_Refinish_No,
qryQUAL_COMBINED.SumOfQual_Refinish_NA, qryQUAL_COMBINED.SumOfQual_Welds_Yes,
qryQUAL_COMBINED.SumOfQual_Welds_No, qryQUAL_COMBINED.SumOfQual_Welds_NA,
qryREIN_COMBINED.SumOfRein_Action_Yes, qryREIN_COMBINED.SumOfRein_Action_No,
qryREIN_COMBINED.Rein_Standards_Yes, qryREIN_COMBINED.Rein_Standards_No,
qryREIN_COMBINED.Rein_Standards_NA, qryREIN_COMBINED.VehRating10,
qryREIN_COMBINED.VehRating15, qryREIN_COMBINED.VehRating20,
qryREIN_COMBINED.VehRating25, qryREIN_COMBINED.VehRating30,
qryREIN_COMBINED.VehicleRatingTotal, qryREIN_COMBINED.Qual_RvR_Dec_Yes,
qryREIN_COMBINED.Qual_RvR_Dec_No, qryREIN_COMBINED.Qual_RvR_Dec_NA,
qryREIN_COMBINED.Rein_Volume_Yes, qryREIN_COMBINED.Rein_Volume_No,
qryREIN_COMBINED.Rein_Vloume_NA, qryOVERALLSCORES.OVERALL10,
qryOVERALLSCORES.OVERALL15, qryOVERALLSCORES.OVERALL20,
qryOVERALLSCORES.OVERALL25, qryOVERALLSCORES.OVERALL30,
qryOVERALLSCORES.TotalOverall
FROM tblCenterList INNER JOIN (qryOVERALLSCORES INNER JOIN
(((QryPHYSAPP_COMBINED INNER JOIN qryQUAL_COMBINED ON
QryPHYSAPP_COMBINED.Center_ID = qryQUAL_COMBINED.Center_ID) INNER JOIN
qryREIN_COMBINED ON qryQUAL_COMBINED.Center_ID = qryREIN_COMBINED.Center_ID)
INNER JOIN tblMAIN ON qryREIN_COMBINED.Center_ID = tblMAIN.Center_ID) ON
qryOVERALLSCORES.Center_ID = QryPHYSAPP_COMBINED.Center_ID) ON
tblCenterList.CenterID = tblMAIN.Center_ID
GROUP BY tblCenterList.CenterName, tblMAIN.Center_ID, tblMAIN.Date,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Floor_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Friendly_NA,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_Yes,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_No,
QryPHYSAPP_COMBINED.SumOfPhysApp_Parking_NA,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_Yes,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_No,
qryQUAL_COMBINED.SumOfQual_Addl_Ops_NA, qryQUAL_COMBINED.SumOfQual_Blend_Yes,
qryQUAL_COMBINED.SumOfQual_Blend_No, qryQUAL_COMBINED.SumOfQual_Blend_NA,
qryQUAL_COMBINED.SumOfQual_Cleaned_Yes,
qryQUAL_COMBINED.SumOfQual_Cleaned_No, qryQUAL_COMBINED.SumOfQual_Cleaned_NA,
qryQUAL_COMBINED.SumOfQual_Color_Yes, qryQUAL_COMBINED.SumOfQual_Color_No,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top