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

 

[Damian S]

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.

 

[John Vinson]

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.

 

[zSplash]

Thank you, Damian, for helping. When I first posted to this NG, I had my
database in a single table, as you suggest, since everything's in a
one-to-one relationship. I was told it was poorly designed, since I had
several names for each record (i.e., OriginatorFirst, OriginatorLast,
DeveloperFirst, DeveloperLast, etc.). So, I took out the names table and
got the one-to-many relationship (between the mainTable and the namesTable).
Now are you saying I should not have done that, but that it was OK to leave
the names within the main table?

The only reason I have 3028 "records" is because there (currently) are 3028
names that relate to the underlying 1237 records, if that makes any sense...

TIA

 

[zSplash]

Thanks, John, for the help. There are 1237 records in 3 of my tables; in my
namesTable, there are 3068 names to go with my other (one-to-one) tables.
(There are ONLY 1237 records in my other tables!)

My form has a subform (which shows the namesTable data -- the one-to-many
table) and the main form is based on the tables with 1237 records (the
one-to-one tables).

I don't understand much, but wonder if I have misdefined the "join" values
of the query upon which my form is based.

TIA