Inventory Database Help

[saultcollectibles]

Hello Everyone



I will say right off the bat I am new to Access but am a very tech minded person so any help well dumbed down a bit would be helpful you don't have to worry about completely going over my head lol.

Ok so here is my problem I am building an inventory database for my collectibles business. I am limited in the construction of the database so that I can easily use it with my opencart import function.

I currently have 8 tables that are identical in structure but are for different product lines
Example Action Figures has
Product ID (PK)
name
categories
sku
upc
location
quantity
model
manufacturer
image_name
requires_shipping
price

now my other tables for stuff like magic the gathering, beanie babies, etc etc the tables are identical to that, and than I created a union query to link them all together into a consolidated inventory called all products.

What I would like to do is have one entry form for my products that would allow me to chose which table to update, for example if I wanted to add a product to action figures I would chose that from a drop down on the form and than proceed to the data entry and it would place that item in the appropriate table.

Also any general advice on the database structure to make it easier to manage would be greatly appreciated as I said I am a novice at this so I am sure there is alot of room to improve

Thanks in advance

[pbaldy]

This was your first mistake: "I currently have 8 tables that are identical in structure but are for different product lines". Your union query is evidence of that, as you are trying to bring them together as they should already be. I'd have one table with an additional field for the product line (one table for this data; you would likely have other tables, including one to list the product lines). The concept is called "normalization". Here's one link on the subject:

Fundamentals of Relational Database Design -- r937.com

[saultcollectibles]

I considered the normalization issues and originally had them all together but I am dealing with very large amounts of products I found it easier to manage by using more than one table, but this may be due to my being new at the program and not that it is not a sound strategy. The only reason I am bringing them back together with the union query is because of the limitations of the import back into opencart. I had the categories originally in place so I could sort the data by product line.

[pbaldy]

I'm curious what "very large" means, but in any case I wouldn't sacrifice proper design. If it's really that many records, I'd switch to SQL Server or some other product for the data and use Access for the user interface. There are free versions.