Hi,
I am new to Access. I have four tables (customer, location, product, and order). There is a one to many relationship between customer and location, location and product, product and order, respectively. I have a primary key field in each of the four tables: CustomerID, LocationID, ProductID, and OrderID (number, autonumber).
I have built a form that allows a user to create and enter an order. The order form prompts the user to select a ProductID from a combobox. The ProductID is saved into the order table. Also, the LocationID and CustomerID are also saved into the order table.
The Location table contains ship to address information: ShipToAdd, ShipToCity, ShiptoState, ShiptoZip, etc). This information is NOT written into the Order Table when an order is entered. I made this decision in an effort to keep the database streamlined knowing that I could access this information with a simple query that pulled in the relevant information with the before mentioned LocationID. I hope this is in keeping with good database construction design!
From time to time, I will need to edit information in any one of the four tables (for example, a Location record's zip code for instance). Suppose I edit the zip code value (from 30058 to 30265) in a Location record (LocationID= 458). Suppose that this location was one of many location records for a Customer record (CutomerID = 7777). Suppose that I previously had entered 5 orders for CustomerID = 7777 to LocationID =458 BEFORE I changed the value of the zip code. Suppose that I entered 5 orders for CustomerID = 7777 to LocationID =458 AFTER I changed the value of the zip code.
Now - if I were to run a report that showed sales sorted by zip code - the first five orders would have initially been associated with zip code 30058. However, after the zip code change, the orders will be associated with zip code 30265 (even though they ACTUALLY occurred in zip code 30058). Here's where my problem arises (and understand that I will use the "zip code" field as a representative example - there are many more specific and technically non-descript fields that are actually at play here). I need to know the proper way to preserve the value of the zip code field (and the other 40 zip code like fields) for all orders.
You might suggest just nail the 40 zip-code-like fields into the order table. I might if thats what you recommend. You might suggest - just make a new Location record for CustomerID = 7777 that reflects the new zip. I could, however, that would require my users to go in and associate hundreds of customer specific ProductIDs to this new Location ID. Instead, I'd like to know if it would be wise to add and use a rev level field to the design of the Location table.
A rev level filed would require me to have duplicate LocationID numbers (one for each rev)....but I need to maintain each of the tables ID fields as primary keys. Any thoughts on how I can maintain primary key status for CustomerID, LocationID, ProductID, and OrderID AND maintain some type of rev history control on the same records?
Thanks
