Multiple criteria query

[jwb257]

Hello,

I have a database tracking economic impact data. I have a separate table for Localities and Towns. Localities have a FIPS code that is the primary key. There are multiple towns that go into each Locality (tied by the FIPS). I have another two tables that hold the economic data for Localities and Towns. I am trying to add the Towns economic data to Localities. I have a query that matches the town to localities with several criterion (Economic category, Year and FIPS). I can't figure out how to add the data when there are more than one town to a Locality. I have tried different queries and also a report. With the report, I was able to set it up so it shows the town data(subreport) under the Locality (main report). However, I have been unable to sum the Locality amount with the total Town amount (I believe it's because you can't sum from a calculated text box). I do not have advanced Access skills so any assistance would be much appreciated.



Thank you!
JB

[June7]

What is the calculation? Can you provide project for analysis? Make copy, remove confidential data, run Compact & Repair, zip if still large.

[jwb257]

Thank you I have uploaded the zip file. In the economic impact data report, I would like the town data (subreport) added to the locality data. When running the report, use FIPS 51195 as this Locality has multiple towns to add. Also, I don't know whether I should be running a query for this instead of calculating it in a report? Please let me know if you have any questions.

Thanks!
JB

[June7]

Your code to not display subreport only works in PrintPreview (or when sent directly to printer). This is by Access design. However, even though the report doesn't show, big blank space will.

Do you want town and locality added for each year/category/subcategory level or just one grand total at end of report?

[jwb257]

Is there a way to remove the blank space?? I don't want the box or the space if there is no data. I would like the town and locality added for each year/category/subcategory.

thank you so much for your assistance!

[June7]

Here is what I have discovered about your report:

1. The HasData code is not needed.

2. Far as I know there is no way to reduce the blank space left when subreport has no data. Even if there is no data, the subreport container control is still there and takes up space.

3. Two ways to sum the Town and Localites.
a. Name the subform container control different from the form, like ctrTowns.
Have a not visible textbox on the subform ReportFooter with ControlSource of: =[TownAmountTotal]
Have a textbox on the main report Detail below the subform with ControlSource of:
=[Amount]+[Reports]![rptEconomicImpactData-byFIPS]![ctrTowns]![tbxTownTotal]
Unfortunately, if subreport has no data the textbox is still visible and shows error.
b. Have a textbox on the subreport ReportFooter with ControlSource of: =[TownAmountTotal]+[Reports]![rptEconomicImpactData-byFIPS]![Amount]

[jwb257]

It worked! Thank you so much for your help June!

JB