Calculations involving data from two different queries

[deltauser2006]

I am trying to find a way to do a simple calculation with data from two
different subreports. The data is calculated in two different
subreports in the first place because, to my knowlege, there is no way
to join the data together under one query. "Subreport A" produces an
average rent rate for an apartment building based on the most up to
date data available which is located in "Table A". "Subreport B"
produces an average rent rate for the same apartment building except
the data comes from "Table B" (a table where older data is stored).
The number "Subreport B" produces is the average rent rate for the
apartment building one year ago. Since data is being pulled from two
different places and is not identical I haven't found a way to join the
two together in a query to do any calculations. I am looking to just
divide one number by the other to produce a percent increase from one
year to the next. I'm hoping there's a simple way to do this that I'm
just missing. Please let me know if you know how to go about this.
Thanks!

 

[Guest]

Hi

Would it not be possible to bring both tables (A and B) into your query
design grip (no join) save the query then in a new column add a formula to
add the 2 rents together. – I have added but of course you could divide,
produce an average, subtract or many, many other ideas. (change the name
Expr1 to what you want)

SELECT (([TableA]![AverageRent] / [TableB]![AverageRent]) / 100) AS Expr1
FROM TableA, TableB;

If, as you say you can’t bring Table A and Table B into the design of your
form’s base query. You could make a separate query just based on these two
tables and then bring “this new query†into the form’s design grid and use a
Left Join (using the apparment’s ID – or whatever the primary key is)

It would look something like this

SELECT TableA.RentID, TableA.AverageRent, TableB.RentID, TableB.AverageRent,
(([TableA]![AverageRent] / [TableB]![AverageRent]) / 100) AS Expr1
FROM TableA LEFT JOIN TableB ON TableA.RentID = TableB.AverageRent;