Updating Pricing in MS Access 2010

[ekr]

Hello- I have a spread sheet of new prices for 1000+ products. I would like to load these prices into my Access database as a new field (newprice). How do I tell Access to take item number 12345 from the spreadsheet and add "$XXX" to the newprice field?

I assume this is an update or append query, but this is new territory for me. My criteria would be an item number match, but I am unsure how to do that. I.e. if 12345 matches on the spread sheet (or a related table) to the database, then populate the database field newprice with $100 (or whatever the new price is.)

Thanks in advance for the any info!

E. Rice

 

[SkipVought]

hi,

So am I to understand that you will have an existing [price] field and a new [new price] field?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ekr]

Hi Skip - Thanks for your reply. Yes that is correct-I would like to keep the existing price field for comparison and I have some linked reports.

 

[SkipVought]

Well what will happen when you have a new new new price field?

Adding fields is NOT a best and accpeted practice in database design for this kind of situation. You may consider having a transaction date associated with your price data for an item. Then you can make as many changes to price as might occur over time, and the structure of the table will be stable.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ekr]

Got it. What if I have a new piece of data that I would like to associate with my item numbers, such as an ID number for a materials list. E.g. Item 12345's materials list ID number is 678A. Can I use an update query for that?

Thanks! E. Rice

 

[lionelhill]

(1) Fleshing-out a stable-design approach to pricing, what I've tried is have a table of items, tblItems, (which has a column ItemID) and a table of Item prices, tblItemPrices. This price-table would contain date-columns ValidFrom and ValidTo, a column for ItemID, and the price. There is a one-to-many relation between tblItems and tblItemPrices.

The advantage of this is that you can change the price as often as you like, but all values "price" has ever had are retained in the database, and you can find the price that an item would have had, on any date, whenever you like.
The disadvantage is that unless you manage how people enter the data, you could end up with multiple prices valid on the same date, which makes for fun in deciding which to use when reporting.

(2) Dealing with a materials list etc.: you need to decide on a hierarchy of what "things" and "subthings" you have, and each level in the hierarchy will correspond to a table in the database. If your items are associated with materials, then you have two levels of hierarchy. If each Item can contain several Materials, but each Material can also be used in several Items, you will need an in-between table (MaterialsInItems) which bridges the two. Personally I find this one of the trickiest things in database-design: coming up with a hierarchy that is sufficiently detailed to deal with all future developments (adding a table later is a major design-change, and can create messiness if some fields in the original table really now belong in the new one...) - but a hierarchy that isn't over-complex (if you've split your data over 5 tables, most of which end up with 1:1 relationships, query-writing becomes unnecessarily long-winded).

 

[Turtleman10]

ekr,

Have you ever thought about putting it in a SQL database. In the long run it will be easer for you.

 

[ekr]

Thanks to everyone for the suggestions and tips! Cheers- E. Rice