Query pulling data from multiple tables

[jetman5843]

Hi All:



I'm new to this forum and am hoping I can get some help on this issue.

I am making a query on customer orders. We use header and detail tables to store order data in.

I have 4 tables I need to query on. We have current open orders tables (header table and detail table) and completed orders tables (header and detail table). The key field in each of the 4 tables are order number. Each set of tables are joined by this field.

When an order is placed, the data is stored in the current order header table for certain information such as customer number, address, date entered, total order value, etc... The line item detail of the order such as the material, description, item cost , etc. is stored in the current order detail table.

As the order is built and items from orders are shipped, the line items are deleted from the current open order Line item table and stored in the order line item history table. when the order is totally complete, the header table entry is deleted and stored in the header history table.

My issue is being able to query both sets of tables for data on orders based on date range selection criteria.

Does anyone have a way to be able to do this?

Regards.
Kevin

[June7]

The issue is using 'history' tables. I would not do this. I would have a field in detail table for ship date and use that field to determine if order is complete. Instead of 'moving' records I would apply filter criteria.

Otherwise, use a UNION query to recombine the records to one dataset and search that query.

[jetman5843]

Thanks for the reply June7:

Unfortunately, the database i'm using is made up of tables that are linked into our Access database from our ERP system and that is how the system stores order data.

Can you point me in the rgiht direction for a little information on union queries? Can I use that for the situation i described above?

Regards,
Kevin

[June7]

There is no wizard or builder for UNION. Must type or copy/paste into SQL view of query designer. There is a limit of 50 SELECT lines. The syntax is like:

SELECT field1, field2, field3 FROM table1
UNION SELECT field1, field2, field3 FROM table2;

Search web on topic, lots of info. Here is one http://www.w3schools.com/sql/sql_union.asp