Cross Join in a Query?

Question

I have data dumps that I am analyzing in Access.  I am creating different tables and am trying to create one joined datasheet that has everything in it.  The problem develops because each row doesn't match up in each table.  Here's a sample of my problem.

Jan Table              Feb Table             Mar Table

ID    Jan Amount    ID    Feb Amount   ID   Mar Amount

1      10                 1    100                1     1000

2      20                 2    200                2     2000

4      40                 3    300                3     3000

If I use inner joins, I get:

JanFebMar Table

ID    Jan Amount   Feb Amount    Mar Amount

1      10                100                 1000

2      20                200                 2000

(Nothing for ID's 3 & 4)

What I want to see is:

JanFebMar Table

ID    Jan Amount   Feb Amount    Mar Amount

1      10                100                 1000

2      20                200                 2000

3      0                  300                 3000

4      40

I think I need a cross join with SQL (an outer join in both directions), but I don't know how to write it.  Can someone help me?  Maybe there is a better way to do this whole thing though.  Thanks in advance for any assistance.

Answer 1

John Vinson answered your qurstion before you asked the question...

oops... <looking around to be sure the Time Police aren't watching> <g>

Answer 2

John Vinson answered your qurstion before you asked the question...

Answer 3

I don't know how to get the data in that format.  It is currently in the format as indicated--separate tables.

Jan Table              Feb Table             Mar Table

ID    Jan Amount    ID    Feb Amount   ID   Mar Amount

1      10                 1    100                1     1000

2      20                 2    200                2     2000

4      40                 3    300                3     3000

How would I get it in your below suggested format in Access?  The data keeps getting dumped from somewhere else in the above format?

ID    Period    Amount

1     Jan         10

1     Feb         100

1     Mar        1000     

2     Feb         20

Answer 4

Well, a better way to store the data would be one tall table with fields for ID, DaleDate and Amount. You can get this with a UNION query:

SELECT ID, #1/1# AS SaleDate, Amount FROM [Jan Table]

UNION ALL

SELECT ID, #2/1# AS SaleDate, Amount FROM [Feb Table]

UNION ALL

SELECT ID, #3/1# AS SaleDate, Amount FROM [Mar Table]

UNION ALL

<etc>

Base an Append query on this to get the normalized data table.

You can then easily create a Crosstab query based on this table, using the SaleDate as the column header, the ID as the Row Header, and First or Sum of Amount.

Answer 5

It may be easier for you to import all the data (as posted) into 1 single Table with the structure like

ID    Period    Amount

1     Jan         10

1     Feb         100

1     Mar        1000     

2     Feb         20

...

This way, the "Period" data is atored as Field values and not embedded in the Field name or Table names.  In all cases, your database needs to avoid storing data in the Table/Field names since database engines are designed to process data in Field values and not in Table names/Field names.

Once you have the data in the above structure, a simple Cross-Tab Query will yield what you asked for.