Multiple inventory items in a single sales/purchase record...

[JuzMaintain]

New here, so if I posted in the wrong forum, please to forgive.

Here is the question in a nutshell: How do you have duplicate entries for a primary key made up of two foreign keys when the data type is an autonumber field in Access 2007? If possible, does it require the use of VBA code or can it be done with a query?

I'm trying to store multiple items into a single record on a junction table (tbl SalesOrderDetail) that uses the primary keys from two other tables (SalesOrderID from tbl Sales Orders and ProductID from tbl Inventory). I have the data type of SalesOrderID from tbl SalesOrders being generated as an autonumber field then being stored in the junction table as one half of the primary key in a number data type field, do-able so long as both fields have long integer field sizes, which they do.

I want to be able to generate the autonumber from SalesOrderID on tbl SalesOrders and have it remain static in tbl SalesOrderDetail in order to be able to store multiple items from the inventory onto a single sales record with that number in the junction table. My assumption is that because tbl SalesOrders is generating the autonumber and tbl SalesOrderDetail is storing it as a general, static number that it will NOT re-increment upon said storage to the junction table but merely store the already generated autonumber.

I also want to mirror this exact scenario on the purchasing side.

Basically, I'd like to tie this situation (both sales and purchasing) to corresponding data entry forms that, when opened, snap to the next autonumber in the sequence at the top of the form, along with the general details like date, customer ID, etc., and allow the user to enter the product details in a continuous datasheet subform below that that is linked to the junction table via either a query, VBA code or some other mojo I'm unaware of and populate tbl SalesOrderDetail consistently in the above stated manner.

Will this keep the records simple, neat and clean or will it create a clusterf*ck? Is there an easier way to tie multiple inventory items to a single sales/purchase record in Access 2007?


Thanks for any help...

SA
Los Angeles, CA

 

[Galaxiom]

I think you are a bit confused. You can't have duplicate primary keys whether they are autonumbered or otherwise. Primary key means unique value.

The junction table has a separate record for each inventory item on the Order.

SalesOrderDetail should be the RecordSource for a subformcontrol on the SalesOrders table. The Master and Child Link Fields Properties of that control will be the SalesOrderID (PK of the SalesOrder table).

The matching key will be automatically inserted into any record added to the subform.

 

[JuzMaintain]

Hi, Glaxiom...

Thank you profusely for responding. I'm certain I did not explain myself properly in my original post and I know the question I'm asking is one that has been asked here countless times before. Allow me to explain...

I'm attempting to construct a very simple Access 2007 db that will keep track of sales to customers and purchases from vendors of company promotional items (T-shirts, hats, etc.). I have seven tables currently. Three of them are:

tbl Customers
[CustomerID] PK, Auto #, Long Integer
[FirstName] Text
[LastName] Text
etc.

tbl Inventory
[ProductID] PK, Text (Necessity dictated I develop unique product #'s, so no Auto #)
[Description] Text
[Category] Text
etc.

tbl Vendors
[VendorID] PK, Text (Again, necessity)
[CompanyName] Text
[ContactName] Text
[Address] Text
etc.

The other four are tbl SalesOrders which pipes to tbl SalesOrderDetail and tbl PurchaseOrders which pipes to tbl PurchaseOrderDetail via their respective PKs. I think I've established a M:M relationship between tbl Inventory and tbl SalesOrders via tbl SalesOrderDetail, with the PKs from each former table acting as a composite key in tbl SalesOrderDetail. Same thing for the purchasing side.

My main objective is to be able to enter inventory items into a sales or purchase order form (2 separate mirror forms) in the manner it would be done manually on paper, with all the pertinent customer, order date, order number, transaction type info at the top of the form and a continuous datasheet subform at the bottom for multiple inventory items (product ID, description, unit price, qty, etc.) to be listed and then store all of that (sales or purchase) as a single record in the respective junction table. I realize they wouldn't act as single records per se, but rather several instances of the same sales/purchase order ID number, each paired with a unique, non-duplicatable product ID number to form unique records.

What is the easiest and cleanest way of doing this? I've been advised to just provide several product fields in the table for each sales/purchase record (Item 1, Item 2, etc.) but that seems inefficient and cumbersome and incorrect to me. In my db, one product can be included in many sales/purchase orders and one sales/purchase order can contain many products, requiring the use of junction tables to establish the M:M relationship, correct?

The other thing is this (and this was the original question I failed to articulate); if the tbl SalesOrders is generating an autonumber for each record in tbl SalesOrderDetail, how does Access 2007 know to essentially "freeze" that number in tbl SalesOrderDetail so that it corresponds to several inventory items on the same sales/purchase order except by having it land in a field in the junction table that's set to General Number data type? Do I need to rather base the subform on a query that pulls the matching records only and THEN populates the junction table?

I'm at my wits end with this. I know it can be done and done cleanly and correctly. That's what I'm looking for; the clean, correct and easiest way to accomplish this seemingly simple task. My buddy told me this would require some VBA and by some, he meant about 25 to 30 lines of code that may as well be written in Greek to me.

I've attached my Relationships window to give you some idea of what I'm dealing with. I know there are incorrect connections there.

Any help is GREATLY appreciated. Thanks.


SA
Los Angeles, CA