Adding subtypes and comparing value to main type

[GAccess]

New to this portion of access , never really had to code/program behind the scenes for calculations. Primarily did user face calculations in access and excel and as well as reports /queries in access.

I have Three Tables

Table A
Department "Name"
Department "Budget-Value"



Table B
Section "Name"
Section "Budget - Value"

-section represents what the department is paying for : such as Electricity, Misc., Extracurriculars, Entertainment, Supplies, Research etc. Each section has a budget.

Table C
Invoice "Number"
Invoice "Amount"
Invoice "Paid"

- Invoice represents the receipt given for spending - the amount requested sometimes differs from the amount paid

I wanted to know if SQL or VBA can help with my dilemma. I want to alert the user when spending has hit a limit so that they can slow down on/stop spending.

So the invoices paid cannot exceed its respective section, and the section budgets entered cannot exceed the entire department's budget.

[pbaldy]

It shouldn't be difficult, but wouldn't you need a field(s) in Table C to identify the section/department?

[GAccess]

Yes, apologies .. I do have that in table c@ pbaldy.

I honestly dont know how to approach this.

Would I need Dlookups? please help!

[pbaldy]

I don't think so. Your problem isn't much different than this:

https://www.accessforums.net/showthr...-Hand-Quantity

I'd have a query to sum up the transactions by section, and then a query that joins the section table to that query so it can compare the budget to what's been spent.

[GAccess]

I was looking to get real time calculations to stop the user from entering any more financial expenditures. The queries are not too hard to do.. but let me go ahead and check your link first.

[pbaldy]

"real time" is simply a matter of checking the query at the appropriate times. From the sound of it, in the before update event of the form so you can stop the entry if you want.

http://www.baldyweb.com/BeforeUpdate.htm

Except you'd be checking your query.

[GAccess]

Thanks pbaldy for being willing to help, but the link is not quite what the goal is.

I would like the user to know there is a problem when budget values for each respective group are being exceeded.

Sum InvoicesA = Sum Section BudgetA = Sum DepartmentA
Sum InvoicesA = Sum Section BudgetB = Sum DepartmentA
Sum InvoicesA = Sum Section BudgetC = Sum DepartmentA

Sum InvoicesB = Sum Section BudgetA = Sum DepartmentA
Sum InvoicesC = Sum Section BudgetA = Sum DepartmentA

and so on

[pbaldy]

It would help if you posted a sample of your data, with the desired result.

[GAccess]

Department	Section	Section Budget	Invoice #	Invoice Amount	Paid on Invoice
Customer Service	4122	3000	4AR-TY	2000	2000
Customer Service	4122	3000	41EPO	1000	900
IT	33333	4000	98234-R	3000	3000
Lab	33333	4000	4324	3000	2500
Tax	33333	4000	R2342	4000	4000
Legal	333457	6000	J2352	5000	5000
Legal	987456	3000	QP-0909	1000	500
Engineering	287333	5000	404054	3000	3000

[GAccess]

let's say Customer Service has a Budget of 7000.
Section code 4122 has a budget of 3000.

When customer service receives an invoice "4AR-TY" for the amount of 2000, they pay 2000 on the invoice.

So now customer service has 1,000 left in their budget for section 4122.

Customer service goes out buys again. They receive an invoice " 41EPO" in the amount of 1000, they only had to pay 900.

SO now customer service will have $100 left on Section code 4122 to spend.. If they try to enter an invoice for more than 100, I want the system to lock them out from doing so.

Though different departments share the same section code... the section codes are used per department, exclusively.

I just want this calculation stuff somewhere in the background as a check and balance. Do you still think you can help?

[pbaldy]

I was hoping for an actual database to play with. That doesn't look like the structure you described earlier. Since I don't know the structure, I'll simply say that you can add up what's been spent with a totals query or DSum() and compare that to what was budgeted.