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.

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.

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]

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?

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?

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.

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.

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]

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

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