Prev: Variable as FieldName to populate a combobox
Next: MS ACCESS
From: j.t.w on 17 Jan 2010 17:46
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
From: Duane Hookom on 17 Jan 2010 19:55
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 Hookom
Microsoft Access MVP


"j.t.w" wrote:

> 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
> .
>
From: j.t.w on 19 Jan 2010 20:25
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
 | 
Pages: 1
Prev: Variable as FieldName to populate a combobox
Next: MS ACCESS