stuck, deliema in associating tables & calculating dimension

[jzwp22]

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.

Create a form based on the order detail table; I typically do this display this form in datasheet view. Use a combo box based on your material table & bind the combo box to the material field. Include the unit cost field in the combo box as well. In the after update event of the combo box, you will need to copy the unit cost of the material into the corresponding unit cost control of the subform (which is bound to the unit cost field of the underlying order detail table). This stores the current cost of the material to the order. That way you can increase the cost in the material table without impacting any previous orders. This is one of the only times where copying more that one field from one table to another would be justified. Save the form.

Now open you order form in design view. Drag and drop the form you created above. The order detail form is now considered a subform and if you have your relationships set up, Access will automatically link the record in the main form to the related records in the subform.

I actually create a sample database awhile back for another forum member that illustrates the above. It is attached.

I would derive this via a select query

Yes.

 

[vega]

Ok, Im following. How do you display a form in datasheet view in 2010?

 

[jzwp22]

Just go to the property sheet of the form and go to the Format tab. Set the Allow Datasheet View to Yes and the Default View to Datasheet

 

[vega]

that's a valuable example, im not sure how I would have figured that one on my own.

Regarding the combo box linked to tlkpMaterialList, I understand but here's the thing regarding unit cost; although I have a field I have no cost data yet; technically I wont have cost info until there's an order and I can derive CurrUnitCost from invoice. Is that ok?

I somewhat wanted it this way; from a cost accounting perspective I want the most accurate number so I didnt want the end user to have such information until there was in fact an order.

So wait for an order and populate CurrUnitCost in tlkpMaterialList? And revise a particular items CurrUnitCost in the event of a change?

 

[jzwp22]

Typically you would enter your costs in the material table. Then when an order is placed you add the material (via the combo box in the subform) and copy the current unit price that is stored in the material table to the order detail record. You can still manually change the cost in the subform for that particular order if you want.

If you have a price increase, you can apply that increase in the material table and all future orders will show the new price when you add the material to a new order. The older orders maintain the previous price.

 

[vega]

Good Moorning,

Alright, that’s what I thought and wanted to confirm.
I had an idea this morning; for food safety reasons I need to match application with the origin of the order best I can so... in the frmApplication what if I include a sub form featuring tblOrders so that the user can navigate what order the material came from and can then select from a combo box? I can then add a field in tblApplication to match what order a particular material came from.

in the tblOrderDetail I can never hide or inactivate an line to stop the user from selecting the same order over and over, I was just thinking with a sub form they can go the extra effort and navigate orders (permitting of course there is a report showing inventory)

 

[jzwp22]

The only way to include a subform is to add a new junction table between the application and order detail table. This table might be justified if multiple order detail items could be associated for 1 application event. I think that may be appropriate in your application

can navigate what order the material came from and can then select from a combo box

You should be able to base the combo box on a query that eliminates those order/order detail materials that have already been applied during previous application events.

 

[vega]

jz, I just wanted to thank you for the help and contribution in this task and project, it made a big difference and provided some momentum.


Can I feel free to post any potential followup issues?


Thanks again

 

[jzwp22]

You're welcome.

Since I may not always be available, it might be best to start a new thread, but you can always add a link to this thread to provide background information.

 

[vega]

Hello jz,

Stuck where I left off.

You should be able to base the combo box on a query that eliminates those order/order detail materials that have already been applied during previous application events.

Im lost as to how I'd create such a query. Ive still not created any queries and cant "see" how Id' be able able to tally application of one material aginst that materials inventory Q or exactly how im of managing inventory.

You mentioned not to have any calculated cells in my table. But I need to calculate total dollar amount of each application. I know that from tblApplication I can calculate total Q (acres X rate per acre), and I know that I can get cost per unit from tblOrderDetail, but how do I tie these together via a query? How do I perform a calculation in a query?

I understand basic queries and refreshed via lynda.com lessons but still at a loss as to the calculations. Its just hard to adapt the examples to my real-world situation.

 

[jzwp22]

Create a new query. Add the applications table and the order detail table (Access should carry over the joins between the tables)

In the lower grid, select the appID, acresapplied field and the rateAcre field from the application table and the currcost from the order details table. Now in an empty column type in the expression you need for example: currcost*acresapplied*rateacre (this is the calculated field). You will have to take care of any unit conversions of course. You can use any of the various arithmetic operators and the order of operations rules still apply. If you want you could switch from the design grid view to SQL view (I find it easier to build calculations there--just my personal preference). You can also give your calculated field a name or alias as follows:

currcost*acresapplied*rateacre as CostPerAcre