How can I use queries to combine cost data from multiple tables? 1

[Johnnycat1]

This is probably the most basic question but I am stumped.

I have a table for cost codes, a table for purchase orders, a table for freight costs, a table for budgets and a table for time sheets.

Each of the tables has a Project Name and a Cost Area and Cost Code. I want to create one query that groups all of the cost areas and cost codes from all of the different sources. They may be unique to each table but I want one master list of all Cost Areas and Cost Codes used on the project.

The Cost Area is a general category and the Cost Codes are details that may or may not fall under each Cost Area.

ie:
TblPurchaseOrders may have the following:
CostArea CostCode
Area1 001
Area2 001
Area1 002

TblFreight may have the following:
CostArea CostCode
Area1 002
Area2 003

The resulting query would produce:
CostArea CostCode
Area1 001
Area1 002
Area2 001
Area2 003

The other tables would be similar. They may repeat the same areas and codes but they may have new areas and codes as well.

As always any help would be very appreciated!

nanos gigantum humeris insidentes

A dwarf on the shoulders of giants...

 

[dhookom]

You can use a union query to create a combined recordset. You can start in design view with one table but must use SQL view to add more select statements.

SELECT "Purchase Orders" as CostType, CostArea, CostCode
FROM tblPurchaseOreders
UNION ALL
SELECT "Freight",  CostArea, CostCode
FROM tblFreight

Duane
Hook'D on Access
MS Access MVP