Can you link multiple records from one table to a single record from another table?

[Kipandrews06]

I'm making an inventory/order database and am trying to figure out how to structure it. So I'm storing products in a warehouse for companies that they use for tradeshows. I currently have it setup where I have a table with all the products. So what I am having trouble figuring out is how to link (if this is even possible) multiple records from one table to a single record from another. What I mean by that is, there's a continuous form that displays all the products. The customer can click on a checkbox next to the product they want to have sent out. So when they click on a button, I have a query that only shows the product records that are selected. When they proceed to the checkout form they can enter in all the shipping information, etc. So the shipping information is on a separate table. When they enter that order in, it would only be a single record. If they select 8 products to ship for example, I don't want 8 separate records for those products, I'd like them all to have the same Order ID # as the shipping information record. Is this possible to do?



My second question is, I'm also having trouble figuring out how to record the selected products. I can't leave products checked after an order so I have it set to change the selected products back to false so it clears out the cart after the order. I've tried an append query but it wouldn't do it because it said "it cannot add INSERT INTO function for Multi-valued fields". For inventory purposes, I need all those selected products for each order to record so that it will #1 have a record of it and #2 deduct the amount that they ordered from the amount of stock on hand.

So to sum it up in a nutshell in case I'm not explaining it very well. I have a continuous form that is the product list. It simply pulls from the products table. Each product record has a checkbox that they can select to have shipped out. Once they select all the products they need you click a button to take you to an item cart form. they verify the amounts and click a button to proceed to the shipping information. The shipping info form has a subform (that is the item cart so they can see the items they selected). They enter in all the shipping information and submit the order. That's ideally how I want it to work. But with the two issues I've run into I'm having a hard time achieving that and need some guidance. I want however many products they select on an order to be the same orderID number as the shipping information OrderID number and I need the selected products for the order to permanently save to a table before the system clears out the checked items. Because the selected products are run by a query that only shows the products that are selected so when the system unchecks all the products it clears the query records. The records I want saved to a table I need to remain there permanently and be linked to the shipping info OrderID.

Hopefully I'm explaining this properly. I'm not advanced in access yet, still figuring alot of stuff out and have hit a brick wall on this. Any help would be greatly appreaciated!

[June7]

Yes, multi-value fields can't be handled like regular fields. If you really want to use (and I advise not) then use a combobox bound to multi-value field to select products into Orders record. Don't use the 'checkbox' method of selecting from the Products table. Without some fancy programming, that method will cause multiple simultaneous users to conflict.

However, instead of multi-value field, i recommend a form/subform arrangement. Main form bound to Orders and subform bound to OrderDetails with a combobox to select product and each product will be a record.

[Kipandrews06]

I see what you mean about it causing a conflict with the checkbox method, I didn't think about that. I love the continuous form I have for the product list, I have an attachment field that shows a small thumbnail image for each product and it just looks really nice. If I took the checkbox out, then could I add a command button next to each product that when clicked would add it to the combo box in the subform like you mentioned?

[June7]

It wouldn't add it to combobox but could create a record in OrderDetails table and display in subform.

[orange]

Do you have a data model showing your tables and relationships?
I would second June's suggestion/advice to NOT use multivalued fields.
You may find this link helpful based on your comment

I'm also having trouble figuring out how to record the selected products

.

[Kipandrews06]

That would probably work. I'll play around with it and see how it does. Thanks for the suggestion!