I just started a new project and am trying to transition a large existing data set from excel to access. I have created two tables so far dealing with product details and another with category details. What I would like to do is link the category name in both table. My problem is that our products often are reorganized from category to category and I want all the records in the products table to update when I change the data in the category tables.
I already have a large set of data I have imported from excel and don't want to go line by line reentering the same information. Is there anyway to create a new look up in the product table that references the category name already there with the category name in the category table but going forward will just be a look up for the category table?
You don't have to go line by line to enter the same information. Simply use an update query with a criteria statement where "something" = "something else."
In addition, if the product categories are always re-organized, you can ignore the categories altogether, and just make an ID# for the products and use the ID# in all tables, querues, reports, etc., instead of bothering with the categories at all. That way, changing categories won't affect historical data. You can always have a separate category table (with a category ID#) and have a relationship between the two tables based on that, without materially affecting the products table or orders table.