Make-Table Query Help Needed

Blue Hornet

Board Regular
Joined
Aug 7, 2003
Messages
224
This is also posted at OzGrid. If you've read it there, this is the same thing, word for word.

Hi all, and thanks in advance for reading.

I'm developing a cost estimating application in Access and I have (among others) tables for "Proposal", "Personnel" and "Equipment".

To simplify, the "Proposals" table has the basic information on the job, including Proposal Number, Project Name, Start Date and End Date.

The "Personnel" and "Equipment" will link via queries to the "Proposals" key field/s to form "Prop_Personnel" and "Prop_Equipment" tables with the specific people and items on the job, and their individual costs, escalation factors, setup and transportation, etc. We'll have from 0 to X people on each job, each with independent rates, Start and End dates. Same with equipment items: 0 to Y items at various rates and times.

This is all fine for coming up with Total Cost for each person and equipment item, and total cost to the project, but now I want to generate a Cash Flow table, and I'm having some trouble there.

What I want is a query that will build a table with N number of rows, where N is the number of months on the project, from Start Date to End Date, and the first column is a date column of Month / Year. Then I would like to build X + Y individual columns, one for each discrete Person and Equipment item. As I've stated there will be variable numbers of entries, but I haven't said they'll also be varying titles and names (items). I can't just label one column "Manager" and another "Subordinate" because some large projects may have two or more managers, for example. Some equipment will be used on one project but not another; on another project the same equipment may be entered multiple times. I don't want to build huge tables with pre-labeled columns that may or may not be used; I'd prefer to have a make-table query do this on the fly for each specific project.

Once I have all of the columns set, I'd like the table to continue filling in with applicable costs in each monthly bucket. (Even though the data will seldom be used this way, it provides transparency when someone will want to drill down to a particular month and see all of the individual cost components. They always do; it makes my own troubleshooting easier, too, when I want to find out why something's out of whack.)

Finally, I'd like to include separate subtotal columns where I can group total monthly costs by various category, such as "Construction" and "Commissioning", for one category breakdown, "Personnel" vs. "Equipment" subtotals, as well as others that may apply (based on other fields in the Proposal / Personnel / Equipment tables).

My intent in the application is that this table will only be accessed through the make-table query. It's not for general editing, but only a repository for costs as determined by other setup and entry tables. (I'll probably have it deleted after each use to avoid even the possibility of misuse.)

I hope this is a simple enough explanation. It seems like it should be a straightforward task, but I'm just not getting untracked on it.

Thanks again for reading.

Chris
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top