Hello,

I’m having trouble with a database design.

I have a number of different expenditure types (Labour, Plant, Materials). I have split these into different tables e.g. Labour: LabourID, Description, GradeID, Rate. Materials: MaterialID, Description, UnitCost, UOM. Etc.

I then want to have an Expenditure table where I store instances of these expenditure types against tasks, e.g. Task, ExpenditureTypeID (from labour/materials/plant).

The problem I have is that I have split the expenditures up into different tables and I then want to store the instances in one table. Is this possible?

Thanks in advance.

I wouldn't mind betting that each expenditure type could be stored thus

Exp_ID
Exp_Name
Exp_Cost/p unit
Unit Name

Exp_Cost/p unit is otherwise known as rate, Hourly Rate etc.
So Labour might be

AUTONUMB (Say: 00435)
"Labour"
6.50
"Hour"

Then when you have an expenditure it simple is
Cost_ID
Cost_Decription
Cost_Qnty
FK_Exp_ID

so a five hour job might be
AUTONUMBER (Say: 09578)
"Fixing the sink"
5
*00435

The total would never be stored but could be calculated at run time

Total = [Cost_Qnty] * [Exp_Cost/p unit]

You can now use data to define as many different expenditures as you like. If you want you can allow users to add more of their own.