Solved Table Fields calculation (1 Viewer)

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
Hi everybody.
I have a primary table as source of a data entry form. Textboxes of the form are getting their values from the second column of different child tables. All child tables are related to primary table by their primary key's. I need to sum the values in third column of child tables (corresponding to the values in textboxes) and then result goes into the last column of primary table.
I use VBA and database connection to save all the values in primary table from my Form. I want to create a function that calculates the summation and saves it in primary table with OnClick event of the existing save button.
Please let me know what is the best approach?
Please let me know if given information is not enough or I am not clearly explain it.
Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Storing "calculated" values are not recommended if you follow the Normalization Rules. Can you tell us the reason why you're trying to do this? Just curious...
 

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
Third column of each child table is called "riskValue". When user selects a value from second column using a combobox in my Form, I want to have the third value in the same row to be used in my summation. This way, when all comboBoxes in the Form have selected values to show the user's selection in the Form I can calculate all third columns (all called riskValue in different tables as third column). In other word a comboBox can only return a single value while I need the third one as well to calculate overall riskValue in my Form.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:42
Joined
Oct 29, 2018
Messages
21,358
Third column of each child table is called "riskValue". When user selects a value from second column using a combobox in my Form, I want to have the third value in the same row to be used in my summation. This way, when all comboBoxes in the Form have selected values to show the user's selection in the Form I can calculate all third columns (all called riskValue in different tables as third column). In other word a comboBox can only return a single value while I need the third one as well to calculate overall riskValue in my Form.
Hi. I'm sure what you just said makes perfect sense to you, but since I am not familiar with your database, it just flew pass over my head. If you're saying you also want to have the third column available after the user selects an item from your combobox, then you can use the Column() property of your combobox, assuming you included the third column in its Row Source. Hope that helps...
 

June7

AWF VIP
Local time
Today, 09:42
Joined
Mar 9, 2014
Messages
5,423
You want to add values from multiple tables and save that result to parent table? How many 'child' tables? Why are there multiple 'child' tables?

If you want to provide db for analysis, follow instructions at bottom of my post.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 28, 2001
Messages
26,999
A combo box certainly CAN return more than one value. Remembering that combo box columns are zero-based, you can ask for Column(2) of that combo box with combobox.Column(2, combobox.ListIndex) and thus pick up your values. The article on .Column also tells you how to know that no selection has yet been made.



Having said that, I suspect a design error here in that if you have that many tables that can produce independent risk values, I wonder at least a little bit as to whether you have created too many tables for the job you actually need. Tell us more about those risk tables because that is where I think you have an issue.
 

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
Hi. I'm sure what you just said makes perfect sense to you, but since I am not familiar with your database, it just flew pass over my head. If you're saying you also want to have the third column available after the user selects an item from your combobox, then you can use the Column() property of your combobox, assuming you included the third column in its Row Source. Hope that helps...
I need to have the third and second column available at the same time. The second value will be saved in parent table and the total of all riskNum's goes to the last column of parent table. Total_riskValue in my tbl_all_logs should have the summation of all riskValues from each child table based on user selection. I've not established my relations yet. This image is prepared only for presentation.
Let me give you an example:
riskNum for fourth option in tble_social_distance is 4
riskNum of tbl_infected_ppl is 2.5 if user selects sixth option of its combobox
riskNum of tbl_who is 10 if user click on the first option in combobox.
....
At this point I save id=4 in [tbl_all_Logs].social_distance_id,
id=6 in [tbl_all_Logs].infected_ppl_id,
id= 1 in [tbl_all_Logs].w_who_id,
...
And finally in [tbl_all_Logs].total_riskValue I save total of 4+2.5+10 = 16.5 when I click on the save button.

Is this clear?
tables.JPG
 

plog

Banishment Pending
Local time
Today, 12:42
Joined
May 11, 2011
Messages
11,611
You have not set up your tables properly. The big issue you have is storing values in table names. Every table that has a [risknum] field needs to be consolidated into just one table. I suggest tblRisks and it be structured like so:

tblRisks
risk_ID, autonumber, primary key
rusk_num, number, this is will hold what's curretnly in all thos [risknum] fields
risk_type, text, this will hold what you are currently storing in each table name (commute, who, next, etc.)
risk_Desc, text, this will hold what ever is in each of the second fields of every table (commute, infected_people, etc.)

That's it, 4 fields. Then you will need another table so that you can discern what Risk goes to what Log record. It would contain just 3 fields:

tblRiskLogs
rl_ID, autonumber, primary key
Log_ID, number, foreign key to tbl_all_Logs
Risk_ID, number, foreign key to tblRisks

So, instead of purpose_id, mandatory_id, w_who_id, etc being in tbl_All_Logs you will add 1 record to tblRiskLogs so that you know what risk goes to each log. Then, when all that is done, when you want to SUM a risk value you run a query.

Lastly, all those [friend_] fields need to go into another table as well. Instead of 4 fields in tbl_all_Logs you would have a new table with 1 record per friend and use the Log_ID as a foriegn key so you know what record all the friends go to.
 

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
You have not set up your tables properly. The big issue you have is storing values in table names. Every table that has a [risknum] field needs to be consolidated into just one table. I suggest tblRisks and it be structured like so:

tblRisks
risk_ID, autonumber, primary key
rusk_num, number, this is will hold what's curretnly in all thos [risknum] fields
risk_type, text, this will hold what you are currently storing in each table name (commute, who, next, etc.)
risk_Desc, text, this will hold what ever is in each of the second fields of every table (commute, infected_people, etc.)

That's it, 4 fields. Then you will need another table so that you can discern what Risk goes to what Log record. It would contain just 3 fields:

tblRiskLogs
rl_ID, autonumber, primary key
Log_ID, number, foreign key to tbl_all_Logs
Risk_ID, number, foreign key to tblRisks

So, instead of purpose_id, mandatory_id, w_who_id, etc being in tbl_All_Logs you will add 1 record to tblRiskLogs so that you know what risk goes to each log. Then, when all that is done, when you want to SUM a risk value you run a query.

Lastly, all those [friend_] fields need to go into another table as well. Instead of 4 fields in tbl_all_Logs you would have a new table with 1 record per friend and use the Log_ID as a foriegn key so you know what record all the friends go to.
@ plog, thanks for your time.
I am not sure if I've understood what you are suggesting here.
I understand the last part about friends fields and I will fix that according to your comment. But I don't follow what you say about two new tables and the way they are related to tbl_all_Logs or each other. Please help me to understand.
Are we going to use cascaded comboboxes to retrieve the right value of risk_num? How it works?
 

plog

Banishment Pending
Local time
Today, 12:42
Joined
May 11, 2011
Messages
11,611
x.PNG


It's a simple many-to-many relationship (https://en.wikipedia.org/wiki/Many-to-many_(data_model)) between tbl_all_Logs and tblRisks. Those type of relationships require a junction tabl (tblRiskLogs) to sort out which risks go to which Logs records.

Formwise it will be implemented with a subform with possibly cascading combo boxes. I'd have to see the data currently in all the other tables that will be going into tblRisks to help recommend a specific input system.
 

Cronk

Registered User.
Local time
Tomorrow, 04:42
Joined
Jul 4, 2013
Messages
2,770
I agree with plog in the main.

I can see there might be a number of levels of the one risk type in which I'd include a risk_TypeID for grouping purposes rather than sorting on risk_type

It looks like there's a need for another table based on Friends although it could be included in tblRisks
 

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
View attachment 80670

It's a simple many-to-many relationship (https://en.wikipedia.org/wiki/Many-to-many_(data_model)) between tbl_all_Logs and tblRisks. Those type of relationships require a junction tabl (tblRiskLogs) to sort out which risks go to which Logs records.

Formwise it will be implemented with a subform with possibly cascading combo boxes. I'd have to see the data currently in all the other tables that will be going into tblRisks to help recommend a specific input system.
I still think I haven't clarify myself very well. What happens to all other tables? You want me to integrate all items in one tblRisks and eliminate all those individual tables? Please look at all items in my tables here:
doc1.JPG
 

June7

AWF VIP
Local time
Today, 09:42
Joined
Mar 9, 2014
Messages
5,423
I agree with Plog's schema.

Yes, tables would be eliminated.
 

plog

Banishment Pending
Local time
Today, 12:42
Joined
May 11, 2011
Messages
11,611
Yes
Yes the data of those 6 tables all go into tblRisks. It would look like so:

tblRisks
risk_ID, risk_type, risk_Desc, risk_num
1, Infected, Yes, 10
2, Infected, No, 1
3, Infected, Not Sure, 5
4, Who, Myself, 1
5, Who, Friends, 3
...
25, Why, Work, 5
26, Why, Shopping, 7
27, Why, Gathering, 8
...
41, Commute, Own Car, 1
42, Commute, Walking, 2
...
 

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
Yes
Yes the data of those 6 tables all go into tblRisks. It would look like so:

tblRisks
risk_ID, risk_type, risk_Desc, risk_num
1, Infected, Yes, 10
2, Infected, No, 1
3, Infected, Not Sure, 5
4, Who, Myself, 1
5, Who, Friends, 3
...
25, Why, Work, 5
26, Why, Shopping, 7
27, Why, Gathering, 8
...
41, Commute, Own Car, 1
42, Commute, Walking, 2
...

@ Plog,
Each table has a ComboBox or OptionGroup connected that user can select the answer in each case.
For instance:
Click on Yes when asked "Were people around you infected?"
Click on Friends when asked "Who were you with in this meeting?"
Click on Bus when asked "What kind of transportation did you take to get to this meeting?"
....
Then after all fields are answered using combo boxes or option groups user clicks on save. Using a VBA module in AfterUpdate event of the button I will update the database with all foreign keys in proper columns.
With your system, I don't know how I can bring up only desired answers in combo boxes/option groups to different questions in order to update and add new row to my tbl_all_Logs in one click only.
That's why I have a table and combo box or option group for each question in the Data Entry Form.
Could you please explain me the best data input system to work with?
I hope you understand my grammar and writing.
MsDh
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 28, 2001
Messages
26,999
The typical method for this is to merge the tables but keep one more field that is used to define from which category each is taken. Then your combo boxes can all reference the same table but with a different WHERE clause that filters out only "friend" entries or only "mandate" entries or only "commute" entries or only "who" entries, etc.

Then you create a series of queries that all look the same but have different filtration. Then you create a UNION query to merge the several queries - looks like less than 10 of them. Then do a sum on the individual risk assessments as accumulated by the UNION query.
 

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
The typical method for this is to merge the tables but keep one more field that is used to define from which category each is taken. Then your combo boxes can all reference the same table but with a different WHERE clause that filters out only "friend" entries or only "mandate" entries or only "commute" entries or only "who" entries, etc.

Then you create a series of queries that all look the same but have different filtration. Then you create a UNION query to merge the several queries - looks like less than 10 of them. Then do a sum on the individual risk assessments as accumulated by the UNION query.
Thanks for explanation. I have no experience working with union queries at all. I am going to work on it tonight and see how far I can go then will update here if more hints were needed tomorrow. Before I start, is it possible to use the union query for ComboBoxes and OptionGroups simultaneously (I mean for both types, same query)?
Thank you again.
 

plog

Banishment Pending
Local time
Today, 12:42
Joined
May 11, 2011
Messages
11,611
We're getting pretty deep in the woods on this and really beyond the scope of the original post and its solution. One thing to understand with databases is that normalization (https://en.wikipedia.org/wiki/Database_normalization) dictates your tables and their fields--nothing else, not forms nor reports. So, no matter how you envision your input forms, it doesn't matter, the right structure for your data is the right structure for your data.

Now, my way for inputting data into this system would work like this. When a new record is created for tbl_all_Logs I would run code that execute 6 INSERT INTO queries into tblRiskLogs, 1 per risk category. I would make a continous form based on tblRiskLogs that show all 6 records per Log_ID. I would add a tab control to your existing form for tbl_all_Logs and on the second tab would add this new subform for tblRiskLogs. The user would tab to it after entering all data that goes into tbl_all_Logs. There they would use drop downs to select the correct risk_Desc for each of the 6 pre-populated risk_Type values.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 28, 2001
Messages
26,999
Silversun, please note that different people see the same things in different ways due to differences in background. Plog isn't wrong either. There are probably a dozen ways to approach this problem and you must find a method that works for you because you understand it AND it is technically feasible. June7, cronk, plog, and I have suggested that your structure is your enemy here. That "rat's nest" of relationships is the flare-lit tip-off that you have a problem of having overthought those risk findings.

Take a really hard look at that link plog gave you, then maybe come back and restate your original problem IN ENGLISH so that we can help you analyze your data and reach your goal.
 

silversun

Registered User.
Local time
Today, 10:42
Joined
Dec 28, 2012
Messages
204
The typical method for this is to merge the tables but keep one more field that is used to define from which category each is taken. Then your combo boxes can all reference the same table but with a different WHERE clause that filters out only "friend" entries or only "mandate" entries or only "commute" entries or only "who" entries, etc.

Then you create a series of queries that all look the same but have different filtration. Then you create a UNION query to merge the several queries - looks like less than 10 of them. Then do a sum on the individual risk assessments as accumulated by the UNION query.
Hi,
I have created my single table with all values in the same place rather than multiple tables. I understood what you all said about the structure. I tried my best (as always do) not to repeat data or tables. So now I have created a table that includes all risk numbers with their corresponding categories or descriptions. I also successfully created 5 different combo boxes with the same table and "WHERE" clause that specifies what items must be shown in each list.
I have to finish this project ASAP, as you can guess it is related to current global situation.
Now I am working on the new form and implementing other details before I can tell you if it is fixed. I will have questions when creating union queries and calculating the summation.

Thank you all for helps.
 

Users who are viewing this thread

Top Bottom