Database Problem:

[Sacred]

Hi,

I'm trying to build a database system for monitoring and recording the
following:

Customer details,
Items ordered,
Item sent,

I’ve set up the tables for the first two. Here’s my problem, I want to be
able to make a query that will allow my to query my customers, once queried
it would need to show both the customer information and their order
information (bearing in mind they may have more than one item).
Currently I’m using a barcode scanner and software so that each order can be
stamped with a barcode and as its sent it can be scanned (this would fill in
information into a query (parameter) and then give me results.
How do I set up the tables, relationships and queries so that I can run a
search for 1 customer and view their details and all of their orders at the
same time.
(I'm not some Access expert so basic explanations would be very helpful)

Regards,
Sacred
(Need further explanation just ask)

 

[DevalilaJohn]

The situation you describe is a pretty standard scenario. You basically have
four tables:

Customers Items (stuff they can order)
============= ==================
*Cust# *Item #
Name Description
etc. etc.

Orders Order Details
===== =========
*Order# *Order#
Customer# *Item#
Order Date Quantity
etc. Status
etc.
*key for uniqueness

With this setup, a customer has orders. Orders are made up of customers and
items. The status field in order detail will tell you if the item has been
shipped, is on back order, etc. You can also include a field under order to
indicate complete.

With this setup, you simply do a few joins and you have the data on each
item in each order for each customer. From there it is a matter of entering
the criteria for a given output.

If I understand your description correctly, you will have a code come up
telling you the order number. If that appears in a field on your screen, you
can use that data to identify the order to a query on the orders table. With
that knowledge you can join against customers, parts and order details (may
take a couple of queries). How you display the data from there (report,
form, subform) is up to you.

Hope this helps.