Novice Needs Help!

[zSplash]

I'm so totally "not getting" this -- hope someone can help.

I have 4 tables (tCase, tDates, tMtgs, tNames). The first 3 tables are
directly related (ID to ID, one-to-one), and have referential integrity.
The tNames table has multiple names that go with the tCase table, so I've
related tNames.fk to tCase.ID in a one-to-many relationship. There are 1237
records in my database.

I created a query of all 4 tables (qAll). In this query, I joined all 4
tables with the first join property: "Only include rows where the joined
fields from both tables are equal." (This query has 3068 records, although
my database only contains 1237.)

So, then I make a form, based on qAll, and it, too, has 3068 records
(instead of the 1237 records of my database). I want to only have 1237
records in my form (until more are added), not 3068. What have I done?

TIA

 

[Guest]

Hi zSplash,

The answer is in your description:

"The tNames table has multiple names that go with the tCase table, so I've
related tNames.fk to tCase.ID in a one-to-many relationship."

This means that you will get a row in your result set from your query for
every one of the related names in your one-to-many relationship. Look at
your results and you will see that you have repeated data with only the data
from the tNames table different. This is correct, and how Access behaves.
What you then do with this data is up to you.

As an aside, having data in a one-to-one relationship is usually not
correct. Generally if it's one-to-one, the data should be in a single table.

Hope this helps.

Damian.

 

[Guest]

I have 4 tables (tCase, tDates, tMtgs, tNames). The first 3 tables are
directly related (ID to ID, one-to-one), and have referential integrity.
The tNames table has multiple names that go with the tCase table, so I've
related tNames.fk to tCase.ID in a one-to-many relationship. There are 1237
records in my database.

Correction: there are 1237 records in your table tNames. There are more
records in your other tables!

I created a query of all 4 tables (qAll). In this query, I joined all 4
tables with the first join property: "Only include rows where the joined
fields from both tables are equal." (This query has 3068 records, although
my database only contains 1237.)

A Query will return a composite record with data from all of the tables in
the query; if each record in tNames is linked to multiple records in some
other table or tables, you will get a composite record for every combination.

So, then I make a form, based on qAll, and it, too, has 3068 records
(instead of the 1237 records of my database). I want to only have 1237
records in my form (until more are added), not 3068. What have I done?

Misunderstood how Forms work.

You do not need to, nor should you, create a Grand Master Query containing
all your data, and then base a form on that. Instead, create a Form based on
the "one" side table, and use one or more Subforms based on the "many". For
instance, if each record in tNames refers to a person who can attend zero,
one or more Meetings, use a Form based on tNames with a subform based on
tMeetings, using the unique Names table ID as the master/child link field to
display only the meetings for that person, and to add new meetings which will
automatically be linked to that person.