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]

If you can, can you start again with different tables? Not one table for each month! The present structure is bad. If you change to a good data structure, it will be MUCH MUCH easier in the long run.

 

[largeselection]

That might be an option, but how would you recommend restructuring?

 

[Fazza]

new table with fields: PurchaseMonth, ID, Purchase

 

[largeselection]

I can probably swing that, but then how can I query the sales with each month next to each other?

 

[Fazza]

For a result like at the end of the first post, use a cross tab query. Something like

TRANSFORM Sum(Purchase)
SELECT ID
FROM table
GROUP BY ID
PIVOT PurchaseMonth

There will be examples in old forum posts. OK?

 

[Fazza]

And if you wanted, you can specify the months. With,

TRANSFORM Sum(Purchase)
SELECT ID
FROM table
GROUP BY ID
PIVOT PurchaseMonth IN (list the months you want in the order you want)

 

[largeselection]

I'll give it a whirl thanks for your insight. I'll post how it goes after a play with it for a bit. Thanks again!

 

[Fazza]

Great. It takes a little time to become familiar with SQL, but basically it is simple enough. I'm sure you'll gain enormously from the 'play'. This is where I started learning SQL http://www.w3schools.com/sql/default.asp

I googled and found MS has this specifically for MS Query, http://support.microsoft.com/kb/136699

MS Query also has built-in help.

 

[largeselection]

Yea I definitely read that article from ms on joins. I will definitely check out the resource you provided and I purchased a basic book on SQL. Thanks so much!