Price Tracking Database Design. Please Help!!

[Guest]

Hello to all.

I need to design a database for tracking supplier's prices. I order items
on an as-needed basis but there are humdreds of them. Basically I would be
working with a part number, a description, a date, a price, and an invoice
number. I would like to enter all past and incoming invoices by typing in
the part number off the invoice (this part number would occur many times
throughout many invoices). By entering this part number, I would like a box
to come up that would have the description (which will remain the same for
each individual part number). At this point I would enter the invoice
number, the date, and the price. I would repeat this process each time an
order comes in. When I need to check the price history of a part number, I
would like to be able to punch in the part number and have a list of all the
previous dates and prices for that item. Again, my goal is to track prices
increases over time for different part numbers.

Could someone suggest a plan for this idea? Does this even make sense? Is
Access the right program to use?

Thank you very much for taking the time to read this. Any help would be
greatly appreciated.

Mike

 

[Guest]

Try this table structure:

Part_Catalog_tab (Part_No, Description, ...other part fields like weight,
unit of measure, etc.) PK: Part_No
--Optional: Part_Catalog_Price_tab(Part_No, Dated, Unit_Price, Invoice_No)
Invoice_tab(Invoice_No, Dated, Supplier_Id, ...other invoice fields)
PK:Invoice_No
Invoice_Line_tab(Invoice_No, Line_No, Part_No, Quantity, Unit_Price)
PK:Invoice_No,Line_No

PK=Primary Key

Read about relationships,forms, and queries in access help for more details
to make your application, these topics are extensive, access can help create
the tool that you are describing,