stuck, deliema in associating tables & calculating dimension

[vega]

I need help. Im returning to Access after 10 + years as a very novice user.
Financial Business Analyst in Agriculture here in southern/central California.

Designing a database to record fertilizer and organic spray use in field. Primary table is "Applications", where such data is recorded. The user can only select a material that was in fact purchased, a purchased material will be documented in table "Orders" an is able to be selected in the Applications form.

Heres my deliema. Eventually the quantity of a material will run out and another order will have to occur. 1) I cant figure out how to associate material use or “total Q applied” field in Applications table with “Ending Inventory” field in the Orders table. I think part of the solution is a query and associative table. 2) even if I do associate, how can I reflect the event of depleting the total quantity of a given order and moving on to a new order of the same material? Its important to associate material use with the order that they originated from, for food safety reasons and for cost accounting.



Thank you and pardon in advanced if I wasn’t as clear as could be.

Attached relationships.

 

[jzwp22]

If I understand your application, an order of material is applied (several times) to a location (applications table), but as you state, you may need many orders of material to continue applying, so this implies that a record in the applications table may actually be related to many orders. That describes a many-to-many relationship which is not what you show in you relationship diagram.


Can the amount applied vary from one application to another?

Also, why do you repeat various fields in the applications table that are fields in related tables? i.e.Lake Ranch & Lake Lot in both the applications and Ranch tables. I also see a repetition of cost related fields as well. BTW, you should not have total cost fields in any of the tables since caluculated fields are generally not stored but calculated on the fly when you need them in forms, reports & queries.

 

[vega]

Hi JZ,

Thank you for your timley reply.

Yes, that's accurate. But im not sure about many to many within applications, Im thinking about this...

Many orders (and reorders) will occur. An order will yield many applications but an application will originate from only one order (unless I dont have enough of fertilizer “x” from order 123 and need more material from new order 456). I suppose there then would be 2 entried originating from each different order.

The amount applied will always be different, dependent on recommendation and acreage.

Repeat fields: I many not have named them properly and been redundant but the fields in Application table are being populated by content on other tables, i.e. Lake Ranch & Lot is populated by a combo box option in a form that is sourced to the Ranch table. I need control of that Ranch table from accuracy.

Total cost fields: I see, this is a db best practice? I need that calculation associated with each application, how would I do that? Im thinking too linear here.

 

[jzwp22]

Repeat fields: I many not have named them properly and been redundant but the fields in Application table are being populated by content on other tables, i.e. Lake Ranch & Lot is populated by a combo box option in a form that is sourced to the Ranch table. I need control of that Ranch table from accuracy.

Looking at your relationship diagram again, I was probably mistaken. The application table is the junction table for a many-to-many relationship between ranch and order, so you may be OK.

From your diagram it looks like an order consists of only 1 material. Is that typical? Or can an order contain many materials?

 

[robertbwainwright]

I think you may want to creat a table that keeps the qty on hand by item. When an item comes in you do an update query that updates the qty on hand to be qty on hand + qty rcvd. When you use a product do another update query that is qty on hand - qty used.

 

[jzwp22]

Contrary to robertbwainwright reply, I think you should be able to use a query to sum up the quantity applied and group by orderID and subtract that from the amount on the order to get the remaining amount still available for application. You can then compare this to the average quantity per application to see if you have to obtain another order.

 

[vega]

Thank you gentleman,

JZ, good catch – yes, an order can contain many materials, especially with respect to organic spray; so... I will have to itemize each order (invoice being unique identifier) by material.

Robert, That’s along the lines of what I was brainstorming. I’m foggy on queries and trying to re-familiarize myself. So add a table + update query? I would have to associate each quantity with an order number to keep track as we'd be following last in first out inventory and cost per unit may change.

JZ, so a query importing Q applied from Application table AND material from the Order table and deriving the difference between the two? This would be a stored query I would periodically run manually updating a remaining inventory in the orders table?

As I said, following LIFO, a new order of the same material may fluctuate in cost so cost per unit may change. I need the ability to match Apps with the proper Order. A little lost here too; I don’t see where this can be managed or where there’s an perpetually updated remaining inventory so I know when to move on to another orders Q and cost.

Referring to your very first comment, where would I calculate the total cost of a certain application?

My heads slightly spinning with this, its also a busy day here at the office. Im a financial analyst first so the obvious may not yet be obvious to me.

 

[jzwp22]

JZ, so a query importing Q applied from Application table AND material from the Order table and deriving the difference between the two? This would be a stored query I would periodically run manually updating a remaining inventory in the orders table?

No updating necessary, just run the SELECT query.

As I said, following LIFO, a new order of the same material may fluctuate in cost so cost per unit may change. I need the ability to match Apps with the proper Order. A little lost here too; I don’t see where this can be managed or where there’s an perpetually updated remaining inventory so I know when to move on to another orders Q and cost.

If you have many materials in an order, they you have to join your applications table to the material in the order detail table (you will need to add that table) not to the order itself.

tblOrderDetails
-pkOrderDetailID primary key, autonumber
-fkOrderID foreign key relating back to table Orders
-fkMaterialID foreign key to Materials List table and a foreign key to the applications table
-QTY (quantity ordered)
-OrderUnitCost (copied in at time of order)

Of course you will have to remove the material field from your order table.

 

[vega]

Thank you kindly jz, this helps and more importantly Im better understanding the overall logic/architecture of the db.

I attached new relationships reflectign my changes. Is this better? Im going to work on the query tonight.

Thanks again

 

[jzwp22]

Your relationship diagram is better, but it still needs more work. As I said earlier, you have a lot of fields that repeat that need to be cleaned up

In tbl_Applications, you do not need the following fields:

Lake Ranch (you can get this based on the relationship with tlkp_Ranch)
Lake Lot (you can get this based on the relationship with tlkp_Ranch)
Unit cost (you can get this based on the relationship to tbl_OrderDetail)
total cost (you can calculate this)
Rate/Acre this sounds like a calculate field so it should not be stored but rather calculated on the fly when you need it.

In tbl_Orders, you do not need the following fields:
Material cost (this field is now in tbl_OrderDetail, so you do not need it in tbl_Orders)
total cost (again this is a calculated value and should not be stored)

In tbl_orderdetail, since you have quantity, UOM,and material cost, I do not think you need cost per unit (that can be calculated).

Also, as a general recommendation, it is usually best not to have spaces or special characters in your table or field names. The underscore you use in the table names are OK but the / in Rate/Acre. Also, the word date is a reserved word in Access, so it should not be used as a field name (appDate perhaps would be a good substitute name?)

 

[vega]

In tbl_Applications, you do not need the following fields:
Lake Ranch (you can get this based on the relationship with tlkp_Ranch)
Lake Lot (you can get this based on the relationship with tlkp_Ranch)
Unit cost (you can get this based on the relationship to tbl_OrderDetail)
total cost (you can calculate this)
Rate/Acre this sounds like a calculate field so it should not be stored but rather calculated on the fly when you need it.

LakeRanch and LakeLot is info I need present in tblApplication for each entry, I will define the options whoch will reside in the tklpRanch; I think you mean for me NOT to name these fields in the tblApp the same field titles in the tblRanch, yes?

Unit cost, yes, I will be able to get this now. Total cost, I was calculating in App table, should I keep it calculated in the table or calculate outside via a query?

In tbl_Orders, you do not need the following fields:
Material cost (this field is now in tbl_OrderDetail, so you do not need it in tbl_Orders)
total cost (again this is a calculated value and should not be stored)

This may be daft but Im not query savvy, when you say "should be a calculated value and not be stored" you mean do so via a query?

I appreciate the best practices comments - sure, that makes sense on the field titles and "/" , I was in a hurry to get the tables laid out and intended to clean that up.

 

[jzwp22]

LakeRanch and LakeLot is info I need present in tblApplication for each entry, I will define the options whoch will reside in the tklpRanch; I think you mean for me NOT to name these fields in the tblApp the same field titles in the tblRanch, yes?

No! that is not what I mean.

I assume that the information in the fields LakeRanch and LakeLot is the same information as in the corresponding fields in tklpRanch. If that is indeed the case, repeating the information in tblapplications is a violation of normalization rules and can lead to data integrity problems. To bring the Ranch information together with the application information you would use a query. The tables are logically joined so there is no need to repeat the information.

Total cost, I was calculating in App table, should I keep it calculated in the table or calculate outside via a query?

It should not be in the table. You can calculate it in a query, form or report when you need it.

This may be daft but Im not query savvy, when you say "should be a calculated value and not be stored" you mean do so via a query?

You can do it in a query or you can do it via an expression in a control on a form or a report. It just depends where you want to show the calculated value.

 

[vega]

good morning,

No! that is not what I mean.

I assume that the information in the fields LakeRanch and LakeLot is the same information as in the corresponding fields in tklpRanch. If that is indeed the case, repeating the information in tblapplications is a violation of normalization rules and can lead to data integrity problems.

I completley understand BUT maybe im missing something; the tblApplication is the main table that the end user will be populating; an event occurs and they make an entry. That event will always take place on a ranch; via planning and modeling (main pilliar of my job function) I know what those ranch codes (lot), names and block numbers are. I am pre-populating this information in tlkpRanch so that this is what the end user is restricted to using (they cant their own descriptors or disambiguation of a universal idenficitation).

So, performing dry runs with my form, the ranch manager is able to initiate a new entry in the tblApplication using frmApplications and via a combo box (presenting values from tlkpRanch), can select "what ranch" they performed an application on and that is "dumped" into the fields in tblApplication.

each Application (entry) takes place on one ranch but one ranch can have many Applications

I may not be naming fields properly, but considering all of this, is it not ok for me to have a prepopulated linking table that predefines a cooresponding or related field in another table?

thank you for your patience

 

[jzwp22]

I may not be naming fields properly, but considering all of this, is it not ok for me to have a prepopulated linking table that predefines a cooresponding or related field in another table?

The linking table is fine.

The key is that only 1 field that matches a field in the link table should be in the related table (application table) not several fields. You have that in the HVLot field which links back to the LotID field of the tlkp_Ranch. Since you link through that, field repeating any other information from the other fields of tlkp_Ranch is unnecessary and would violate normalization rules.

On your application form, you can still show the information from the other fields of the linking table but you would only store the key value in the table. I've attached an example. Please take a look at the form I've included.

 

[dipok]

Hi! I have faced a very critical problem. That is, When i create a drop down list in form but it does not shown in report. What can I do for this problems?

Pls answer .

 

[jzwp22]

Forms and reports are different. To show the information from a linked table along with the data from a primary table, you need to create a query that has both tables in it. You would then base your report on that query.

 

[vega]

The linking table is fine.

The key is that only 1 field that matches a field in the link table should be in the related table (application table) not several fields. You have that in the HVLot field which links back to the LotID field of the tlkp_Ranch. Since you link through that, field repeating any other information from the other fields of tlkp_Ranch is unnecessary and would violate normalization rules.

Got it! These methodologies are important for me to follow and I want it to be subconscious as I set up for new projects in the future.

Thank you for the example – your form and example is identical to what I created, I just have more linking fields. The main purpose for this is to associate all of the variables into one neat Application table for food safety purposes, derive inventory/manage supply and how much each App cost.

So I think I have the architecture down now I must face query I need to

• derive cost of each application (cost accounting function)
  • is this an action query?
• manage & trace inventory
  • is this an update query?

 

[jzwp22]

associate all of the variables into one neat Application table for food safety purposes

One neat table is not how relational databases work. You will want the separate tables and make a neat query that brings the data you want together.

derive cost of each application (cost accounting function)
is this an action query?
manage & trace inventory
is this an update query?

These should be handled with SELECT queries not action queries

With respect to managing inventory, do you mean what has been ordered and what has been applied as we have already been discussing?

 

[vega]

While I ponder queries...

I am making/modifying forms. You made a helpful recommendation; create tblOrderDetails to itemize all materials in a given order. So I am trying to put a form together from tblOrders so that the user can enter order data; how can I have them use the same form to enter (multiple) materials within an order and get those entries to the tblOrderDetail? i.e. how to I correlate materials with their parent order? I may be over thinking something here and its simple.

 

[vega]

With respect to managing inventory, do you mean what has been ordered and what has been applied as we have already been discussing?

essentially, so that I can assess whats been applied of a certain material versus how much is on hand AND to match materials with their purchase cost (material X may have a different unit cost in 3 weeks)

I would derive this via a select query