combining queries and calculated fields (1 Viewer)

[FONT=&quot]I have created a db with three tables (please see pdf attachment for details/relationships).

The one table is the vendors’ info, the second is the bills info and the third is the categories of splitting each bill (project/office expense).
I have a query that calculates taxes,total for each bill.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]There are the following cases of splitting a bill to various project codes:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot] 1. No split (only projects): I would like to calculate: AA=total bill or [/FONT][FONT=&quot]BMZ=total (depends[/FONT][FONT=&quot] on choice of project [/FONT][FONT=&quot]code)
[/FONT]
[FONT=&quot] 2. Split to office: I would like to calculate: AA=60% total bill and BMZ=40% total bill

3. Split between [/FONT][FONT=&quot][FONT=&quot][FONT=&quot]projects:[/FONT][/FONT] I would like to be able to type the amount [/FONT][FONT=&quot]for each field

4. Split between project for project [/FONT][FONT=&quot][FONT=&quot] & office[/FONT]:
- for project I would like to be able to input [/FONT][FONT=&quot][FONT=&quot]the amount
[/FONT]- for office: calculate:
i) AA= 60% of (Total bill-Project amount)
ii) BMZ=40% of (Total bill-Project amount)

I know how to do case 1 & 2. I am confused with cases 3 & 4. How to proceed when there are multiple splits and not just one?

please let me know if any more clarifications are needed.
thank you in advance.[/FONT]

VendID does not belong in tblExpCategory. BillID is the FK that links to the correct record. Beyond that I don't have enough information to help. Where are you doing this split? Do you intend to do it record by record or after everyghing is entered for a bill?

thank you for your answer.
You are correct about VendId, I will remove it.
For your question: I use a subform of tblExpCategory where I fill in the split info for each bill (see attached files).

What would be the best way to do it?
(p.s. I am a newbie, so please have patience...)

thank you!

I can't tell what AA and BMZ are but they appear to be a repeating group and First Normal form expressly prohibits repeating groups. I think you have database design issues that What is the possibility of finding out that a third option will be needed? Seems like that would make a mess of everything. Should you properly normalize the schema now so adding new categories will never cause a problem?

AA/BMZ are the two different budgets we are using for our projects. We have specific funds for each budget.
The rule is that when the bill is "office expense" we split the bill 60% to AA budget and 40% to BMZ budget (that's the most usual case).
The only real case that both categories (office expenses & project) are combined and more than one split is true, regards courier bills: one courier bill can include unlimited shipments that belongs to various projects and office expenses. And I will have such a bill once a month.
I will check my options again regarding the design of my database and get back to you. If you have any other suggestion I 'll by happy to listen to.
thanks!

Please do some reading on normalization. Accounting practices change and you will have no control over that. One day, you'll come into the office and be told that there are now three buckets that costs have to be allocated to and this app will fall apart.

To make this type of process work with the necessary flexibility, you'll need a table that defines budgets, a table that defines expenses, and a table that Allocates Expenses as a percent to each budget. This third table is frequently called a junction table. The process to build this junction table is a little tricky since you will need to ensure that the AllocationPct for all the rows in the Expense Type will need to add up to 100%

Once the junction table is built, that is what will be used to create the necessary accounting entries for each budget. The allocation will be done entirely with an append query. No code is required except to run the allocation. But more importantly, no code is required to change the allocation for an expense. You just change the junction table. No code is required to add a new expense. You just add the expense and create the necessary entries in the allocation table.