Creating Summary Reports from Two Tables

[PrinceAli]

I am trying to streamline cost reporting for my construction projects
and I would like some help here. I have two tables:

1) Base Cost: cost that are budgeted for each cost code.

Setup like this: Cost Code / Cost Description / Budgeted Cost
(Total) / Labor / Material / Subcontractor / Equipment
An example: 33 33 16 Combined Utility Sewerage $872,757.00
$199,500.00 $553,257.00 $120,000.00

2) Actual Cost: Cost Code / Type (Labor or Material or Sub or
Equipment) / Date / Source / Amount

I would like to create a report that would using the actual cost table
tally up all the costs grouped by cost code and type and then compare
the total versus what I have in the base cost table and show how much
money I have left over in the budget. Actual cost table will be
updated on a monthly basis with new cost data while the base cost
table will remain unchanged and will be used to compare costs.

Appreciate the help. Cheers

 

[Access Developer]

Do you maintain a separate database for each project, or do you maintain a
project identification field (which is not shown) in each record of the Base
Cost and Actual Cost tables?

You appear to describe seven Fields in the Base Cost Table, but the example
you show appears to contain only six.

"Un-normalized Design Issues"

1. You identify costs in the Base Cost table by the Field's Name,
but apparently by a Type Code in Actual Cost.

2. You also identify both specific costs _and_ the total of those
costs in the Base Cost record.

This would be a commonly-used design in a spreadsheet, but is not
"normalized" for a database. It will be simpler to include a record
for each type of cost in the Base Cost table, and no total record,
but to calculate the total in a query when you need it.

You describe updating the Actual Cost table monthly, but there's neither a
field for identifying the month nor a date field shown as included in the
Record. Do you replace the entire record, so that it represents "Actual
Costs To-Date"?

From what you describe, it seems that you maintain only summary totals in
your tables... I would expect that you might find it useful to record each
item of actual expense, and summarize them in a query.

If you could clarify for us, it will be easier for someone to offer specific
suggestions. By the way, what version of Access are you using?

 

[PrinceAli]

Hi thank you for your response. I am using Access 2012 version.

This is one database for each project and when I created this database
I used the option to link the database with an excel spreadsheet. I
would like to keep updating the spreadsheet in excel so I can have
access automatically sort the database and update the cost data.

Maybe this will clarify the example:

Cost Code: 33 33 16
Cost Description: Combined Utility Sewerage
Budgeted Cost (Total): $872,757.00
Labor: $199,500.00
Material: $553,257.00
Subcontractor: $0
Equipment: $120,000

In respone to the design issues:

1. In the actual cost table, the type would be labor, material,
subcontractor, equipment.

2. Yes this is true because of the way I have this in the excel sheet.

Actual cost table does have a date of when this cost was paid or
entered in the system. The more recent that data, the more realistic
the money left in the budget would be.

The actual cost spreadsheet is generated from quickbooks from accounts
payable. Since I do not want to re-enter that data in access, I had it
setup to pull directly from it so I can replace the sheet with a newer
one periodically.

If this is not an ideal way of doing it, how should I setup the DB to
properly handle the information? I will set it up per your suggestions
and then if you can help I can properly query and summarize the
information.

In the end, I would like to compare the actual cost vs the budgeted
cost in a report that I can give to the owners so we can know if we
are making money or loosing money.

Thanks again