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]

I'm a big fan of using SQL (as many old posts confirm). It is a great technique. Please post again as required.

What about VBA? Are you using that or just manually? I guess you know that pivot tables can also be built using SQL?

 

[Fazza]

I re-read your initial post & see you are using VBA already.

That is good, because without VBA is a little lacking in efficiency.

 

[largeselection]

Yes I'm trying to use VBA to try and speed some things up and maybe make a decent front end to select the data I need to see, Im definitely going to look through some of your history to see if I can pick up some tips/pointers with regard to SQL and vba integration.

 

[Fazza]

You might find ADO very suitable.

Maybe using MS Access is more natively suited to the task?

 

[largeselection]

Unfortunately I don't have access to access. I'm limited by somewhat limited systems. But I've been digging deeper into SQL etc because I had some success accessing information by maintaining a collection of csv files and accessing them via SQL as if they were relational tables.

 

[Fazza]

I've done some good things using Excel files too. Such as multiple users in various locations updating forecast files. Users liked to use Excel files so the system was built around them. (I'm pretty much a novice at Access.) One trick was querying open files isn't possible so I had each forecast file create, on file closure, a simple data file in a sub-directory. These data files were never used by anyone else, so I could rely on them being closed. Querying multiple files was fast and, like you describe, it worked as if these separate files were a relational database. SQL is awesome for this sort of work. Have fun.

 

[largeselection]

The Crosstab query works very well, but is it possible to pull more than one field?

For example, It works great to pull [PURCHASES] with [MONTHS] as the x-axis headers, but what if I want to pull another field [TYPE OF PURCHASE]

So that it would have
ID/FEB PURCHASE/FEB PURCHASE TYPE/MAR PURCHASES/MAR PURCHASE TYPE

for each record.

I've tried a couple different variations of adding in the [TYPE OF PURCHASE] field in the TRANSFORM section at the top, but to no avail.

 

[largeselection]

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.

 

[largeselection]

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...

 

[Fazza]

The Crosstab query works very well, but is it possible to pull more than one field?

For example, It works great to pull [PURCHASES] with [MONTHS] as the x-axis headers, but what if I want to pull another field [TYPE OF PURCHASE]

So that it would have
ID/FEB PURCHASE/FEB PURCHASE TYPE/MAR PURCHASES/MAR PURCHASE TYPE

for each record.

I've tried a couple different variations of adding in the [TYPE OF PURCHASE] field in the TRANSFORM section at the top, but to no avail.

For mutliple column fields, suggest you do not use cross tab queries but instead use a pivot table. They offer tremendous built-in flexibility. OK?