Archiving records question

[Slez via AccessMonster.com]

I'm debating (with myself) whether or not to archive records in a database
and would like to hear opinions on what others may have done, or what others
may think is best for my situation. I need to explain what I'm trying to
achieve.

I manage a database that we use for estimating projects. It's a fairly basic
structure:
(1) Project > (Many) Items > (Many) Item Details
One of the fields in Item Details is "ProductDescription" wher users can
select from another table called Product. Each record in that table has an
associated UnitCost.
So ultimately there is one record in the Product table related to many in the
Item Details table.

Periodically, as with any business, UnitCost of a given Product is adjusted.
This in turn changes the amount for a project because the Products selected
change in cost.

My thought is that in order to "lock in" the UnitCost as products are
selected and added to the items on projects, I'd need to append these to
another table or even another database. Does that sound logical, or is there
a better way to lock these prices in once selected?
Any thoughts or suggestions are appreciated?

 

[Rick Brandt]

I'm debating (with myself) whether or not to archive records in a
database and would like to hear opinions on what others may have
done, or what others may think is best for my situation. I need to
explain what I'm trying to achieve.

I manage a database that we use for estimating projects. It's a
fairly basic structure:
(1) Project > (Many) Items > (Many) Item Details
One of the fields in Item Details is "ProductDescription" wher users
can select from another table called Product. Each record in that
table has an associated UnitCost.
So ultimately there is one record in the Product table related to
many in the Item Details table.

Periodically, as with any business, UnitCost of a given Product is
adjusted. This in turn changes the amount for a project because the
Products selected change in cost.

My thought is that in order to "lock in" the UnitCost as products are
selected and added to the items on projects, I'd need to append these
to another table or even another database. Does that sound logical,
or is there a better way to lock these prices in once selected?
Any thoughts or suggestions are appreciated?

You should have a field in ItemDetails for UnitCost and copy it there at the
time you enter the ProductDescription. This is not a break from
normalization rules exactly for the reason you have stated. It is a value
that could change in the source table and you need a record of its value at
the time of each entry into the ItemDetails table.

 

[Jeff Boyce]

Save yourself (and your users) a lot of extra work. If you have a Product's
UnitCost that is good for some period of time, add StartDate and EndDate
fields to your table to show when that product's unitcost is valid.

?New unitcost for a product? New record, with a new startdate (and the old
record for that product end-dated)!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joan Wild]

Just store the price in the Item Details table. This isn't redundant, as
you are storing the price *as it was at the time you selected it*. If you
look in the Northwind Sample database, they show how this is done in the
Order Details table and form, where the user selects the product, and the
current price is automatically stored along with the product in the Order
Details table.

 

[Slez via AccessMonster.com]

Thanks to all for the replies! Great input as always!

I checked out the Northwind Sample database and see how that works. That's
exactly what I want to accomplish so I'm going to give it a try!

I do have a follow up question though. I'm going to create a new field
called UnitPrice in my ItemDetail table. There are in excess of 60,000
existing records that I would need to append with the current price from the
product table. Is there any easy way of doing that. It seems quite daunting
to have to go through all of the records and update the price manually.

Thanks once again!
Slez

Just store the price in the Item Details table. This isn't redundant, as
you are storing the price *as it was at the time you selected it*. If you
look in the Northwind Sample database, they show how this is done in the
Order Details table and form, where the user selects the product, and the
current price is automatically stored along with the product in the Order
Details table.

I'm debating (with myself) whether or not to archive records in a database
and would like to hear opinions on what others may have done, or what

[quoted text clipped - 26 lines]

a better way to lock these prices in once selected?
Any thoughts or suggestions are appreciated?

 

[Jeff Boyce]

You don't mention where the list of 60,000 is coming from...

Could you use an append query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks to all for the replies! Great input as always!

I checked out the Northwind Sample database and see how that works.
That's
exactly what I want to accomplish so I'm going to give it a try!

I do have a follow up question though. I'm going to create a new field
called UnitPrice in my ItemDetail table. There are in excess of 60,000
existing records that I would need to append with the current price from
the
product table. Is there any easy way of doing that. It seems quite
daunting
to have to go through all of the records and update the price manually.

Thanks once again!
Slez

Just store the price in the Item Details table. This isn't redundant, as
you are storing the price *as it was at the time you selected it*. If you
look in the Northwind Sample database, they show how this is done in the
Order Details table and form, where the user selects the product, and the
current price is automatically stored along with the product in the Order
Details table.

I'm debating (with myself) whether or not to archive records in a
database
and would like to hear opinions on what others may have done, or what

[quoted text clipped - 26 lines]

a better way to lock these prices in once selected?
Any thoughts or suggestions are appreciated?

 

[Joan Wild]

Use an update query to apply the existing price to the existing records.

--
Joan Wild
Microsoft Access MVP

Thanks to all for the replies! Great input as always!

I checked out the Northwind Sample database and see how that works.
That's
exactly what I want to accomplish so I'm going to give it a try!

I do have a follow up question though. I'm going to create a new field
called UnitPrice in my ItemDetail table. There are in excess of 60,000
existing records that I would need to append with the current price from
the
product table. Is there any easy way of doing that. It seems quite
daunting
to have to go through all of the records and update the price manually.

Thanks once again!
Slez

Just store the price in the Item Details table. This isn't redundant, as
you are storing the price *as it was at the time you selected it*. If you
look in the Northwind Sample database, they show how this is done in the
Order Details table and form, where the user selects the product, and the
current price is automatically stored along with the product in the Order
Details table.

I'm debating (with myself) whether or not to archive records in a
database
and would like to hear opinions on what others may have done, or what

[quoted text clipped - 26 lines]

a better way to lock these prices in once selected?
Any thoughts or suggestions are appreciated?

 

[Slez via AccessMonster.com]

OK, I gave this a try this morning and am getting the following message:

Syntax error (missing operator) in query expression 'ProductDescription =
Base.3 drawer'

I pasted in the following code from Northwind and adapted to my database'
field and table names:

Private Sub ProductDescription_AfterUpdate()
On Error GoTo Err_ProductDescription_AfterUpdate
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductDescription = " & Me!ProductDescription

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitCost", "Product", strFilter)

Exit_ProductDescription_AfterUpdate:
Exit Sub
Err_ProductDescription_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductDescription_AfterUpdate
End Sub

Where Northwind has a ProductID, I have ProductDescription, which is a text
field. Perhaps that has something to do with the error? In the error
message, "Base.3 drawer" happens to be the product I picked to try this out.
UnitPrice is the field I added to the table ItemDetail to store the value.
UnitCost is the value I'm pulling in from the table Product.

I'd really like to make this work and would greatly appreciate further
assistance!
Slez

Use an update query to apply the existing price to the existing records.

Thanks to all for the replies! Great input as always!

[quoted text clipped - 26 lines]

 

[Joan Wild]

You can update the price in one update query. The following would be the SQL
statement using Northwind:

UPDATE Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID SET [Order Details].UnitPrice = [Products]![UnitPrice];

So you need to substitute your field/table names. If I'm reading your post
right
UPDATE Product INNER JOIN [ItemDetail] ON Product.ProductDescription=
[ItemDetail].ProductDescription SET [ItemDetail].UnitPrice =
[Product]![UnitCost];

You'd first *backup* your mdb!! Open a new query and dismiss the add table
dialog. Go to View, SQL View and paste in the statement above, replacing
any text that you see in the SQL View window.

Switch to design view, as that will trigger any errors in names that I may
have. Run the query.

 

[Slez via AccessMonster.com]

I need to back up a bit. I'm perceiving that your response is directed
toward the update query used to update all existing records.

I need to just get this AfterUpdate event working in the form so that when I
select new products (records) to add to the subform, it updates the UnitPrice
field.

If I'm off base, I apologize...I've never done this specific operation before,
and I leave a little to be desired as far as my knowledge of code.
Thanks again!
Slez

You can update the price in one update query. The following would be the SQL
statement using Northwind:

UPDATE Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID SET [Order Details].UnitPrice = [Products]![UnitPrice];

So you need to substitute your field/table names. If I'm reading your post
right
UPDATE Product INNER JOIN [ItemDetail] ON Product.ProductDescription=
[ItemDetail].ProductDescription SET [ItemDetail].UnitPrice =
[Product]![UnitCost];

You'd first *backup* your mdb!! Open a new query and dismiss the add table
dialog. Go to View, SQL View and paste in the statement above, replacing
any text that you see in the SQL View window.

Switch to design view, as that will trigger any errors in names that I may
have. Run the query.

OK, I gave this a try this morning and am getting the following message:

[quoted text clipped - 32 lines]

assistance!
Slez

 

[Joan Wild]

Sorry about that, I thought you were working on updating your existing
records for the new field you added.

If you open Northwind and look at the Orders form, you'll see how they
accomplish this - look in the After Update property for the Product combobox
on the subform.

--
Joan Wild
Microsoft Access MVP

I need to back up a bit. I'm perceiving that your response is directed
toward the update query used to update all existing records.

I need to just get this AfterUpdate event working in the form so that when
I
select new products (records) to add to the subform, it updates the
UnitPrice
field.

If I'm off base, I apologize...I've never done this specific operation
before,
and I leave a little to be desired as far as my knowledge of code.
Thanks again!
Slez

You can update the price in one update query. The following would be the
SQL
statement using Northwind:

UPDATE Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID SET [Order Details].UnitPrice = [Products]![UnitPrice];

So you need to substitute your field/table names. If I'm reading your
post
right
UPDATE Product INNER JOIN [ItemDetail] ON Product.ProductDescription=
[ItemDetail].ProductDescription SET [ItemDetail].UnitPrice =
[Product]![UnitCost];

You'd first *backup* your mdb!! Open a new query and dismiss the add
table
dialog. Go to View, SQL View and paste in the statement above, replacing
any text that you see in the SQL View window.

Switch to design view, as that will trigger any errors in names that I may
have. Run the query.

OK, I gave this a try this morning and am getting the following message:

[quoted text clipped - 32 lines]

assistance!
Slez

 

[Slez via AccessMonster.com]

Please read this previous post...
I gave this a try this morning and am getting the following message:

Syntax error (missing operator) in query expression 'ProductDescription =
Base.3 drawer'

I pasted in the following code from Northwind and adapted to my database'
field and table names:

Private Sub ProductDescription_AfterUpdate()
On Error GoTo Err_ProductDescription_AfterUpdate
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductDescription = " & Me!ProductDescription

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitCost", "Product", strFilter)

Exit_ProductDescription_AfterUpdate:
Exit Sub
Err_ProductDescription_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductDescription_AfterUpdate
End Sub

Where Northwind has a ProductID, I have ProductDescription, which is a text
field. Perhaps that has something to do with the error? In the error
message, "Base.3 drawer" happens to be the product I picked to try this out.
UnitPrice is the field I added to the table ItemDetail to store the value.
UnitCost is the value I'm pulling in from the table Product.

I'd really like to make this work and would greatly appreciate further
assistance!
Slez

Sorry about that, I thought you were working on updating your existing
records for the new field you added.

If you open Northwind and look at the Orders form, you'll see how they
accomplish this - look in the After Update property for the Product combobox
on the subform.

I need to back up a bit. I'm perceiving that your response is directed
toward the update query used to update all existing records.

[quoted text clipped - 38 lines]

 

[Joan Wild]

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductDescription = " & Me!ProductDescription

strFilter = "ProductDescription = " & chr(34) & Me!ProductDescription &
chr(34)

 

[Slez via AccessMonster.com]

Thanks Joan!

strFilter = "ProductDescription = " & chr(34) & Me!ProductDescription &
chr(34)