Please explain relationships. I'm so dumb, I'm sorry.

[Guest]

I've tried to get to grips with MS Access many times in the past but usually
end up totally defeated by trying to understand how relationships work. I
have a go, I read the books but nothing seems to work as it should.

Now I'm trying to put together an Access database to handle items sold on
eBay. I've set up four tables focussing on different parts of the sale
(insertion, bid details, despatch, etc.) and I need to have two fields (item
and Auction ID) from the first table appear across all four tables, linking
the information therein. I created fields in all the tables for these two
important items and tried linking them to the first table.

In my naivity I thought that if I entered, say, an Auction ID into the first
table it would them automatically appear in the other three tables when I
opened them. Not the case - as I discovered.

What stupid thing am I doing wrong? Am I expecting too much? Have I missed
something blindingly obvious? Can anyone prevent Access from beating me to a
bloody pulp once again?

I'm desperate. I know Access is up to this job but if some kind soul can
point me in the right direction I'd be enternally grateful.

 

[tina]

understanding relationships is difficult for most people, so you're not
alone. but make sure you're not focusing solely on table relationships and
ignoring normalization principles - the two go together, in building a
correctly structured relational database. really all you can do is keep
reading and practicing, until the light bulb comes on. recommend you look at
the resources at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html,
beginning with the Database Design 101 and Starting Out links.

hth

 

[John Vinson]

In my naivity I thought that if I entered, say, an Auction ID into the first
table it would them automatically appear in the other three tables when I
opened them. Not the case - as I discovered.

A relationship PREVENTS you from entering invalid data. It does not
automagically create "dummy" placeholder records in the related tables
- and it shouldn't.

What you'll need to do is create the tables with the relationships,
and THEN use a Form with Subforms to enter the data. A Form would be
based on the "one" side table, and one or more Subforms on the form
for the "Many"; the Master Link Field and Child Link Field properties
of the Subform control will cause the AuctionID and ProductID to be
automatically filled in *when you create a record on the subform* (not
before).

Note that table datasheets are NOT designed for data entry, except in
really "quick & dirty" work. Forms are the way to go.

John W. Vinson[MVP]

 

[Terry Kreft]

When I was on the training course in which I was introduced to normalisation
and relationships, the trainer told me that normalisation was one of those
things that when you understood it you couldn't believe how "obvious" it
was. This turned out to be true.

You've got to understand normalisation first, this then makes it so that you
understand Candidate Keys, Primary Keys (PK) and Foreign Keys (FK) and what
their role is in relationships.

Once you understand that you'll understand why your question is wrong
headed.

Relationships do not cause keys to cascade down from the PK to the FK they
ensure that where an FK exists a corresponding PK exists.

In Access you can cause the correct FK to appear in a table by using a form
to enter details into the table. What you do is create a main/subform form,
you enter details into the form which is bound to the PK side of the
relationship and as soon as you start to enter details into the subform
(which is on the FK side of the relationship) Access will enter the key
values automatically for you.

 

[Rick B]

I've tried to get to grips with MS Access many times in the past but
usually
end up totally defeated by trying to understand how relationships work. I
have a go, I read the books but nothing seems to work as it should.

Now I'm trying to put together an Access database to handle items sold on
eBay. I've set up four tables focussing on different parts of the sale
(insertion, bid details, despatch, etc.) and I need to have two fields
(item
and Auction ID) from the first table appear across all four tables,
linking
the information therein. I created fields in all the tables for these two
important items and tried linking them to the first table.

In my naivity I thought that if I entered, say, an Auction ID into the
first
table it would them automatically appear in the other three tables when I
opened them. Not the case - as I discovered.

What stupid thing am I doing wrong? Am I expecting too much? Have I missed
something blindingly obvious? Can anyone prevent Access from beating me to
a
bloody pulp once again?

I'm desperate. I know Access is up to this job but if some kind soul can
point me in the right direction I'd be enternally grateful.

Thank you one an all for a) answering my questions so patiently and
helpfully, and b) making me feel a little less stupid that when I first
posted this cry for help.

I will persevere at trying to build my database and come to grips with the
relational aspects of MS Access.

Thanks again

 

[Jamie Collins]

Relationships do not cause keys to cascade down from the PK to the FK

They can be set to do so for DELETE and UPDATE actions but not for
INSERT actions that the OP seem to be expecting.

Jamie.

--

 

[Terry Kreft]

Thankf for the clarification Jamie, I was too focused when posting to
properly qualify that statement.