Auto-Calculate Field from Other Table Values

[christakis]

Hello All,

I'd appreciate your input on how to set up my tables as I'm just starting off with setting my database up and I want to make sure I start off in the right direction.

I'm doing a simple database to track the purchase orders (PO) I am managing. Each PO has a PO Number and an Original Value. POs may have multiple amendments which would change the PO value. I would however like to keep the history of the PO original value and all different amendments.

So I created two tables:
tblPO:
ID
PO Number (Number)
PO Original Value (Currency)
PO Sum of Amendments (???????????)
PO Current Value (Calculated = PO Original - PO Sum of Amendments)

tblPOAmendments:
ID
PO (Lookup from tblPO)
PO Amended Value (Currency)
Amendment Date (Date/Time)
Amendment Desc (Text)

Now the two, million dollar questions are:
1.) Is this the right table structure to use.
2.) How do I go about calculating the Sum of Amendments field? - If you have a link to a tutorial which would apply to share this would be great!

Thanks,
Christakis

 

[plog]

1. Probably not
2. You tell me.

First some general naming advice. 1. Don't name a field just ID, prefix it with what it is an ID for (Amendment_ID, PO_ID, etc.). When you start writing queries and have 3 tables in there and they all have a generic field named 'ID' it will get confusing 2. Only use letters, numbers and underscores (no spaces or any weird characters). This will make queries and coding easier down the line.

Specific to your post: you don't store calculated fields. So any data that you can determine from other fields (e.g. PO Current Value, PO Sum of Amendments) you don't store. Instead you build a query and reference that query when you want the current value.

Lastly, you don't have a field named 'Amendments' so I don't know exactly how you would do it. An educated guess would be you meant the PO Amended Value field, but that's not entirely clear if its an amendment to an individual item on the PO or the entire PO. If it is just adding up the PO Amended Value field you would do so using a totals query: https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

 

[christakis]

1. Probably not
2. You tell me.

First some general naming advice. 1. Don't name a field just ID, prefix it with what it is an ID for (Amendment_ID, PO_ID, etc.). When you start writing queries and have 3 tables in there and they all have a generic field named 'ID' it will get confusing 2. Only use letters, numbers and underscores (no spaces or any weird characters). This will make queries and coding easier down the line.

Specific to your post: you don't store calculated fields. So any data that you can determine from other fields (e.g. PO Current Value, PO Sum of Amendments) you don't store. Instead you build a query and reference that query when you want the current value.

Lastly, you don't have a field named 'Amendments' so I don't know exactly how you would do it. An educated guess would be you meant the PO Amended Value field, but that's not entirely clear if its an amendment to an individual item on the PO or the entire PO. If it is just adding up the PO Amended Value field you would do so using a totals query: https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

Thanks for your reply. PO Amended Value, should read "PO Amendment Value" really. So my logic was have the original PO value in tblPO. Then calculate the current value based on the Original PO Value + (Sum of amendments against each PO).

I've already created a Sum query to get the Sum of amendments vs each PO, however I'm not sure how I go about to calculate the current PO value using the query results and original values. From what you're saying I should skip the "sum of amendments" and "current value" fields all together and just stick to the values being calculated in the forms/queries?

 

[plog]

Yes, any calculated value should be calculated, not stored.

From a practical standpoint, this means making a totals query on the tblPOAmendments table to add up all the values you want. Lets call that query 'AmendmentTotals. You would GROUP BY the PO field and SUM the value field. Then to get the current value you create a new query, using tblPO and AmendmentTotals, you would link them via the PO fields and then you could do math between the 2 relevant fields to get your Current value.

 

[christakis]

OK, thanks. Top man. I'll give it a go and see how it goes.