How to combine records from multipe tables into one query?

[General Ledger]

Dear All,

Using Access 2007, I am trying to bring together records from three tables: Open Purchase Orders, Inventory, and Open Sales Orders.


I want to determine:

1. Do we have sufficient quantities on order and in inventory to fill each sales order, indicating whether we need to buy more.
2. Do we have more quantities on order and in inventory than we have sales orders for, indicating we are over stocked.

The common fields across the three tables are the item number and project number. An item number might be for one project or multiple projects.

I want to show the current status for each combination of inventory item number and project number, regardless of which tables the combination exits. It is possible to have a combination present on only one of the three tables. For example, we could have one or more sales order for an item/project but no open purchase order and no inventory.


The fields I want as output are:

• Item Number
• Project Number
• Purchase Order Number
• Purchase Order Quantity
• Inventory Purchase Order Number
• Inventort Quantity
• Sales Order Number
• Sales Order Quantity

If I was doing this in Excel, I might start in each table concatenating the item number and project number to create a key field. Then creating a pivot table which consolidates the three tables with the item number/project number as the row item. Add the name of the source data (PO, Inventory, SO) as column items. Add Quantity as the data item.

What kind of joins do I use to relate the tables to one another?

Do I join on both the item number and project number fields?

What kind of query do I create? Will a simple select query do it or do I need to something more elaborate (Union, Crosstab, etc.)?

I am at best a novice in Access so please be detailed in any responses.

Thank you so much for your support.

GL

 

[HiTechCoach]

The good news is that is possible to do with Access.

Now the bad news. Inventory control is one of the most difficult things to program. It does not matter what software or language you use for the development platform.

The key to getting thed working correctly is first to get all the tables properly normalized.

To help get you started I recommend that your read this: Inventory Control: Quantity on Hand