Crosstab guery from multitables

[elena]

Hi, All
i need help with crosstab query which combine data from different tables,
where in first two tables fld1 related to field "shortname" in the third
table. Is it possible at all? Please, help

Example:
Table 1:
fld1 fld2 fld3
Len1 3/3/2008 100
len2 4/1/2008 200
lena1 1/1/2008 150
lena3 2/1/2008 224
Lee 1/1/2008 500

Table 2:
fld1 fld2 fld3
lee 6/3/2008 96700
lee1 2/1/2008 1200
lee2 3/1/2008 1650
lee1 1/4/2008 22554
Lee1 1/9/2008 55700

Table 3: (short/long naming)
Len1 Lena
len2 Lena
lena1 Lena
lena3 Lena
lee Leeza
lee1 Leeza

Result that I need:
Jan(tbl1) Jan(tbl2) Feb(tbl1) Feb(tbl2) Etc…
Lena sum() sum()
Leeza sum() sum()

 

[karl dewey]

Is it possible at all? Yes. Post the actual table and field
names.

 

[elena]

Hi, Karl
Thank you for reply:
I have Tab1 and Tab 2, both tables with the same fields
fld1 fld2 fld3
Len 1/1/08 300
Len1 2/1/08 400
Lee 4/1/08 200

Tab3:
ShorName LongName
Len Lena
Len1 Lena
Lee Leeza

Table 1:
fld1, fld2, fld3

Table 2:
fld1, fld2, fld3

Table 3:
ShortName, LongName

It works fine with one table at the time, but how i can combine two table to
get result like this:
Jan(Tab1) Jan(Tab2) Feb(Tab1) Feb(Tab2)

Lena 500 200 400 300

My crosstab query has another lookup query as source:

LookUp1 query:
SELECT Tab1.fld1, Tab1.fld2, Tab1.Total, Tab3.LongName
FROM Tab3 INNER JOIN Tab1 ON Tab3.ShortName = Tab1.fld1;

CrossTab query:
TRANSFORM Sum(LookUp1.fld3) AS SumOffld3
SELECT LookUp1.LongName, Sum(LookUp1.fld3) AS [Total Of fld3]
FROM LookUp1
GROUP BY LookUp1.LongName
PIVOT Format([fld2],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");