Need guidance for Inventory dbase, want to avoid calculated field.
plisvb
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.
Gina Whipp
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...
John W. Vinson
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]
plisvb
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]
plisvb
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]
>>
plisvb
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.
Gina Whipp
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...
John W. Vinson
>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
plisvb via AccessMonster.com
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,
>
>> Thanks Gina for the reply,
>>
>[quoted text clipped - 27 lines]
>>>>
>>>> Thanks everyone.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1
plisvb via AccessMonster.com
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.
--
Gina Whipp
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...
Jeff Boyce
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...
Gina Whipp
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" <nons...@nonsense.com> wrote in message
news:uWiGzDO...@TK2MSFTNGP04.phx.gbl...
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.
Jeff Boyce
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" <NotInt...@InViruses.com> wrote in message
news:%23rlQglO...@TK2MSFTNGP02.phx.gbl...
Gina Whipp
--
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" <nons...@nonsense.com> wrote in message
news:OqOoL9X...@TK2MSFTNGP04.phx.gbl...
Daryl S
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:
> .
>
plisvb via AccessMonster.com
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.
--
Message posted via http://www.accessmonster.com
John W. Vinson
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.
plisvb via AccessMonster.com
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]
--
John W. Vinson
wrote:
>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.
Forms aren't "linked". Is this in a Subform control? or are you opening the
transactions form using an OpenForm macro or VBA call?
>In the Subform I have a dropdown field called "Transaction Item" here's the
>code for that field:
A "dropdown field" is properly called a "Combo box". It helps if you use the
Access jargon...
>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;
What's the distinction between the Item Number and the ID? Is each "Item" in
the Inventory table unique? Does it correspond with what I've been calling a
"product"?
>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
That should work, if there's a textbox (not a field) on the form named
Current_Price.
>I'm not exactly sure if I've done something inherently wrong here. If you
>could advise, that would be great.
Is it working as you expect, or not? The VBA code looks ok, assuming the
control and fieldnames are correct.
plisvb via AccessMonster.com
1) "Forms aren't "linked". Is this in a Subform control? or are you opening
the
>transactions form using an OpenForm macro or VBA call?"
I'm using a subform.
2) "A "dropdown field" is properly called a "Combo box". It helps if you use
the
>Access jargon..."
my apologies
3) "What's the distinction between the Item Number and the ID? Is each "Item"
in
>the Inventory table unique? Does it correspond with what I've been calling a
>"product"?"
ID is the auto number field (key which nobody sees) Item Number is a number
created by my client although it is usually the UPC of the product preceded
by the supplier unique code (i.e. PA-00001)
Yes, each item is a product.
4) That should work, if there's a textbox (not a field) on the form named
>Current_Price.
This was a problem, I was using a field, I'm now using a textbox but whenever
I try to bind the textbox to my inventory transactions Current Price field I
get a #NAME? error.
this is my code to bind it: [Inventory Transactions]![Current Price]
and this where I'm now stuck.
Thank you soooo much
John W. Vinson wrote:
>>Ok so,
>>
>[quoted text clipped - 5 lines]
>>(Invoices) form. So I'm trying to implement the solution with what I've
>>already done.
>
>Forms aren't "linked". Is this in a Subform control? or are you opening the
>transactions form using an OpenForm macro or VBA call?
>
>>In the Subform I have a dropdown field called "Transaction Item" here's the
>>code for that field:
>
>A "dropdown field" is properly called a "Combo box". It helps if you use the
>Access jargon...
>
>>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;
>
>What's the distinction between the Item Number and the ID? Is each "Item" in
>the Inventory table unique? Does it correspond with what I've been calling a
>"product"?
>
>>I've also created a field called Current Price in my Inventory Transactions
>>Table and added that field to the subform.
>[quoted text clipped - 5 lines]
>>Me.Current_Price = Me.Transaction_Item.Column(4)
>>End Sub
>
>That should work, if there's a textbox (not a field) on the form named
>Current_Price.
>
>>I'm not exactly sure if I've done something inherently wrong here. If you
>>could advise, that would be great.
>
>Is it working as you expect, or not? The VBA code looks ok, assuming the
>control and fieldnames are correct.
--
John W. Vinson
wrote:
>4) That should work, if there's a textbox (not a field) on the form named
>>Current_Price.
>
>This was a problem, I was using a field, I'm now using a textbox but whenever
>I try to bind the textbox to my inventory transactions Current Price field I
>get a #NAME? error.
If you added the currentprice field to the table, it is probably not yet in
the Form's Recordsource. Open the Form in design view. View its Properties;
the first property on the Data tab will be the Recordsource. Click the ...
icon by it, and open it in query design view (if Access asks if you want to
create a query, accept the offer). Add the new field to the query and close
the query design window; accept Access' offer to save the SQL.
You should now be able to use currentprice as the control source of the
textbox.
plisvb via AccessMonster.com
still a no go.
I no longer have the #NAME? error but the field still doesn't seem to update
at all.
John W. Vinson wrote:
>>4) That should work, if there's a textbox (not a field) on the form named
>>>Current_Price.
>>
>>This was a problem, I was using a field, I'm now using a textbox but whenever
>>I try to bind the textbox to my inventory transactions Current Price field I
>>get a #NAME? error.
>
>If you added the currentprice field to the table, it is probably not yet in
>the Form's Recordsource. Open the Form in design view. View its Properties;
>the first property on the Data tab will be the Recordsource. Click the ...
>icon by it, and open it in query design view (if Access asks if you want to
>create a query, accept the offer). Add the new field to the query and close
>the query design window; accept Access' offer to save the SQL.
>
>You should now be able to use currentprice as the control source of the
>textbox.
--
plisvb via AccessMonster.com
Figured it out, I knew if was a bonehead mistake. I didn't have macros
enabled by default since upgrading my operating system and reinstalling
office.
Thank you so much for all of you help John, huge help.
plisvb wrote:
>Did that, I've added the field to the table and form's record source and
>still a no go.
>
>I no longer have the #NAME? error but the field still doesn't seem to update
>at all.
>
>>>4) That should work, if there's a textbox (not a field) on the form named
>>>>Current_Price.
>[quoted text clipped - 12 lines]
>>You should now be able to use currentprice as the control source of the
>>textbox.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1