Form: 2 Tables Reading sum of field in second table (1 Viewer)

undertm

Registered User.
Local time
Tomorrow, 02:22
Joined
Jul 28, 2011
Messages
14
Hi All


I have two tables 1. tblBudgets and 2 tblMaterial_Purchase_Request and one form "frmBudgets"



The tblbudgets is a single record table which contains the 2019 budgets. example

ID
Fin_Year
Insurance
Stationary


When the form opens using the about table, i put the budgets in the single record table. example

Fin Year 2019
Insurance 10,000

Stationary 2,000


The second table "tblMaterial_Purchase_Request" is a Purchase order transactions table, there are lots of transaction purchase order records in there.


ID

Transaction_Type Text

Qty number

Item_Cost Currency

PO_Total_Amount Currency


Form (frmBudgets) see attached.



Fin Year 2019
Insurance 10,000 [total of tblMaterial_Purchase_Request = Insurance] ??

Stationary 2,000 [total of tblMaterial_Purchase_Request = Stationery] ??



I am struggling to get the frmbudgets to create a text box that will show the "total of tblMaterial_Purchase_Request = Insurance"



tried sql query, sum, dsum its just not working.


Anyone out there that can help would appreciate it.


Terry
Thanking you in advance.
 

Attachments

  • Image 23-3-19 at 3.26 pm.jpeg
    Image 23-3-19 at 3.26 pm.jpeg
    52.7 KB · Views: 52

June7

AWF VIP
Local time
Today, 07:22
Joined
Mar 9, 2014
Messages
5,425
Not understanding what you want.

Do you mean each expenditure type has its own field? This is not a normalized data structure. If you want to provide db for analysis, follow instructions at bottom of my post.
 

undertm

Registered User.
Local time
Tomorrow, 02:22
Joined
Jul 28, 2011
Messages
14
How do i attach an example?
 

plog

Banishment Pending
Local time
Today, 10:22
Joined
May 11, 2011
Messages
11,613
The tblbudgets is a single record table which contains the 2019 budgets. example

ID
Fin_Year
Insurance
Stationary

I don't think you table(s) are set up properly. Does the above mean tblBudgets has a field called something like [Insurance] and a field called [Stationary], etc.? If so, that is not how you should set it up.

You don't store values in field names and all of those fields are named after budget categories. Instead, you should have a budget category field into which you put values like "Stationary", "Insurance", etc.:

tblBudgets
ID, autonumber primary key, will correspond to existing ID field
Fin_Year, number, will correspond to existing Fin_Year field
BudgetCategory, text, what is now in each fields name will go in here
BudgetAmount, currency, this will hold what is now in each of those category field names.

That's it. That table with just 4 fields will hold all your budget amounts (and it easily expands to accomodate new ones in the future--just add a new record). That's how your budget table should be set up.
 

Users who are viewing this thread

Top Bottom