Question quantity box in a multiple item lookup up column?

[stereotypethat]

I wish to create a database that holds information about my customers, including a list of what they bought and the quantity of each item that they bought. I have a separate table with a complete list of my products and used this as the reference for a lookup table in a column. I can set the lookup column to hold multiple values and I can get a single field to hold all the items that were purchased. But I can't figure out a good way to get the quantity of each item that was bought. Instead of a simple check box in the lookup, is there a way to get a quantity box to be filled in. I'd prefer not to need a column for each separate product on the order (as I'll eventually have as many extra columns on all the orders as I have products which would be like 200).

I'm new to this. Any helpful hints would be greatly appreciated.

THANKS!

 

[Scooterbug]

What you are describing is a classic Sales database. What you need is a table to hold data for only one product at a time. There is a templet for Order Entry. Since you are new to this, I think starting with that and taking a look might be benificial for you.

In a nutshell, however, what you will need to do is to have at least 4 tables. One for Customers, Products, Order Info and Order Details. Order Info will house the ID number of your customer at the minimum. You can also add things like payment type, Order date, etc. The Order Detail table will house the line items for the orders. By storing the Order ID with each entry in the order detail table, you can use that to tie them all together.

Hope I didn't make this too confusing...my teaching skills can be lackluster at times

 

[HAMMAMABUARQOUB]

let the lookup field be a combo box
use the after update event of the combo box to fill the data in the quantity box using the Column(indez) prop
Example:

Private Sub Combo1_AfterUpdate( )
Me.Qnty=Me.Combo1.Column(2)
End Sub

where Colmn(2) here is the 3rd Column (index starts from 0)