Query Across 3 Tables

[Guest]

I have 3 different tables that contain the Following Columns:

Work Order
Program Name
Work Section
Total Hours
Bid/Actual/Forecast
Month 1 Hours
Month 2 Hours
Month 3 Hours
Month 4 Hours
Month 5 Hours
Month 6 Hours
Month 7 Hours
Month 8 Hours
Month 9 Hours
Month 10 Hours
Month 11 Hours
Month 12 Hours

Table 1 Contains the hours we bid for each program.
Table 2 Contains the hours we actually spent for each program.
Table 3 Contains the hours we are forecasting due to changes within each
program.

What I need is the following:
A Pivot Line Chart that allows me to select between programs and see the
variences month to month between what we bid, actually spent, and what we
forecasted.

I can get a seperate pivot chart for each table, but that is not management
wants.

We would perfer not to combine all three tables into one massive table
without deleting the other tables.

I don't know how to construct my Query.

If anyone could me get to my goal, it would be greatly appreciated.

Thanks,

Jesse

 

[Douglas J Steele]

You need to redesign your tables. You have a repeating group, which won't
allow you to do what you want.

You need two tables:

Table1:
Work Order
Program Name
Work Section
Total Hours

Table2:
Work Order
Bid/Actual/Forecast
MonthNumber
MonthHours

(This assumes that Work Order is the primary key for Table1)

In other words, rather than 12 fields for hours in the 1st table, you have
12 rows for hours in the 2nd table.

You say you've got 3 tables, but you show a Bid/Actual/Forecast indicator in
your table, so I don't understand why you have 3 tables.

 

[Guest]

I understand what you are saying about how to setup my tables, but I need to
keep my actuals, bid data, and forecast data in seperate tables.

Here is why:
Our management will not buy a ERP tool so I am having to pool data from
three different sources, which are Project, Oracle, and our Proposal database.
The reason for the indicator column was for combing all of the data into one
huge table in Excel for a Pivot Table. We now have reached the limits of
Excel, which why I am using Access.

If have 3 different tables in Access, will your approach still work or is
there an easy way to combine the tables together in Access without having to
use Excel?

Thanks,
Jesse