Ms query - left join with multiple tables

[largeselection]

Hello,

So as part of a VBA project I'm working with in Excel, I am trying to simplify some SQL writing, but I can't get the Left Joins to work.

Basically, I have many tables and I want to be able to pull information from all of them. They are set up in the following way.

MASTER_TABLE:
[ID#], [NAME], [ADDRESS]

FEBRUARY_TABLE:
[ID#], [PURCHASES]

MARCH_TABLE:
[ID#], [PURCHASES]

APRIL_TABLE:
[ID#], [PURCHASES]

ETC BY MONTHS

There may be some ID#s that are in FEB, but not MARCH, or in MARCH, but not APRIL or FEB, etc...

Ideally I wanted to do a full outer join between ID# in all four (or more) tables, but I think this might be too complex.

So as an alternative it occured to me that since my MASTER_TABLE should contain all the possible [ID#]s I could probably accomplish this with a left join.

So I can't do this graphically, but I think it is possible with SQL (I am a very beginner with SQL). I want to be able to pull a query which lists all the [ID#]s from the MASTER_TABLE and then the [PURCHASES] for FEB, MARCH, AND APRIL.

Tables might look like this:
MASTER_TABLE
ID/ADDRESS
101/XXX
102/YYY
103/ZZZ
104/AAA
105/BBB
106/CCC
107/DDD

FEB_TABLE
ID/PURCHASES
101/50
102/25
103/35

MAR_TABLE
101/25
104/50
105/10

APR_TABLE
101/50
106/10
107/100

And I would want the query to look like:
ID/FEB/MAR/APR
101/50/25/50
102/25/0/0
103/35/0/0
104/0/50/0
105/0/10/0
106/0/0/10
107/0/0/100

Any idea how to get this done? Or any helpful links/books I should consult?

Thanks!

 

[Fazza]

Also,

Is it possible to combine this with a Left Outer join on a table which has information about each [ID#]?

So say for example in my [ID#] table I have the field [M/F], I would want to be able to run the crosstab on the [purchases] info and be able to bring in the M or F values from the MASTER_TABLE.

Yes. Join the tables and run the cross tab from that. Or maybe better if you're doing something more complex is to use a pivot table.

Cross tab SQL would be like below. HTH

TRANSFORM sum(whatever)
SELECT row field/s
FROM (join multiple tables and do what
you like as a standalone query
all enclosed within parentheses)
WHERE you can add criteria too
GROUP BY row field/s
PIVOT pivotfield

 

[Fazza]

Also, I tried to do a left join with 3 tables and I keep getting syntax or join not supported errors.

This is what I'm using (all of the fields are unique names):

SELECT MASTER_ID, TABLE1_PURCHASE, TABLE2_ASSOCIATION
FROM {oj `C:\DATA1`\MASTER.csv MASTER
LEFT OUTER JOIN `C:\DATA1`\TABLE1.csv TABLE1
ON MASTER_ID = TABLE1_ID
LEFT OUTER JOIN `C:\DATA1`\TABLE2.csv TABLE2
ON MASTER_ID = TABLE2_ID}

Based on what I've read online and in different spots I don't see why I keep getting errors...

Suggest you add some parentheses to explicitly identify the items being joined. Check too that LEFT OUTER JOIN is supported - I'm not sure offhand. If that doesn't help, please post some sample data & I'll have a look.

 

[largeselection]

Left outer join is the way I've done it in the past...but I can't seem to get this right even when I tried a bunch of combinations of parentheses to make it more clear. I keep getting join not supported. I also have access to office10 with powerpivot, so far I think this current method makes the most method for this project though. I find it more cumbersome to select tables and define relationships in powerpivot vs ms query. Since in MS query as I save files to a folder, they appear as available tables to query in my connection and so far I don't know how to create a connection to a whole folder in powerpivot.

 

[raulitoxd]

This Query works for me, you should consider Parentheses ( )


SELECT e.[evaluador] FROM ( [Evaluador$] e
inner JOIN [EvaluadorRequerimientos$] er ON e.[IDEvaluador]=er.[IDEvaluador] )
inner join [Requerimientos$] r ON r.[IDRequerimiento]=er.[IDRequerimiento]