Calculations using figures in two different reports to produce a third

[grg]

Am very novice user. Have two different reports produced from two different simple select queries. One report contains many categories of statistics, displaying sums totalled from input table. Other report contains cost rates selected by project, produced by simple select query from input table. I need to know the MOST SIMPLE way to produce a third report containing the results of computations using the statistical totals from the first report and the rates from the second report (most computations are simply multiplications of a figure in first report by a figure from the second report, occasionally with variation of combinations of figures in a formula.) Thought the obvious way to do it was to create a third query, then display it, however haven't yet figured out how to do that (novice, remember)but it seems overly complex for such a simple objective. Even tried to form a link to Excel, do the computations, then produce a report from that. Again, havent figured out how to do it but indications are that it is relatively complex thing to do and generates need for other software (Excel) and large cumulative file when I need to keep this thing as simple as possible. I need a simple tool to use, not get lost manipulating software that I barely understand. Would appreciate help. Thanx

 

[randysmid]

grg,
One of the most popular techniques is to create a 3rd query, and use the original two queries as the source. Then, in the 3rd query, link the two queries together based on some common element.
All of the calculations from the two queries will now be available to the new report, which should have the 3rd query as its recordsource.
Please respond to this post if you encounter any troubles.
HTH,
Randy Smith
California Teachers Association

 

[grg]

Thanx for response. I thought that was the best approach but haven't figured out how to do it yet. The resulting report I get so far just reflects one of the inputs, i.e. I haven't learned how to create the third query so that it caries thru to the report. Got all wrapped up in Expression Builder, etc. Boils down to my lack of knowledge so far in producing a query of queries. That's why I was investigating working directly with the reports. My new field names don't work so there's something I haven't learned yet. This thing is onerous in my case because there are about twenty-five or so figures in each report that will produce about thirty results. Thanx again.... GRG

 

[randysmid]

Hi,
You probably don't want to do this in SQL code right now. This should be relatively easy to do in the Query tool. Here are the steps to follow:
1) Click on the Query object, then click New
2) Select Simple Query Wizard
3) Select the first query, then select the fields you want to include
4) Before clicking Next, select the other query and pick those fields that you want. (be sure to pick the fields that link the tables together)
5) Save the query.
6) Go to the new report (or create one if you haven't done so already), and use the query as the source of the data.
HTH,
Randy Smith
California Teachers Association

 

[grg]

randy, Thanx, your suggestion worked well. finally discovered my error in doing it. constructed several queries. some are large. now getting error message "overflow" when try to display last one. no explanation. haven't yet figured out what that one means.
GRG