Subform Caption To Show OrderStatus

[June7]

Yes, trying to save aggregate data instead of calculating when needed is problemmatic. How to make sure the adjustment is not posted repeatedly? Adjustment for new order item and revised order item. Very, very tricky. Maybe the AfterUpdate event of product quantity. Can't remember discussions in all your threads. Have you used the OldValue property of data controls (textbox, combobox, listbox, etc)? In the AfterUpdate event of quantity, if Value is different from OldValue then adjust product inventory and set Post field to true. Then there is the deletion of order item (this is another pitfall for allowing edit of transactions and saving aggregate data) that will also require an adjustment of inventory.

[burrina]

I am beginning to see your point about calculate later but it scares me. I see too many what if scenarios there as well. To my way of thinking it is deal with it now or deal with it later and experience has taught me never to put anything off. Maybe I am just way off her because of lack of experience. If all I worry about is absolute criteria and dont worry about posting then, hey no problem, until!!! Maybe they are in a datasheet now or something, dont know, too far down the road for me think about now.

[June7]

My 'calculate later' applies only if raw data transactions are entered for inventory in and out. A principle of relational database is 'enter raw data, calculate on reports'. By following this concept it doesn't matter what edits are done to the order records because the net difference of in and out records is a dynamic calculation based on the saved records. This way the aggregate values are not 'disconnected' from the data that produced them. The aggregate calcs can be supported by the raw data (an audit trail). With what you have, the beginning balance of inventory is obliterated with the first adjustment. If you don't know what the starting value was how can you verify that the products sold were accurately applied to adjust the inventory? How did you get from A to Z? A is now an unknown so there is no confidence Z is correct. You can build a db with your structure and make it work but doubt any competent fiscal officer would want it, at least not in an organization of any size or complexity. This 'data disconnect' does not follow best practice standards and an auditor would give negative assessment. The organization I worked for is subject to federal audit because of grant funding and would be slammed for using such a structure.

However, if there is no concern for supporting the inventory value with the raw data, then continue as you are. The challenges with this approach do seem greater.

[burrina]

So then, I should not worry about posting the record to inventory, the report will sort that out later? Only verify that a Customer,Employee and basic stuff is required?
I guess I am lost here because what if the record needs to be edited or completed? seems to me that a lot of checking on the part of the user would have to be done to verify data is correct that way! They enter a record and it is NOT forced to be posted to inventory or any status set, how then do you lookup records without the necessary requirements and fix them?I suppose a query and display those record and allow the user to go back and do what should have been done in the first place! This just does not make sense to me. If I eliminate posting from my design, does this now mean I am doing the raw data thing? I now have to design a form to address these issues! A report wont fix them, just display them.Thanks for trying to explain this to me, it makes sense kinda, but I am not sure how to implement it or what changes to drop and add. Do I now create a form/report that shows all of the records not posted/not paid but then on some form or datasheet it has to be addressed. Besides forcing them to post to inventory now, what other problems do you see with my db. I am open minded and would like to do this the right way. If you agree to take a look at it for a while, I would send the whole db , you already have the manual and some understanding of how it is structured do you not? Let me know if your willing to invest any more time with me or else I will move on but still thank you for your help.

Dave,

[June7]

So then, I should not worry about posting the record to inventory, the report will sort that out later?

Only if you input raw data records for products received as well as sold so that the net difference of their sums can calculate a balance of product on hand. You said you have this project split as 'Buy' and 'Sell' databases? The Buy part sounds like the raw data for products received.

I have attempted to describe best practice. Either you choose to use what I describe or you don't. I just wanted to help you make an informed choice. To understand the possible ramifications.

Maybe this will help.
The db I have now shows 5 Bon Bon in Inventory. OrderDetails shows 5 Bon Bon sold. If I physically count the Bon Bon's on shelf and there are really 7 (or only 3) - which should be correct, the 5 in record or 7 (or 3) on the shelf? Did product not get deducted (or did it get deducted twice)? I don't know what the starting quantity was so don't know what value 5 should have been deducted from to derive the balance. However, if there were raw data purchase records showing a total of 12 Bon Bon purchased and the total of sold is 5 then the Inventory balance is 7. I can view the detail records that affirm this. I now know there should be 7 Bon Bon on the shelf but are only 5 (or 3) - theft, breakage, duplicate shipment on same order? If there are 8 - received more than ordered and someone didn't verify the packing slip or a customer was shorted one item?

[burrina]

I will try and implement as best I can.

Thanks,

[June7]

I may have edited previous post after you first read it.

[burrina]

Thanks, again I will try and wrap my head around this and see how I can implement it. You have given me a lot of helpful information and I thank you for your time and willingness to help. I have gone back and done some restructuring and added fields and removed other code,etc... new datasheets for viewing Completed,Awaiting Approval and Inventory Receiving. Renamed all table names with descriptions and lots of other mundane but necessary stuff. I am no doubt not qualified to write this, but in the end I will get it right.
Thanks Again,
Dave