Make Table Query

    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

    Re: Make Table Query


    First let me say that what you are attempting does not seem simple, so don't feel bad for being stuck. You may be better served to write some code using ADO to build this recordset if you know VBA. Since you specifically requested a query we will explore that direction. Without a lot of study, considering your brief descriptions, and finally not knowing the exact dimensionality of your tables concerning dates, I am going to make some assumptions to get us started.


    First your dates for your buckets must be contained somewhere in order to separate them into this timeline, and since you stated your proposal table only contains a start and end date I will assume the other pertinent dates are contained in the personnel and equipment tables where you are going to book those resources. If that is the case, then this seems to be a chain of seven individual queries to yield the final result you seek.


    Query 1 - Step 1:


    This query would link your proposal table to the personnel table and show a date column from the personnel table, the subtotal descriptor for each personnel resource, the personnel resource, and your cost.


    Query 2 - Step 2:


    This query would link your proposal table to the equipment table and show a date column from the equipment table, the subtotal descriptor for each equipment resource, the equipment resource, and your cost.


    Query 3 - Step 3:


    This query would UNION steps 1 and 2.


    Query 4 - Step 4:


    This query would aggregate step 3 by grouping the date buckets, grouping an alias column for a place holder with "Total" as a value, grouping the SubTotal Descriptors, and Sum the costs.


    Query 5 - Step 5:


    This query would union the detail query step 3 with the aggregate subtotals of step 4.


    Query 6 - Step 6:


    This query would use query step 5 and be a crosstab query where you would select the date field as a row, the resource field as a column, and the cost as a value.


    Query 7 - Step 7:


    This would be the make table query that makes a table by dragging the asterisk from query 6 into the design grid.


    You will probably need to devise a sorting key for your columns since I doubt you will appreciate an alpha-numeric sort on your column headings. This could be a separate table linked to query step 5 and used in the construction of the crosstab query step 6.


    It will be interesting to see if anyone has an easier way to approach this!


    Hope this helps,

    :guitar: - Scott

    Re: Make Table Query


    Chris and Scott,


    As I was reading the brief I realised this is similar to what I do most of the time- cost models for a very large number of items. Scotts solution contains the essentials of what I do for the life cycle models that I produce.


    Step 1 calculate al costs for each resource in anyway that it is useful/done normally. For example human resource and equipment resource may be calculated differently.


    Step 2 create a single table of all costs with columns for Cost, Cost Source/ID- in his case Human Resource or Equipment or any other cost source.


    Step 3 create a cross tab query on the single table.


    Step 4 report the data in many different formats.


    The advantage of this is that you can easily add different cost sources to the data. Say there was a one off fee for licensing to use a particular peice of software, add it in to your costs table (step 2) and it will appear in your cross tab.


    I guess what I am sayin is Scott has got it right by my judgement! :)


    Good luck,


    Alan.

    Re: Make Table Query


    Scott and Alan,


    Thanks both for your input, and I understand most of what you're saying here. (Though I confess I have only a vague notion of what you meant exactly by Query 4, Scott; maybe the answer is there but I'm too dense to see it. It seems like by aggregating and grouping date buckets, I'm getting a single line for "total cost".) Unless there's something I'm missing, I still don't see how this can deliver month-by-month cash flow information.


    I guess the easiest way to state this is that I'm trying to do something in Access that would be as simple as breathing in Excel: Write a table that shows each month of the project (and no more), then show total projected cost for the month by listing and adding each expected element of cost for that month, each in its own column. I just don't want to do it manually. I could do it fairly 'simply' with formulas in Excel, but then it bogs the spreadsheet.


    Chris

    Re: Make Table Query


    Query 4 does what your formulas would do for the subtotals like construction etc. Actually if you want a grand total by month it would require one more query similar to number 4 where you remove the grouping on your subtotal descriptor and have another alias there as a place holder for the UNION query.


    If this is purely for visual effect, and you are comfortable with Excel Objects and VBA why not do this on a form. You could use an actual VBA form in Access, not an Access form. You could set up the references to Excel you need. Then you could make a nice looking form where the meat of it would be an embedded Excel grid object. You could construct a query that consolidates all the information you will need to build the grid. Then spin through that query as an ADO recordset and populate the Excel grid on the form using the Range Objects.


    Just a thought,

    :guitar: - Scott

    Re: Make Table Query


    Chris,


    Do you want the data or just a report of the totals? If you actually only need a report then you can simply use the Reporting within access and just use Group Headers and Footers for totals.


    If you need the data then you write queries using the summary functions. (Right click on the grid in the query design to reveal these).


    Both of these solutions require that for each cost item (person or equipment) you can generate the same data, that is:
    Item as the actual person or equipment ID
    Date as Month and Year
    Cost Group as, say, Personnel, Equipment etc
    Cost as $ (or £, but I think you're in Windsor, Connetitcut (sp?) not Windsor, Berkshire :))


    From this one table you have a database of costs, so you can sum for any of the parameters etc. The advantage of this over Excel is that it can be as large as you like and you will still need the same code.


    If you would like I can give you a more detailed description of how I do this, but it is Friday night and I am about to log off! I will be back on line on MOnday, or possibly tomorrow morning.


    A.

    Re: Make Table Query


    Once again, thanks to both of you, I think I have finally managed to take what you were saying and combine it with something I figured out on my own, and I am approaching an answer now.


    What I did was to manually create a 'calendar' table, filled with one column of dates. These are 'potential project dates', and they range from Now to six years from now. Eventually I'll have to modify or add to that as time goes by, or if we get exceptionally long projects, but this works for now. So the single-column table has "Project Month" values that run from 1-Nov-2004, 1-Dec-04, 1-Jan-05 ... 1-Dec-2010.


    Now I can combine that table with my tables for Personnel and Equipment and examine whether "Project Month" >= StartMonth (for a person or thing) AND "Project Month" <= EndMonth (for the person or thing) and enter the appropriate cost for the month if the condition is met. (It also checks to see if Project Month = StartMonth, for example, then is there a Transportation or Setup adder, etc., but you get the idea.)


    This will give me a table of 72 (months) times the number of people or equipment items I may have. So, potentially a huge table, but I'm confident that Access can create it and calculate it with more facility than Excel (and I don't need to keep it forever, anyway). From this table I should be able to make my reports and exports to suit the other data requirements that are sure to crop up.


    But I've got this far, and it's working more or less as I had planned. My problems were that I was
    1) trying to automate the 'calendar table' capability, and that just wasn't needed, and
    2) I thought I'd have to grow the number of columns to suit the number of 'person' and 'item' adders. I don't; I just grow the rows instead, with another set of 72 rows for every discretely added person or thing. No big deal. It's actually pretty simple, once I got the idea how to proceed.


    I'm going to bookmark this thread, because I'm sure to come back to it when I get into more of my reporting, summarizing, etc. ... but that's for tomorrow.


    I want to thank you both again for your help.


    Chris

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login