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 |
#1
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
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. |
#2
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
plisvb,
I can think of two options... Option 1. Have an Invoice Detail table that stores that calculated value. Option 2. Have the Invoice sent to a .pdf file and now it is forever immortalized. http://www.lebans.com/reporttopdf.htm I have actually used both and sometimes together. Yes, you will read that storing calculated fields is a big no-no BUT there are exceptions and you just happened on to one. -- 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" 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. |
#3
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
On Mon, 02 Nov 2009 22:15:44 GMT, "plisvb" u55912@uwe 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. This is a case where "calculated fields" aren't quite what they seem. You really have two price values - a current price, and a price as of the time of a previous transaction. These are different attributes of the item, and should be stored separately. Normally what one would do is actually *store* the price as of the time of transaction, in an InvoiceDetails table typically - you can do so in the afterupdate event of an item combo box, or some other appropriate event. How are your tables currently structured and related? It's not clear from your post. Surely there are multiple items per invoice, are there not? -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Thanks for the John,
There definitely are multiple items per invoice. My table structure is the following: Invoice table transactions table products table. Multiple transactions per table. Can you describe a little bit further your statement "InvoiceDetails table typically - you can do so in the afterupdate event of an item combo box, or some other appropriate event." Peter John W. Vinson wrote: Hello All, [quoted text clipped - 19 lines] Thanks everyone. This is a case where "calculated fields" aren't quite what they seem. You really have two price values - a current price, and a price as of the time of a previous transaction. These are different attributes of the item, and should be stored separately. Normally what one would do is actually *store* the price as of the time of transaction, in an InvoiceDetails table typically - you can do so in the afterupdate event of an item combo box, or some other appropriate event. How are your tables currently structured and related? It's not clear from your post. Surely there are multiple items per invoice, are there not? |
#5
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Thanks for the John,
There definitely are multiple items per invoice. My table structure is the following: Invoice table transactions table products table. Multiple transactions per table. Can you describe a little bit further your statement "InvoiceDetails table typically - you can do so in the afterupdate event of an item combo box, or some other appropriate event." also if you have an example available that would be awesome. Peter John W. Vinson wrote: Hello All, [quoted text clipped - 19 lines] Thanks everyone. This is a case where "calculated fields" aren't quite what they seem. You really have two price values - a current price, and a price as of the time of a previous transaction. These are different attributes of the item, and should be stored separately. Normally what one would do is actually *store* the price as of the time of transaction, in an InvoiceDetails table typically - you can do so in the afterupdate event of an item combo box, or some other appropriate event. How are your tables currently structured and related? It's not clear from your post. Surely there are multiple items per invoice, are there not? |
#6
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Thanks Gina for the reply,
The invoice details table seems to be a common answer. Could you further elaborate on how I would store that calculated value. I thought of the pdf idea also, not sure if it will be acceptable for my accountant though. Also, I have a whole bunch of reports (monthly/yearly) that won't be accurate if I don't find a more dynamic solution. Thanks again Gina Whipp wrote: plisvb, I can think of two options... Option 1. Have an Invoice Detail table that stores that calculated value. Option 2. Have the Invoice sent to a .pdf file and now it is forever immortalized. http://www.lebans.com/reporttopdf.htm I have actually used both and sometimes together. Yes, you will read that storing calculated fields is a big no-no BUT there are exceptions and you just happened on to one. Hello All, [quoted text clipped - 22 lines] Thanks everyone. |
#7
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
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. -- 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" u55912@uwe wrote in message news:9e8b07bf79e5c@uwe... Thanks Gina for the reply, The invoice details table seems to be a common answer. Could you further elaborate on how I would store that calculated value. I thought of the pdf idea also, not sure if it will be acceptable for my accountant though. Also, I have a whole bunch of reports (monthly/yearly) that won't be accurate if I don't find a more dynamic solution. Thanks again Gina Whipp wrote: plisvb, I can think of two options... Option 1. Have an Invoice Detail table that stores that calculated value. Option 2. Have the Invoice sent to a .pdf file and now it is forever immortalized. http://www.lebans.com/reporttopdf.htm I have actually used both and sometimes together. Yes, you will read that storing calculated fields is a big no-no BUT there are exceptions and you just happened on to one. Hello All, [quoted text clipped - 22 lines] Thanks everyone. |
#8
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
On Tue, 03 Nov 2009 03:12:13 GMT, "plisvb" u55912@uwe wrote:
Thanks for the John, There definitely are multiple items per invoice. My table structure is the following: Invoice table transactions table products table. Multiple transactions per table. Can you describe a little bit further your statement "InvoiceDetails table typically - you can do so in the afterupdate event of an item combo box, or some other appropriate event." You CERTAINLY need at least one more table. Surely you don't reenter all of the information about a product - its name, cost, etc. - again and again for every transaction??? The Products table should have a ProductID as its primary key, and additional information about the product (e.g. its current cost). There needs to be a table - I'll call it InvoiceDetails - related one to many to the Invoice table (each Invoice may have many InvoiceDetails), and also related one to many to the Products table (each Product may be sold many times, on different invoices). I'd expect the InvoiceDetails table to have fields InvoiceID (foreign key to the Invoices table); ProductID (foreign key to the Products table); PriceSold (a currency field holding the price at which that item was sold on that invoice); quantity; etc. - other information about the sale of *this* product on *this* invoice. You would probably use a Form based on Invoices with a Subform based on InvoicedDetails. There'd be a combo box on this subform to select which product is to be included; you could include the current price from the Products table in the Combo Box's rowsource query. The combo box could have code in its AfterUpdate event to "push" the price from the combo box into the PriceSold field. -- John W. Vinson JVinson *at* Wysard Of Info *dot* com -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
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 |
#10
|
|||
|
|||
Need guidance for Inventory dbase, want to avoid calculated field.
Hi John,
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. This may be an acceptable scenario for my client. Thanks for this. John W. Vinson wrote: Thanks for the John, [quoted text clipped - 9 lines] typically - you can do so in the afterupdate event of an item combo box, or some other appropriate event." You CERTAINLY need at least one more table. Surely you don't reenter all of the information about a product - its name, cost, etc. - again and again for every transaction??? The Products table should have a ProductID as its primary key, and additional information about the product (e.g. its current cost). There needs to be a table - I'll call it InvoiceDetails - related one to many to the Invoice table (each Invoice may have many InvoiceDetails), and also related one to many to the Products table (each Product may be sold many times, on different invoices). I'd expect the InvoiceDetails table to have fields InvoiceID (foreign key to the Invoices table); ProductID (foreign key to the Products table); PriceSold (a currency field holding the price at which that item was sold on that invoice); quantity; etc. - other information about the sale of *this* product on *this* invoice. You would probably use a Form based on Invoices with a Subform based on InvoicedDetails. There'd be a combo box on this subform to select which product is to be included; you could include the current price from the Products table in the Combo Box's rowsource query. The combo box could have code in its AfterUpdate event to "push" the price from the combo box into the PriceSold field. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
Thread Tools | |
Display Modes | |
|
|