Relationships and queries

[kena]

I have 5 tables and each has a common field "Unique ID"
with which I want to link them.
I use a query based on these 5 tables to provide data for
the input form.
I have set the relationship to "one to many" from one
form "Personal Details" to each of the others. "Personal
Details" has as the primary key "Unique ID".

The query will not provide any data, nor can I add any. I
think it is something I have done with the relationships,
but can't figure it out. I started this exercise, because
I wanted to update the "Unique Id field in each record
when it changed or was entered in the "Personal Details"
table via the from.

I need help in understanding how to set up the
relationships and updating the Unique Id field in each
table via the query.
Thanks for any help
kena

 

[Steve Schapel]

Kena,

Assuming you set it to enforce referential integrity, when you defined
your Relationships, you will not be able to enter a record in one of the
"many" side tables without a record with a corresponding Unique ID in
the Personal Details table. That is *all* the Relationships will do.
Relationships do not "do" anything at all. They are not involved at all
in data entry, and they will not "update the Unique ID field in each
table via the query"... this is simply not what it is all about. I
can't answer more specifically without knowing more details of what
these other 4 tables are, and what you are trying to achieve. But one
standard way for one-to-many relationships to be managed is via a main
form/subform structure, with the subform's Link Master Fields and Link
Child Fields properties set appropriately. That way, a new record
entered via the subform will automatically be allocated the Unique ID of
the current Personal Details record on the main form. I will also say
thatif you have a query with 5 tables, as you have described, you will
only get a record returned if there are already matching records in all
4 of the "many-side" tables corresponding with the Unique ID of any
given Personal Details record. Unless, that is, you use Left Joins in
your query. In other words, if you are using the query design view,
right click on the join line between the tables, select Properties, and
then select the option that says something like "all Personal Details
records and only matching records from the other table".