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

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.

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.

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]

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.

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.

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.

.

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

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]

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