Need guidance for Inventory dbase, want to avoid calculated field.

[plisvb]

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.

 

[plisvb]

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

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?

 

[plisvb]

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

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?

 

[plisvb]

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

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]

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

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

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.

 

[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.

 

[plisvb via AccessMonster.com]

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.

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]

 

[plisvb via AccessMonster.com]

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.

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]

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

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.

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.

 

[Jeff Boyce]

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.

 

[Gina Whipp]

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

 

[John W. Vinson]

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]

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]

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

 

[Daryl S]

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.

 

[plisvb via AccessMonster.com]

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]

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]

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

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.

 

[John W. Vinson]

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]

Hi again John,

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

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.