Year vs. Year Comparison

[Guest]

I have two tables, one with sales date for 2005 and one with sales data for
2004. Both tables have the exact same format, but may vary as to the
particular customers that we sold to (ie - some customers were only 2004, and
some were both years).

I have tried to create a query that produces all of the same general
information (ie - customer, item, etc) and then pulls the 2005 sales data in
one column and the 2004 sales date in the next column. However, the result
is not accurate.

Any advice on how to create this type of querry?

 

[Chaim]

You might want to post a summary of your table structure. Usually, you would
not set this up as two separate tables. Typically, you would have one table
and some views (query defs) that select the records by year, giving the
'impression' of having two tables.

As a general rule, if two or more tables have the same fields, they are
describing the same thing. The data differentiates things, not the table the
data is in. Rather than being an advantage, it is a severe drawback and
complicating factor.

 

[John Vinson]

I have two tables, one with sales date for 2005 and one with sales data for
2004. Both tables have the exact same format, but may vary as to the
particular customers that we sold to (ie - some customers were only 2004, and
some were both years).

Well... you're paying the price for what I'd consider an unwise design
decision. Storing data in a tablename (as you are doing) is bound to
cause this type of trouble!

Unless you have millions of records every year (and if you do, you
should be considering SQL/Server or another true client/server
database system), you may well be better off using a single table with
a date field, or perhaps a fiscal-year field; you can use a Query to
display just the data for 2004 or 2005 as needed, and base your forms
and reports on that query.

I have tried to create a query that produces all of the same general
information (ie - customer, item, etc) and then pulls the 2005 sales data in
one column and the 2004 sales date in the next column. However, the result
is not accurate.

Any advice on how to create this type of querry?

It sounds like you want a "Full Outer Join". Access supports Left and
Right outer joins (all the records in 2004 and matching records in
2005, or vice versa) but not a Full join. To get the result you want
you'll need to base a Query on two other Queries.

First, create a query joining the 2004 data to the 2005 data by
CustomerID; select the Join line, and choose option 2 (or maybe 3) -
"Show all data in [2004] and matching data in 2005". This is a "Left
Outer Join" query. Save it as qry2004.

Now do the same, except select the other option on the join line, to
show all 2005 customers who were not included in 2004. Save it as
qry2005.

Finally, open a new query in SQL view and type (you can't do this in
the grid):

SELECT * FROM qry2004
UNION
SELECT * FROM qry2005;

to show all records.

John W. Vinson[MVP]