Calculated Controls

[osullipa]

I have a main form which shows customer personal details including their budget and a subform which shows the costs for each course they book. The sub form has 4 boxes which I use to capture course fee, travel fees,subsistance and other expenses (along with other details such as course code etc). These cost details are stored in the table study_leave_recs in the respective 4 fields. I show on the subform the total spend for each course being booked using a calculated control. On the main form I use four dsum statements added together to show the total spend per customer.
=DSum("[COUR_ACT_FEE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_SUBSISTANCE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")++DSum("[COUR_ACT_OTHER_EXPEN]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")

It works ok but is there a better way of doing this? Also is there any way I can use the resultant output to calculate / show the remaining budget (the budget is held in the general table "people"? I'm having trouble using the above statement to subtract the calculated spend from the budget box shown in the main form!!

I would appreciate any help, I seem to have spent ages on this on! Thanks in advance. Regards Peter

 

[Ian Mac]

Hello,

I think you would be far better off re-organising your data.

You would benefit more from a table designed as follows:

CostID - PK
TypeID - Long Int
Amount
Date

You would have more flexiblity later on down the line, i.e. you might need to add another CostType to your table and therefore another Column/Field.

If you transfer your data to the way I have suggested you can add another type to a Lookup Table without any hard work what so ever:

TypeID - PK, Autonumber
CostType - Text

Hope this helps,

 

[osullipa]

Calculated controls.

Hi Ian, thanks for your suggestion. I'm new to access and don't fully understand your suggestion. Did you mean me to modify the course booking table so that I only tracked total cost. If so, that is not an option open to me. The person I am helping, asked for these to be entered on the form separately, but then to show the totals spent on each transaction, the total spent per user in the budget period and if possible the ammount remaining in the customer budget after each booking transaction.

Also I don't understand TypeID - PK / and how you meant the loook up table to work!! Sorry to be a pain. I really would be grateful for an understanding of your explanation.

 

[Mile-O]

Do a search on database normalisation.