I am trying to implement a better inventory/orders system for our company. The main problem that I am having is that many of our products consist of more than on item - (most orders are system packages).
When we key in an item number, I need the system to populate each line item for all the individual items that make up the system on the invoice so one each can be pulled from inventory.
Can someone steer me in the right direction for researching my problem? Can it be done in Access???
Read up on relationships between tables. This is the heart of relational database theory.
Here's one approach:
Create a table with your packages listed. Something like tblPackages. Then create a table with all your products in it -- maybe tblProducts.
Every single product (or part) goes in tblProducts. Each should have a unique ID code (ProductID). I use autonumbers for these.
Then to "make" a package, add a record to your tblPackages table. Give it a unique ID (PackageID - again an autonumber field) and a name and anything unique to the package itself (not what makes it up). That means stuff like price, your business's ID for the package (Don't use the ProductID for your inventory ID codes unless you have a very, very good reason! It should just be for relating tables.)
Now you have a table with your products and a second with your packages.
It's time for a third - tblProductsInPackages. You need a third because you have a many-to-many relationship between these two tables (many products can be in one package, many packages can contain the same product).
All it needs is two fields: ProductID and PackageID. These should NOT be autonumbers since you'll enter the matching ID code from each of the other tables.
You have products called "cheese", "ham", "rye bread", "mayonnaise" and "lettuce." Their ProductIDs are 1, 2, 4, 8, and 10 perhaps. In that order.
You also have a package called "Ham and Cheese Sandwich." It's InventoryCode is "SandHC_001" and its PackageID is 152.
In your tblProductsInPackages, you'd have the following records:
...plus other ones interspersed for other packages.
If you query the tblProductsInPackages and link the other two tables, you can filter those results to show what you want.
For your ordering process, you'd enter either your InventoryID for either a product or a package. For our example, you may enter (or choose from a list box) "SandHC_001." Your database would look that up and find the correct PackageID and use it to pull up all the right "parts" or products.
So if it's a product whose inventory code you enter, only the single product would be added to your invoice. If it's a package, all the component products would show up. You'd need to do some coding to get this to work or design your form to specify Product or Package (easier for novices).
(BTW, this would mean you'd need Inventory Numbers in each table as well. Did I already mention that?)