Combine Two Queries in Different Layouts

  • Thread starter Thread starter j.t.w
  • Start date Start date
J

j.t.w

Hi All,

I'm trying to figure out a way to combine 2 queries that are in
different layouts. Initially, the 2 tables come from spreadsheets,
which I import as they are. Then I create 2 summary queries which
result in the example seen below.

Table20_Summary
Prog Grp 2008 2009 2010 2011
1000 FFA 150 160 155 162
1000 FFM 175 180 170 183
1000 FFF 200 220 230 225
1000 FFW 300 320 330 325

TablePB_Summary
Prog FFA FFM FFF FFW
1000 157 178 222 321

I would like the end result like this...
Prog Grp 2008 2009 2010 2011 PB
1000 FFA 150 160 155 162 157
1000 FFM 175 180 170 183 178
1000 FFF 200 220 230 225 222
1000 FFW 300 320 330 325 321

Can someone give me an idea on how to accomplish this or put me on the
right path?

The TablePB_Summary is my baseline value and the Table20_Summary is my
currently budgeted values. I'm actually trying to show the difference
between the baseline and budgeted value by year (shown below)..

Prog Grp 2008 2009 2010 2011 PB Diff2008 Diff2009
Diff2010 Diff2011
1000 FFA 150 160 155 162 157 7
-3 2 -5
1000 FFM 175 180 170 183 178 3
-2 8 -5
1000 FFF 200 220 230 225 222 22
2 -8 -3
1000 FFW 300 320 330 325 321 21
1 -9 -4

Thanks in advance for any and all help.

j.t.w
 
I expect you could create a normalizing union query from tablePB_Summary with
SQL like
SELECT Prog, FFA as PB, "FFA" as Grp
FROM tablePB_Summary
UNION ALL
SELECT Prog, FFM, "FFM"
FROM tablePB_Summary
UNION ALL
SELECT Prog, FFF, "FFF"
FROM tablePB_Summary
UNION ALL
SELECT Prog, FFW, "FFW"
FROM tablePB_Summary;

Then create a standard join query from the union query and Table20_Summary.
 
Duane,

Thank you for your help. It works as you layed it out. I was hoping
there was something like a reverse cross tab query that would get the
job done.

Thanks again,
j.t.w
 
Back
Top