Adding more than 1 order for a customer

[Guest]

There is probably a very simple solution to what i'm trying to do but i'm
currently stuck.

I'm designing a database for our company that sells a single product.
Customers may wish to buy more than one of these products at a time and so
may order say 5 units from us at a time. Each of these units has a unique
Serail Number(i.e. SP100-000012). I have created a customers page that lists
all the information (address, name etc) of each customer. I now want to be
able to enter the number of units purchased in a box and then under that in a
seperate box input the seperate serial numbers of those units. At the moment
i can only input one serial number.

Does anyone know how i can solve this - simply put, i want enter the number
of units purchased and then list the serial numbers of each of those units on
the same page.

 

[Jeff Boyce]

It sounds like you are describing a one-to-many relationship ... one order
can include many unique serial numbers. Does your table structure reflect
this parent-child relationship?

If you are using a "many" table to hold the serial numbers associated with
an order, you could use a subform to display/collect all the serial numbers
associated with an order.

 

[Guest]

Jeff,

That sounds just what i'm after but i havn't yet created my table for the
serial numbers. How do i create the subform you described to input the serila
numbers? I want to be able to input them in a vertical style list and then be
able to search for them once the database is complete.

Thanks for your help,

Regards,

Phil

 

[Jeff Boyce]

Take a look in Access HELP on "subform".

Generically, first you build the table, something like:

tblSerialNumber
SerialNumber (if this truly is unique, you would use it as a Primary
Key)
OrderID (a foreign key from your tblOrder)

Then you'd build a form, with the table (or a query thereof) as the source.

Then you'd add the form as a subform to your main form, and link
(parent-child) on the OrderID fields.