[AccessD] Difficult calculations

I would recommend the use of one or more temporary tables to process the
data.  It looks like from your description that he needs to use data from
fields in different records in one calculation.

Maybe something that would first put all of the VUH Inpatient Revenue JanYTD
data into the work table.  The another query that would add the VUH
Outpatient Revenue JAN YTD data to the corresponding work record, etc.

Then he would have one or more final queries that would take all of the data
in the one record needed for a given calculation and calculate it.

Is that clear as mud now?  LOL.

Bobby


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gina Hoopes
Sent: Tuesday, March 07, 2006 12:13 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Difficult calculations


A co-worker has asked for my help and I'm stumped. Perhaps one or more of 
you can help me figure out how he can accomplish what he needs. The way his 
table is laid out is the main problem, but it serves him in all of the other

ways he reports on the data. He's got the following fields: StmtType, 
Category, and 36 fields with numbers (JanYTD, JanLYB, JanCYB for all 12 
months). The StmtType field has the primary designation (VUH, PHV, VCH) and 
the Category field has the secondary designation (Admissions, Discharges, 
Patient Days, Inpatient Revenue, Outpatient Revenue). So for each primary 
designation he has each of the secondary designations. So, a table row looks

like VUH/Admissions/JanYTD/JanLYB/JanCYB/... then 
VUH/Discharges/JanYTD/JanLYB/JanCYB ... I hope that makes sense since I 
don't know how to represent it graphically with plain text.

Now here's what he needs:
(VUH Inpatient Revenue JanYTD + VUH Outpatient Revenue JAN YTD) divided by 
VUH Inpatient Revenue Jan YTD = X - (X is his ratio)
X times VUH Patient Days Jan YTD
X times VUH Admissions Jan YTD
X times VUH Discharges Jan YTD

The calculations have to take place for each of the 36 values. I would be 
fairly simple for him to accomplish with queries if there were only a couple

of values but with 36 columns for each primary/secondary combination it's 
way too cumbersome to accomplish that way. Can anyone help me figure out if 
he can get from here to there?

Thanks so much,
Gina