Calculations Across Tables

  • Thread starter Thread starter bgetson
  • Start date Start date
B

bgetson

Right now, I've got 5 different tables (A-E) that each have a field
for a Date, Name, and Profit.

I want to find a way to calculate each record's profit as a percentage
of the maximum across all 5 tables, restricted by records of the same
date. On top of that, I only want to display the records for the most
recent date.

I'm new with SQL, so I haven't yet figured out the syntax to deal with
unions and aggregate functions.

Any help is greatly appreciated.
-bgetson
 
Maybe you should consider consolidsting your five tables into one table
first. From the description in your post it's the right thing to do. Then
pulling out the data you want is simple!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Thanks for the input. I would combine all of the data, but it helps in
other calculations to leave them separate.

For example, right now I have 5 separate queries, one for each table,
that does the same percentage calculation, but based on the maximum
within the table - instead of across all 5 tables. It seemed to me
that it would be easier to analyze the data if the current
distinctions are maintained.

On the other hand, I can keep the idea of merging the data as a
backup, because I'm pretty sure I'm capable of handling those
statements.

Do you have any suggestions for my original question?
 
Right now, I've got 5 different tables (A-E) that each have a field
for a Date, Name, and Profit.

I want to find a way to calculate each record's profit as a percentage
of the maximum across all 5 tables, restricted by records of the same
date. On top of that, I only want to display the records for the most
recent date.

I'm new with SQL, so I haven't yet figured out the syntax to deal with
unions and aggregate functions.
Click to expand...

I fully agree with Steve about merging the data. It's actually simpler to
calculate percentages based on a subset of the data than to merge multiple
tables as you're doing now!

You'll need to create a UNION query selecting the date, name and profit fields
from all five tables; base a TOTALS query on this UNION query finding the
maximum the profit; join this totals query to *another* union query selecting
the specific values; do the division... and you'll be durned lucky if you
don't get a Query Too Complex error!

John W. Vinson [MVP]
 
Alright..... I guess I can't argue with the people who know how to do
this stuff. Plus, I can always create queries to re-establish my
original distinctions among the tables. Thanks for your help.

-bgetson
 
<<....the same percentage calculation, but based on the maximum within the
table - instead of across all 5 tables. >>

With one table, all you need for these calculations is a query with an
appropriate criteria to limit the data to what you find now in the table
that you are doing your calculation on.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Thanks for the input. I would combine all of the data, but it helps in
other calculations to leave them separate.
Click to expand...

bgetson,

I am interested in hearing what is *easier* when they are kept as
separate tables.

(Because I agree with Steve and John Vinson about keeping only one
table if the column names are all the same (and adding one more column
for type/category)).

Sincerely,

Chris O.
 
The thing with Access is that people tend to do something that "looks" right
and are resistant to what is actually correct, even though it may look more
complicated. In the long run it is far easier to deal with.

Have you sorted it out?
 
Back
Top