Combining data from 2 tables

[Guest]

I have 2 linked tables that have the same kind of data. Table 1 is the live
data and Table 2 is archived data. Both have the same field_names. I want to
build a query that selects all appropriate data from each table.

As an example, both tables contain customer_number, invoice_number,
invoice_amount. There may be customer records in tbl1 that are not in tbl2
and vice versa. I want to create a query that will list all of the records in
each table and combine the data where the customer_number is the same.

tbl1 contains:
Cust1 Inv1 Amt
Cust2 Inv1 Amt
Cust3 Inv1 Amt
Cust3 Inv2 Amt

tbl2 contains:
Cust1 Inv2 Amt
Cust2 Inv2 Amt
Cust2 Inv3 Amt
Cust4 Inv1 Amt

The resulting query should display:

Cust1 Inv1 Amt
Cust1 Inv2 Amt
Cust2 Inv1 Amt
Cust2 Inv2 Amt
Cust3 Inv1 Amt
Cust3 Inv2 Amt
Cust4 Inv1 Amt

I know could build a table that combines all of the data from both sources
and then run the query on the new table, but I'm hoping to avoid that step if
it is unnecesarry. I'm new to access and I'm sure I'm missing the obvious,
but if someone could point me in the right direction, I would appreciate it.

Ted

 

[Rick B]

This would be a union query. My question is why you moved data to an
archive table? Just add a field such as "inactive" and flag it for all old
records or just filter by date when you pull the data. It is rare that you
would really want to "archive" records to a different table.

Rick B

 

[Guest]

You are so cool. Worked great.
To answer your question, I am linking to data in another application and the
2 table design is inherent in that application. I don't move the data by
choice, it is the result of the functionality of the source application.

I am using access to generate a single report that is 2 separate reports in
the other application.

Thanks again for the fast and acorrect resply.

 

[Larry Daugherty]

Hi Ted,

The question is, 'Why do you have two tables in the first place"? Unless
you are approaching the 2Gb limit for your data, what's the point? For the
sake of discussion, what does "archived" mean in this context? You're using
data from both tables. It kind of implies that you may have named your data
tables for the year. That's a "No-No". Name the table for the entity whose
attributes are stored in the records. Where the Year/Date makes a
difference, it should be stored in the records in the table. Do it that way
and your problem is solved.

All that being said you might achieve what you want with a UNION query. You
can find it in Access Help.

HTH

 

[Guest]

Hey Larry,
I know, I know....

As I responded to Rick, this design is not by choice. I am pulling data from
another application that is desiged to "archive" data in a historical file
based on a specific criteria. Basically, if a record meets the criteria (paid
& over 180 days old) the record is automatically purged to the archive file.
It's a "pita" because of reporting issues. To get a complete activity for a
customer, the source application requires 2 reports...1 from live data file
and 1 from the archive file.

I am trying to generate a single report that shows everything...and now I
can thanks to Rick pointing me toward the union query.

btw: I just wrote my very first SQL statement and it worked.

Thanks,

Ted