Budgets vs actuals job costing database

[handlebars]

If i want to compare actual costs against budgeted costs in an access database how can i evaluate both at once? Do I have to create a special kind of relationship between my budgeted costs and actual costs to evaluate??

How can i explain my relationships non-visually - is there anywhere i can post an image of this?

Andrew

 

[psemianonymous]

Three tables.

[THING YOU ARE COUNTING - let us call it "Object"]

[Costs]

[Budgeted]




So join Object to Costs and Object to Budgeted

[Costs] <- [Object] -> [Budgeted]


Make them OUTER JOINs which require the Object but DO NOT require the cost/budget item.



Create one query which pulls ALL data from ALL three tables, linked as shown above.



Use that query for a data source for further queries to save yourself some hassle, unless you are dealing with a huge (huge!) amount of data.



If you have multiple cost items, you can change the above to a Totals query and sum all the Costs (and budgets?). This will make the query uneditable/not updatable/you get the idea.

You should now see your data like so:

&quot;Ice Cream Project&quot; 50.00 (Null)
&quot;Running Out Of Ideas&quot; (Null) (Null)
&quot;Totally Stumped&quot; 500,000,000 400,000,001.50



--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[handlebars]

Maybe i should have explained, in the first instance i have the tenders in one table, when it becomes a job i have created a simple function to take this info into a new job form, where all the actual timesheets and expenses will be input. A problem with this is that i need to query my tender figures and final figure separately (one by quoteID the other by jobID). It does not seem pretty although it fulfils the main criteria. What i was wondering was if i could update all the tender file with the newly assigned job number so that a query can be produced to display in another way?