Auto Calculation between two tables in MS Access

[ace8works]

NEED HELP WITH THE ABOVE:​

1. As seen above there are 2 table in a database:-
(a) The "AuthRationOfficer" Table.
(b) The "RationOfficer" Table.

2. Here the access Database is connected to a VB.net program where it will display the Authorised amount of Ration depending on the following:-
(a) No of Days.
(b) No of people.
(c) VEG/NON VEG/EGG VEG/PURE VEG.

3. The Auth Amount of ration per person per day is as shown in the table "AuthRationOfficer".

4. The data of no of days, no of people and preference will be fed in the table "RationOfficer" via VB.net.

5. I need the Table "RationOfficer" to calculate the amount of atta, rice etc. as per the no of people, no of days and preference in the same table as save it as per date.

 

[arnelgp]

is it a simple calculation?
it look like you have same data, whether Veg, Nveg, etc?
can you use Update query:

update RationOfficer
SET atta = NoOfDays * DMax("atta", "AutRationOfficer"),
rice = NoOfDays * DMax("rice", "AutRationOfficer"),
dal = NoOfDays * DMax("dal", "AutRationOfficer"), ...

 

[ace8works]

Thank you for the reply
But am sorry am starting out of the Blue. Am new to MS access and queries to access is very less over the internet.

The problem is that when the data is getting updated it has to meet the condition if that person is a Veg, Non Veg as well. The data authorised for Veg, Non Veg, Egg Veg and Pure Veg is given at each row of the table AuthRationOfficer.

So when am updating in table RationOfficer, it need not check the DMax condition but it has to check if the checkbox of Veg or NVeg or EVeg or PVeg of RationOfficer table is tick and as per that the row in AuthOfficerRation needs to be selected for multiplication.

 

[mike60smart]

View attachment 90376View attachment 90377

NEED HELP WITH THE ABOVE:​

1. As seen above there are 2 table in a database:-
(a) The "AuthRationOfficer" Table.
(b) The "RationOfficer" Table.

2. Here the access Database is connected to a VB.net program where it will display the Authorised amount of Ration depending on the following:-
(a) No of Days.
(b) No of people.
(c) VEG/NON VEG/EGG VEG/PURE VEG.

3. The Auth Amount of ration per person per day is as shown in the table "AuthRationOfficer".

4. The data of no of days, no of people and preference will be fed in the table "RationOfficer" via VB.net.

5. I need the Table "RationOfficer" to calculate the amount of atta, rice etc. as per the no of people, no of days and preference in the same table as save it as per date.

Hi
I would say by looking at your table structures that these tables are not normalised.

You have names of items as fieldnames and these should be records in a table.

 

[ace8works]

Hi
I would say by looking at your table structures that these tables are not normalised.

You have names of items as fieldnames and these should be records in a table.

Not understood the context.

 

[mike60smart]

Not understood the context.

Can you upload a zipped copy of the database?

 

[ace8works]

Can you upload a zipped copy of the database?

 

[mike60smart]

4 tables, all with virtually the same number of fields names is wrong somehow.

When you say the tables are connected via VB.net, can you explain your process?

 

[ace8works]

4 tables, all with virtually the same number of fields names is wrong somehow.

When you say the tables are connected via VB.net, can you explain your process?

This is how my Form look in VB.Net and when data is filled in date, str, veg, Non veg, No of days textbox the data needs to be inserted into the database where it will be calculated as per the authration scale.

How this was helpful?

 

[mike60smart]

I have no knowledge of working with VB.Net so I will leave this for others to add comments to.

 

[ace8works]

I have no knowledge of working with VB.Net so I will leave this for others to add comments to.

Thank you

 

[FrankRuperto]

This is how my Form look in VB.Net and when data is filled in date, str, veg, Non veg, No of days textbox the data needs to be inserted into the database where it will be calculated as per the authration scale.

How this was helpful?

Regardless of vb.net form, you need to properly normalize the data. The four tables with the same fields is a bad design.

 

[plog]

First, put aside any thought of forms. Those are the last part of building a database system. As others have mentioned you need to work on normalization:

Database normalization - Wikipedia

en.wikipedia.org

That's the process of properly setting up your tables. Check out that link, work through a few tutorials and then apply what you learn to your database.

The big issue you have is that you are trying to store data horizontally with more fields and specific names. Instead, data should be stored vertically--with more rows and generic field names. For example-- [Coffee], [Tea] and [Jelly] should not be field names but values in a field. The table to accomodate those 3 items would look like this:

RationID, RationType, RationAmount
1, Coffee, .25
2, Tea, .08
3, Jelly, 1.1

In fact, a table structured with those 3 fields can hold an unlimited number of rations. You would just keep adding rows of data to the table with the correct value for RationType. You wouldn't add new fields to accomodate new ration types. That's what you need to do to fix your database--make it accomodate data vertically with generic field names and more rows of data to accomodate all your values.

Again, read up on normalization.