using two queries in a report

[Alliana Gray]

Okay, so I have a table, with fields like Location, Waste produced, and Accident Frequency.

There are several US locations and for certain reasons I've had to put 'Summary US' and 'Summary Canada' as choices for location.

When creating a summary for the US, I can do the sum of fields pretty easily. However, for things like Accident Frequency, I don't want to add all the accident frequency from all the locations, I just need to display the one that has Summary US as a location. I managed to do this in my Summary Canada report by changing the field to a combo box, and editing the row source and then creating a query that narrows my results to my specifications but I can't duplicate it in the US report.

If needed, I can attach the database, but I'm trying to with this initial post and it says I can't upload it because of a security something or other. Need help with that as well.

I've tried basing the report off of two queries but I get this error 'The specified field [field name] could refer to more than one table listed in the FROM clause of your SQL statement.'

[blazerboy6]

Have you considered a main report that contains the information from both (Waste produced etc) and sub-reports for the US Accident Summary and Canadian Accident Summary? If produced properly, the whole thing will look like one report in report view/print preview, but the data from the queries will be able to be separate for US and Canada for each subreport.

[Alliana Gray]

I can't figure out how to do the subreports ):

[blazerboy6]

No worries, it took me a bit to figure it out with one of my reports, and I had over 10 subreports in it!

Set up your two queries independant of one another, one for US summaries and one for Canadian summaries for Accident Freq. Once those queries work the way you want (by year, quarter etc) create a simple report for each one that has just the Summary data field in it. Basically a single control (ie text box) with no label (see attached Screenshot). I have my summary of brochures distributed in a given quarter, but only display that field in the subreport.

In your Main Report, you can now add that subreport as a "line" by clicking subform/subreport under Controls in the Design Tab. Give the new subreport the label you want to see, and voila, it can look just like any other field you have in your Main Report.

The important part is setting your query up correctly, with the same basic parameters you run your main report in. My example is filtered by Quarter, as the Main Report is a quarterly report. The subreport is quarterly as well, it's just not displayed as a field in the subreport. When the subreport is run on it's own, it looks like a series of numbers (which are the sums) and seems useless since there's no formatting, but when it's inserted and run as part of the Main Report, it makes sense.

Play around with the subreports and queries, hope it works for you. If not, try to post a copy of your database (you need to zip it to get it on here).

[Alliana Gray]

It worked!!! Thank you soooo much!

[Alliana Gray]

However I'm having trouble sorting it by month... I set up the query just like my main query but when I run my report, let's say the report lists from Jan-Dec but there is only accident information for June... it's displaying the same info from June for all 12 months. How can I get it to display the proper monthly data in the correct month?

[blazerboy6]

Hmm...

It shouldn't be filtering like that, so I'm not sure exactly what settings in either the report/subreport or the query are causing this issue.
1) Is it displaying only June for both US and Canadian subreports? I figure if you have a single "Canada location" to sum, vs multiple "US locations" to sum, that the queries for the two should be slightly different...

2) Does the subreport, when run independent of the main report, give you only a single line, or multiple lines? You don't want the query for the subreports to filter date range as well, leave that up to the main report. You just want the query the subreport is based on to sum by month, but not to have a filter criteria for date range added, since your main report already has a filter criteria for the year you want to see.

3) Does the main report launch from a form where you input the calender year? The issue may be in how the main report selects the date range to filter by... but you don't want your subreport to do that as well, it could be double filtering itself.

[Alliana Gray]

I left the Canada report alone since I got it to work correctly with the combo box option...

when I rub the subreport, it only displays one line because there is only one line of data for that field currently.

[Alliana Gray]

Here, I finally figured out how to attach the database...

I got it to sort of work. Now it displays all the results but it displays all of it in the field.. like the data from 5 months in one field in the May section of the report...

The name of the report I need help with is 'US Summary'... if you could perhaps look at the 'Canada Summary' and check out how I managed to use combo boxes to suit my needs and maybe figure out why it won't work with the US.. that would be sooooo great!

[blazerboy6]

Hey Alliana,

I think I've figured out what your issue is the us reports.

A)
West Chicago.

the Query for west chicago had additional tables inserted in design view, but the fields were only based on the sustainability matrix. If you pull out all tables and queries but the sustainability matrix, it did not duplicate the data for me, otherwise it repeated march 4 times, april 4 times, etc... that may have lead to replication of data for you if anything was based on that query.

B)
As far as the US Summary goes, I don't mean to sound critical AT all. It's very easy to look at someone else's database and find issues. I think the main issue is in how the queries are structured. I included a couple of examples within the edited database attached. The reason why the Canadian Summary worked was that there were no expressions written into the query, but I still think the queries and reports need to be restructured to function properly (See Also: Complete Canadian Summary Temp).

It will probably be fastest to start with a new query for each and base new reports on them. In the two TEMP queries inserted (See: Temp Sum US and Temp Sum CANADA) I used [Location] to filter by with a WHERE condition, GROUPed BY the [YTD], and SUMmed the other fields (I only inserted the first four fields for times sake See Also: Complete Canadian Summary Temp, but you can add all of them later). This will mean your query is only 4 lines long, one line for each month (5 for Canadian with this record set) with the sums for each field in that line. When you create your report, you can use the same structure you currently have, just base it on the new query. Each field within the report will then be referring to the new query, and not the old one. If you need the report to display calculations of specific fields, in the control source for that fields property in the report, you can enter in the calculations you need.
(ie: instead of Expr1: [curRecyc]/[lbsRecyc] as a new field in the query, you can input =[curRecyc]/[lbsRecyc]into a new control on the report)
That way the data will still be summed properly in the query, and it shouldn't give you issues when running the report.

Let me know if this makes sense, and I can clarify my thoughts. Sorry to recommend more work, but it should be pretty strait forward following this approach to query/report. The only time consuming process should be formatting the report the way you want it. You can also individually go through the controls in the current report and change them to refer to the new query, but I think that would take you more time than starting from scratch...

Hope this helps,