Calculations Across Tables

[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

 

[Steve]

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)

 

[bgetson]

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?

 

[John W. Vinson]

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.

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]

 

[bgetson]

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

 

[scubadiver]

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?