If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
plisvb,
No the price would be *put* in the Price field in using and Update query in the After_Update event of the Grocery Item. No ectra typing. Now, you would write that After_Update event to only take place if the Price is 0 (zero) or Null. Now the Price is there, no typing and even if you change the Price in the Source table no effect on your tblInvoiceDetails. Oops, almost forgot, you will want to write something for the On_Change of the Grocery Item in case and Item is selected by mistake. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "plisvb via AccessMonster.com" u55912@uwe wrote in message news:9e905c9b632a4@uwe... Thank you Gina, This is exactly the way I have the tables set up except I called my table "Transactions" instead of "tblInvoiceDetails". Unless I've misunderstood, the way you set it up, doesn't the user have input price at each instance of an invoice? My client wants to avoid this situation, he wants a manager to be able to set the prices on his own via the products table and have that change the price for any "new" invoices. I appreciate your help. Gina Whipp wrote: plisvb, I would have... tblInvoice iInvoiceiD (PK) iDate iCustomerID (FK linked to PK in tblCustomers) etc... tblInvoiceDetails idInvoiceDetailID (PK) idInvoiceID (FK linked to PK in tblInvoice) idQuantity idUnitOfMeasure (Optional field) idGroceryID (FK linked to PK in tblGroceries) idDescription idPrice idBackOrdered (Optional field) idApplyCoupon (Optional field) tblGroceries gGroceryID (PK) gItemID gBrandID (Optional field) gDescription gPrice gUnitOfMeasure gIsle (Optional field) tblCustomers cCustomerID cCompanyName cFirstName cLastName cAccountID etc... In the above scenario the Price would be stored in tblInvoiceDetails and/or you can also store the calculated total there. But as long as you are storing the Price of the Item at that time up to you whether you want to store the calculated total. In this scenario the tblGroceries Prices can change anytime but will not effect tblInvoiceDetail. I didn't know where to put your Transactions table because I am not sure what it is. It might be the tblInvoiceDetails but only you can answer that. Thanks Gina for the reply, [quoted text clipped - 27 lines] Thanks everyone. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
#12
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Gina & John have pointed out the approach of storing the price-at-sale
value. An alternate approach would be to keep a pricing history, rather than a "current price". In this scenario, each item's price has a date range during which it is applicable. When the price of an item changes, the former "current" price gets end-dated and the new "current" price gets a start date on a new record. To reconstruct an old invoice, your query finds the prices as-of-the-invoice-date. Just another approach... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "plisvb" u55912@uwe wrote in message news:9e8843c5bc41f@uwe... Hello All, I've searched through countless "calculated fields" threads but I've never found an answer to my question. I'd like to know the best way to set the following up? Inventory database for groceries, product prices change constantly. I've created an invoice form that calculates (price from a products table * quantity) and that works fine. I print out an invoice and it's perfect. However, if the price of product "A" for example changes a week later and I need to reprint that same invoice from a week ago, my totals are obviously different and my accountant yells at me. I need to find a way to freeze the invoice and price field in time so that it's calculated only at the time of initial processing. I'd like to know the best way to do this without having the user input the prices on the fly for every single transaction. Thanks everyone. |
#13
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Jeff,
And please note not attacking your suggestion, just making an observation... I would never use that approach for groceries. Have you been to the supermarket? I get a flier from mine every week with the *new* sale prices. The prices changes weekly (sometimes nightly). I would use that approach if I was running some type of rewards prgram or even manufactoring (where prices changes maybe yearly) or even tracking salaries but never groceries as that table has the potential to become hugh in your scenario. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Jeff Boyce" wrote in message ... Gina & John have pointed out the approach of storing the price-at-sale value. An alternate approach would be to keep a pricing history, rather than a "current price". In this scenario, each item's price has a date range during which it is applicable. When the price of an item changes, the former "current" price gets end-dated and the new "current" price gets a start date on a new record. To reconstruct an old invoice, your query finds the prices as-of-the-invoice-date. Just another approach... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "plisvb" u55912@uwe wrote in message news:9e8843c5bc41f@uwe... Hello All, I've searched through countless "calculated fields" threads but I've never found an answer to my question. I'd like to know the best way to set the following up? Inventory database for groceries, product prices change constantly. I've created an invoice form that calculates (price from a products table * quantity) and that works fine. I print out an invoice and it's perfect. However, if the price of product "A" for example changes a week later and I need to reprint that same invoice from a week ago, my totals are obviously different and my accountant yells at me. I need to find a way to freeze the invoice and price field in time so that it's calculated only at the time of initial processing. I'd like to know the best way to do this without having the user input the prices on the fly for every single transaction. Thanks everyone. |
#14
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
On Tue, 03 Nov 2009 13:53:06 GMT, "plisvb via AccessMonster.com" u55912@uwe
wrote: Yes, this is how I have it set up however, I never thought of using a combo box to allow the user to select the price from a list predetermined by a manager. I haven't either, and that's not what I was suggesting! I'm ASSUMING that there is a products table with the unique ID of the product, its name, and its current price. The user could select the *PRODUCT* - by name - from the combo box. In the combo box's AfterUpdate event you could have code like Private Sub cboProductID_AfterUpdate() Me!txtCurrentPrice = Me!cboProductID.Column(2) End Sub This would let the user select a product from the combo box; store that product ID in the Transactions table (as the bound column of the combo box); and update the textbox txtCurrentPrice - bound to the Price field in the Transactions table - to the value found in the third column of the combo's rowsource query (the Column property is zero based). This value could be edited after the selection, if the manager wants to offer a discount or charge a premium. -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
I absolutely agree. Any items with high "velocity" in pricing would NOT be
a candidate for that approach ... unless it is precisely that high velocity that the database is designed to track! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Gina Whipp" wrote in message ... Jeff, And please note not attacking your suggestion, just making an observation... I would never use that approach for groceries. Have you been to the supermarket? I get a flier from mine every week with the *new* sale prices. The prices changes weekly (sometimes nightly). I would use that approach if I was running some type of rewards prgram or even manufactoring (where prices changes maybe yearly) or even tracking salaries but never groceries as that table has the potential to become hugh in your scenario. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Jeff Boyce" wrote in message ... Gina & John have pointed out the approach of storing the price-at-sale value. An alternate approach would be to keep a pricing history, rather than a "current price". In this scenario, each item's price has a date range during which it is applicable. When the price of an item changes, the former "current" price gets end-dated and the new "current" price gets a start date on a new record. To reconstruct an old invoice, your query finds the prices as-of-the-invoice-date. Just another approach... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "plisvb" u55912@uwe wrote in message news:9e8843c5bc41f@uwe... Hello All, I've searched through countless "calculated fields" threads but I've never found an answer to my question. I'd like to know the best way to set the following up? Inventory database for groceries, product prices change constantly. I've created an invoice form that calculates (price from a products table * quantity) and that works fine. I print out an invoice and it's perfect. However, if the price of product "A" for example changes a week later and I need to reprint that same invoice from a week ago, my totals are obviously different and my accountant yells at me. I need to find a way to freeze the invoice and price field in time so that it's calculated only at the time of initial processing. I'd like to know the best way to do this without having the user input the prices on the fly for every single transaction. Thanks everyone. |
#16
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Point taken...
-- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Jeff Boyce" wrote in message ... I absolutely agree. Any items with high "velocity" in pricing would NOT be a candidate for that approach ... unless it is precisely that high velocity that the database is designed to track! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Gina Whipp" wrote in message ... Jeff, And please note not attacking your suggestion, just making an observation... I would never use that approach for groceries. Have you been to the supermarket? I get a flier from mine every week with the *new* sale prices. The prices changes weekly (sometimes nightly). I would use that approach if I was running some type of rewards prgram or even manufactoring (where prices changes maybe yearly) or even tracking salaries but never groceries as that table has the potential to become hugh in your scenario. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Jeff Boyce" wrote in message ... Gina & John have pointed out the approach of storing the price-at-sale value. An alternate approach would be to keep a pricing history, rather than a "current price". In this scenario, each item's price has a date range during which it is applicable. When the price of an item changes, the former "current" price gets end-dated and the new "current" price gets a start date on a new record. To reconstruct an old invoice, your query finds the prices as-of-the-invoice-date. Just another approach... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "plisvb" u55912@uwe wrote in message news:9e8843c5bc41f@uwe... Hello All, I've searched through countless "calculated fields" threads but I've never found an answer to my question. I'd like to know the best way to set the following up? Inventory database for groceries, product prices change constantly. I've created an invoice form that calculates (price from a products table * quantity) and that works fine. I print out an invoice and it's perfect. However, if the price of product "A" for example changes a week later and I need to reprint that same invoice from a week ago, my totals are obviously different and my accountant yells at me. I need to find a way to freeze the invoice and price field in time so that it's calculated only at the time of initial processing. I'd like to know the best way to do this without having the user input the prices on the fly for every single transaction. Thanks everyone. |
#17
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
The correct way would be to have a separate pricing table for your products,
where each product can have multiple prices, but no more than one price on any given day. This does make the queries and code much more complex, as they do a lot more work during the joins to find the price that was current at some past date. A simpler solution would be to expand your invoice item table to include the price on the processing date. Then you would not need to look up the price again, just calculate the amount from the quantity and price on the invoice item record. -- Daryl S "plisvb" wrote: Hello All, I've searched through countless "calculated fields" threads but I've never found an answer to my question. I'd like to know the best way to set the following up? Inventory database for groceries, product prices change constantly. I've created an invoice form that calculates (price from a products table * quantity) and that works fine. I print out an invoice and it's perfect. However, if the price of product "A" for example changes a week later and I need to reprint that same invoice from a week ago, my totals are obviously different and my accountant yells at me. I need to find a way to freeze the invoice and price field in time so that it's calculated only at the time of initial processing. I'd like to know the best way to do this without having the user input the prices on the fly for every single transaction. Thanks everyone. . |
#18
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Ok so,
Been trying to figure it out by myself... needless to say I've never coded in VB before so I'm not exactly sure what I'm doing wrong. I've add an extra field in my inventory transactions subform called currentprice. I've create an After Update event on the Transaction Item field (drop down list all products) Here's the code: Private Sub Transaction_Item_AfterUpdate() currentprice = Inventory!Cost End Sub Inventory!Cost is my product table called Inventory and the field containing the price is "cost" Why won't it work!!! Am I missing something? Thank you very much everyone, this is such a great resource for people to have. John W. Vinson wrote: Yes, this is how I have it set up however, I never thought of using a combo box to allow the user to select the price from a list predetermined by a manager. I haven't either, and that's not what I was suggesting! I'm ASSUMING that there is a products table with the unique ID of the product, its name, and its current price. The user could select the *PRODUCT* - by name - from the combo box. In the combo box's AfterUpdate event you could have code like Private Sub cboProductID_AfterUpdate() Me!txtCurrentPrice = Me!cboProductID.Column(2) End Sub This would let the user select a product from the combo box; store that product ID in the Transactions table (as the bound column of the combo box); and update the textbox txtCurrentPrice - bound to the Price field in the Transactions table - to the value found in the third column of the combo's rowsource query (the Column property is zero based). This value could be edited after the selection, if the manager wants to offer a discount or charge a premium. -- Message posted via http://www.accessmonster.com |
#19
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
On Wed, 04 Nov 2009 21:39:08 GMT, "plisvb via AccessMonster.com" u55912@uwe
wrote: Ok so, Been trying to figure it out by myself... needless to say I've never coded in VB before so I'm not exactly sure what I'm doing wrong. I've add an extra field in my inventory transactions subform called currentprice. Did you add an extra *FIELD IN THE TABLE* called currentprice? Or just in the form? If in the form, it won't be stored or remembered anywhere. You'll need to add the field to the Table, and then open the Form in design view, view its Recordsource property, add the field, and *then* add a textbox to your form. I've create an After Update event on the Transaction Item field (drop down list all products) Here's the code: Private Sub Transaction_Item_AfterUpdate() currentprice = Inventory!Cost End Sub This bears no resemblance to what I posted, of course; and will fail for at least three reasons (there's no variable named currentprice, you can't refer to a table in that way, and referring to a table wouldn't select the right row from the table even if you could). I cannot see your form or your tables, so I'm groping in the dark here a bit... but let me suggest the following: 1. Use a Subform bound to the inventory transactions table. 2. On that subform, put a Combo Box named cboProduct based on a query like: SELECT Inventory.InventoryID, Inventory.ProductName, Inventory.Cost FROM Inventory ORDER BY ProductName; to select the product. Bind this combo to the InventoryID field in the Transactions table (which item is being selected); set the combo's ColumnWidths property to something like 0";1";0" to display only the product name. Put a textbox txtProductCost on the form, bound to the transactions table Cost field. In the combo box's AfterUpdate event put Private Sub cboProduct_AfterUpdate Me!txtProductCost = Me!cboProduct.Column(2) End Sub to "push" the cost from the third field in the product query (the Column proprety is zero based) into the textbox. -- John W. Vinson [MVP] |
#20
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Ok so,
First of all thanks for your help. I'm learning a lot even in my failures. I believe that I've duplicated what you said to do but it still doesn't work. I already have a transactions subform which is linked to an overall Orders (Invoices) form. So I'm trying to implement the solution with what I've already done. In the Subform I have a dropdown field called "Transaction Item" here's the code for that field: SELECT Inventory.[Item Number], Inventory.Item, Inventory.ID, Inventory. Discontinued, Inventory.Cost FROM Inventory WHERE (((Inventory.Discontinued) =False)) ORDER BY Inventory.[Item Number], Inventory.Item; I've also created a field called Current Price in my Inventory Transactions Table and added that field to the subform. My code for the event update added to the Transaction Item field is the following: Private Sub Transaction_Item_AfterUpdate() Me.Current_Price = Me.Transaction_Item.Column(4) End Sub I'm not exactly sure if I've done something inherently wrong here. If you could advise, that would be great. Many Thanks again John W. Vinson wrote: Ok so, [quoted text clipped - 3 lines] I've add an extra field in my inventory transactions subform called currentprice. Did you add an extra *FIELD IN THE TABLE* called currentprice? Or just in the form? If in the form, it won't be stored or remembered anywhere. You'll need to add the field to the Table, and then open the Form in design view, view its Recordsource property, add the field, and *then* add a textbox to your form. I've create an After Update event on the Transaction Item field (drop down list all products) [quoted text clipped - 4 lines] currentprice = Inventory!Cost End Sub This bears no resemblance to what I posted, of course; and will fail for at least three reasons (there's no variable named currentprice, you can't refer to a table in that way, and referring to a table wouldn't select the right row from the table even if you could). I cannot see your form or your tables, so I'm groping in the dark here a bit... but let me suggest the following: 1. Use a Subform bound to the inventory transactions table. 2. On that subform, put a Combo Box named cboProduct based on a query like: SELECT Inventory.InventoryID, Inventory.ProductName, Inventory.Cost FROM Inventory ORDER BY ProductName; to select the product. Bind this combo to the InventoryID field in the Transactions table (which item is being selected); set the combo's ColumnWidths property to something like 0";1";0" to display only the product name. Put a textbox txtProductCost on the form, bound to the transactions table Cost field. In the combo box's AfterUpdate event put Private Sub cboProduct_AfterUpdate Me!txtProductCost = Me!cboProduct.Column(2) End Sub to "push" the cost from the third field in the product query (the Column proprety is zero based) into the textbox. -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|