Need to sum costs from table by department & calc. balance from budget 1

[Sherman6789]

We have eight departments. Each has a printing and product budget for the year. The database tracks the various products and printing and keeps data about them. Reports are also made as needed. Each department has hundreds of records added on a daily basis by staff members.

We would like to allow the managers of each department to be able to enter their budget amount into the system somewhere. When reports are made, the printing costs and other costs are added for each department and the amount will be subtracted from the budgeted amount to determine the balance available and for other various statistics and reports.

The budget amount can be changed during the year as additional money is made available to the departments.

Should I use "varibles" or create a one record table with totals and their IDs stored in fields?

If so, how do I write the code to access the data from the various records by department and where?

The budget amounts should be availabe for reports, on-the-fly calculations, forms, etc., if possible.

Your assistance is highly needed.

Thanks!

Sherman

 

[dhookom]

This all depends on how your budgets are set up and how you want to compare to printing and products. If there is only one budget figure per year per department then use a table structure like:
[tt][blue]
tblDeptAnnualBudget
================================
dabDABID autonumber primary key
dabDeptID link to department primary key
dabYear
dabBudgetAmt
[/blue][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]