Joining Multiple Tables

[Guest]

I have three tables with the same amount of fields. They are Source,
destination and volume.

I'm trying to show trends from the source to the destination over a three
month period. However there are months where there will be no data for a
particular location or the data will start that month. So basically I want
all records from each table in a new table, but I want it so that there are
two new columns for each source and destination.

The new table would look something like
Source Destination Month 1 Month 2 Month 3
Loc A Loc B 8700 8900
Loc A Loc C 7000 3400
Loc A Loc D 2000 3000 6000
Loc A Loc E 5000 7000

So not one table will have all the records that I want. I have tried joins
threw Access, but it say only from one table to the next not all records from
both tables.

I don't know if this can be done or not. I hope it can because it will make
things at work a lot easier. So if there is a way to do it please let me
know.

 

[Guest]

You don't want a new table. Instead you want a crosstab query. First create a
normal query to gather up all the data that you want to see and save that
query. Next go to Queries on the object bar and select New. Open up the
Crosstab Query Wizard and base it on the query that you just created. It make
take a few tries but eventually you'll get something that looks like you want.

 

[John Nurick]

Hi Hard,

Can you explain more clearly? You seem to say your tables have three
fields Source, Destination and Volume: where do the months come from?

 

[Guest]

John, the months are in different tables. So here is what I got.

Nov_2006
Dec_2006
Jan_2007

A table for each month with pretty much the same data. However, the
destination of the traffic isn't always the same for each month. So for
instance the Nov table has the following

Source Destination Volume
Loc A Loc B 3000
Loc A Loc C 2000
Loc A Loc E 5000

My Dec Table May have something like
Source Destination Volume
Loc A Loc B 2000
Loc A Loc D 5000

My Jan table may have something like
Source Destination Volume
Loc A Loc B 3000
Loc A Loc C 4000
Loc A Loc D 8000

So I want all destination location to be in one table with their
corresponding numbers in different columns. I was looking and here are the
way that I see doing it, but I'm wondering if it can be done any easier.

1. take the Nov table and create a new table
2. Run an unmatched query against Dec and find the destinations that are in
one table and not in the other. Then append that to the new table.
3. Run an unmatched query against Jan and find the destinations that are in
one table and not in the other. Then append that to the new table.
4. Then do a join query where I get all the records in the new table and
the ones that correspond in Dec and Jan table.

This actually does what I want were I take all the records from each of the
tables and combine them into one table with two new fields added.

It's hard to explain, but I hope that I have done a better job.