I have 90 tables in the database (each representing a store)
Yikes, not what I'd recommend here.
Combined tables of data with a storeId to identify the store would be much more flexible. (IF they open a new store, you have to define a new table with relationships instead of just adding new records to existing tables.)
What kind of data have you entered in these 90 tables? Items available for sale at each store or something else?
given the reference to inventory data, I think you would be better off with this structure:
Either a portal to StoreInventory on a Stores layout or a list/table layout based on StoreInventory can be used to list all Products offered for sale at a given store.
Let me know if this works for you. If so, I can share a demo file that may give you some ideas on how to work with such a many to many relationship between stores and products.
Thank you so much! I'll make sure to try this solution.
Each table has the following columns (in CAPS) the SKU number, the MODEL, INVENTORY, STATUS and sales for JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER and DECEMBER. I get a regular report of the store month by month that goes into an excel file, that excel file is the one that turns into a table in FM through a recurring import. I am aware this is complicated but like I said I am experimenting (I do not have a computer degree so this is pretty hard for me).
Ok, so from what I understand is that I should merge the tables? I'm guessing this way could be simpler, it would mean that there would be a lot of repeated SKU's would it not? I do have a Store ID for each, so maybe this way could work better. I'll let you know when its done.
it would mean that there would be a lot of repeated SKU's would it not?
no repeated SKUs, they would be stored in the products table. The StoreInventory table would link a given store to a given product in the products table. And the SKU, while unique may not be the best choice for linking Product records to other tables. SKUs are often externally generated values that contain embedded info that a knowledgeable person can decode in order to learn more about the product. While this has its uses, this leaves the format and content of a SKU subject to possible revision and you want to use identifiers (called primary keys) that aren't subject to change. I suggest using an internally generated serial number in the products table for your relationship links to other tables. By all means define a field for the SKU in products so you can use it to find and sort products records, just don't use it in relationships.
You might also want to consider using one record for each month's sales totals for each product for that store. It can be more work to set up a layout with your columns that way, but can make other reports much easier to set up.
Here's the demo I mentioned: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html