how to link with primary key

[Guest]

I have a table (but you enter the details on a user-friendly form which all
works fine) for customers - names, organisation, some codes for what type of
org etc., and another separate table for orders, so every time a request
comes in, a new order is made out.
I want to be able to link the two tables somehow (so I can look at all the
orders from a particular customer, for instance), but when I've tried to do
it, it won't let me - it seems to only want to allow one order per customer,
but as we'll be getting re-orders, that's no good.
There is something called a "primary key", which you set on each table. I
think this enables you to link tables together, but it won't (as far as I can
see) allow you to link one record in one table to multiple records in the
other table.
PLEEEZE HELP

 

[Rick Brandt]

I have a table (but you enter the details on a user-friendly form
which all works fine) for customers - names, organisation, some codes
for what type of org etc., and another separate table for orders, so
every time a request comes in, a new order is made out.
I want to be able to link the two tables somehow (so I can look at
all the orders from a particular customer, for instance), but when
I've tried to do it, it won't let me - it seems to only want to allow
one order per customer, but as we'll be getting re-orders, that's no
good.
There is something called a "primary key", which you set on each
table. I think this enables you to link tables together, but it
won't (as far as I can see) allow you to link one record in one table
to multiple records in the other table.
PLEEEZE HELP

You need a a Primary Key in the Customers table [CustomerID].

You need a Primary Key in the Orders table [OrderNum]

You need a Primary Key in the OrderLineItems table [OrderNum]+[LineNum]

In addtion to [OrderNum] being part of the two field PK in the OrderLineItems
table it is also the Foreign Key that relates it to the Orders table. In this
way a main form for Orders can have a subform for LineItems and the [OrderNum]
field links the two. When you look at any particular order in the Orders main
form the LineItems subform will automatically display only the line-items for
that order.

Likewise your Orders table needs a Foreign Key [CustomerID] that relates it to
the Customers table. In this case the Foreign Key need not be part of the
Primary Key since (I assume) the Order Number will be unique across all
Customers. This Foreign Key in the Orders table would then allow you to see all
Orders for a particular customer by filtering on that matching field. You could
accomplish this with a main form for Customers that uses the Orders main form as
a subform, but that would not be typical.

 

[Larry Daugherty]

The Orders table should have its own primary key. The following
assumes that you are using Autonumbers as Primary keys: In a
different field of the Orders table there should be a field of type
Long Integer and usually with exactly the same name as the primary key
of the Customers table (CustomerID). That new field is referred to as
a Foreign Key. However, it will never be referred to in Access
documentation or any schema diagram as such. You are just expected to
know what foreign keys are and to be able to identify them in schema
diagrams. Design the rest of your Orders table.

Go to the Relationships window and show those two tables: Customers
and Orders. Click on CustomerID in tblCustomers and drag to
CustomerID in tblOrders. Access will draw a line between the two
fields. Doubleclick on the line and establish Referential Integrity
with Customers on the one side and Orders on the many side. Also turn
on Cascading Updates and Deletes. If these are new concepts to you,
you should learn about them in Help and maybe a good text book.

The most common paradigm for displaying the relationship and making it
useful is the Form/SubForm. There is ample help in Access on creating
that relationship. It will probably take a few tries to get
everything tweaked to your liking. Don't feel like an idiot if it
seems awkward at first. Also, don't be misled by the simple
directions in Access Help. The tools within Access make it all flow
together. Size the subform control so that several Order records can
be shown. When you first try it, only one proto record will show at
the outset. Once you put data in the first proto record, another will
open.

Once you have the form/subform laid out you can test how it works.
You must have your Customer form on a customer record to be able to
enter Order data. If there is no customer record selected you won't
be able to enter Order data.

If you have more issues, post back. You'll find sympathetic help here
and in microsoft.public.access.gettingstarted and
microsoft.public.access.tablesdesign and microsoft.public.access.forms
You'll find lots of aspiring Access developers addressing the same
issues you are in the 3 named groups.

HTH