Trouble with creating relationship

[Richard Harison]

I really am a better Access programmer than will come across here. I have a
relatively simple problem which for some reason is really stone walling me!
There are 3 tables, a form, and a report involved. So here goes:
The first table is names of volunteers. It is related 1 to 1 (ref. integrity
enforced) to a data table containing phone, address, county etc. The 3rd table
is a record of schedules. Since, besides its own fields, the report requires
the county info from the data table. This schedules table was created before
there was a need for the counties field. I realized I now had to relate the
schedule table to the foreign key in the data table. I created a field in the
schedule table, long integer, indexed duplicates OK, and hand input the correct
key # assigned to the volunteer (and thus also the foreign key for the data
table.) Something like this:

Volunteers Data Schedule
primary key (Vol_ID)----foreign key (Vol_ID) ----foreign key (Vol_ID).
The report runs fine using a query. But two other things happened. I cannot
relate the data and schedule tables using ref. integrity, which might also
explain why the form (which controls the schedules table) will not fill in the
key field (Vol_ID) in the table.
As I said before there is probably a very simple answer, which I am missing.
That ever happen to you!??

 

[Nikos Yannacopoulos]

Richard,

Why would you have two tables for volunteers, with a 1-to-1
relationship? All of that data should be in one table (unless you need
more than 255 fields per volunteer, which I doubt)... generally, in a
well normalized database there should be no 1-to-1's, plus it will make
your life with the queries, forms, reports etc. much easier.

HTH,
Nikos

 

[Richard Harison]

Thanks Nikos!
I agree--1 table would have been sufficient. My reasoning at the time was that
I was using the firstname/lastname table for other purposes as well. The
database has 6 instances where the firstname/lastname table is the control
source for drop down combo boxes. Additionally, the volunteers portion of the
database is relatively static, and is not the source of reports, etc.
My real problem is the need to pull one field from the vol_data table
[WhichCounty] and make it accessible to a report. I added a long integer field
to the schedules table,dutifully filled in all 281 numbers to link it with the
foreign key of the [Vol_Data] table (which is in turned linked to the Primary
Key of the [Vol_Names] table.
A query generates a report flawlessly. (1 volunteer can have many schedules)
But I cannot get the schedules FORM to add any key # to the new long integer
field I created in the schedules table. I suspect the problem is that Access
won't let me enforce referential integrity, but I don't know why, since I filled
in all the correct numbers for the existing data.

 

[Nikos Yannacopoulos]

I agree--1 table would have been sufficient. My reasoning at the time was that
I was using the firstname/lastname table for other purposes as well. The
database has 6 instances where the firstname/lastname table is the control
source for drop down combo boxes. Additionally, the volunteers portion of the
database is relatively static, and is not the source of reports, etc.

Neither constitutes a valid reason why you should brake up the table...
I still insist you should only have one.

My real problem is the need to pull one field from the vol_data table
[WhichCounty] and make it accessible to a report. I added a long integer field
to the schedules table,dutifully filled in all 281 numbers to link it with the
foreign key of the [Vol_Data] table (which is in turned linked to the Primary
Key of the [Vol_Names] table.
A query generates a report flawlessly. (1 volunteer can have many schedules)
But I cannot get the schedules FORM to add any key # to the new long integer
field I created in the schedules table. I suspect the problem is that Access
won't let me enforce referential integrity, but I don't know why, since I filled
in all the correct numbers for the existing data.

Again, your problems are created from the redundant table in your
schema. Normally, a form/subform link reflects a one-to-many
relationship between two tables; the problem is you have two tables on
the "one" side (form), so the linking gets tricky! You should probably
still be able to get it to work (by linking the two 1-to-1 tables
together in a query and using that as the main form's recordsource), but
it's still not the right thing to do. Use a trick to "remedy" a problem
stemming form a sub-optimal design, next thing you know another problem
comes up that requires another trick, and soon enough you realize it's
time you went back and fixed your data design, only you've spent too
much time and energy "patching up" until you get there... believe me, I
learned this the hard way (and I'm sure I'm not the only one).

HTH,
Nikos

 

[Richard Harison]

Is there a quick way to combine the two one-to-one tables? I tried the analyze
function, but it saw no problem with the linked tables. Thanks!

--
All the Best
Richard Harison

I agree--1 table would have been sufficient. My reasoning at the time was
that I was using the firstname/lastname table for other purposes as well.
The database has 6 instances where the firstname/lastname table is the
control source for drop down combo boxes. Additionally, the volunteers
portion of the database is relatively static, and is not the source of
reports, etc.

Neither constitutes a valid reason why you should brake up the table... I
still insist you should only have one.

My real problem is the need to pull one field from the vol_data table
[WhichCounty] and make it accessible to a report. I added a long integer
field to the schedules table,dutifully filled in all 281 numbers to link it
with the foreign key of the [Vol_Data] table (which is in turned linked to
the Primary Key of the [Vol_Names] table.
A query generates a report flawlessly. (1 volunteer can have many schedules)
But I cannot get the schedules FORM to add any key # to the new long integer
field I created in the schedules table. I suspect the problem is that
Access won't let me enforce referential integrity, but I don't know why,
since I filled in all the correct numbers for the existing data.

Again, your problems are created from the redundant table in your schema.
Normally, a form/subform link reflects a one-to-many relationship between two
tables; the problem is you have two tables on the "one" side (form), so the
linking gets tricky! You should probably still be able to get it to work (by
linking the two 1-to-1 tables together in a query and using that as the main
form's recordsource), but it's still not the right thing to do. Use a trick to
"remedy" a problem stemming form a sub-optimal design, next thing you know
another problem comes up that requires another trick, and soon enough you
realize it's time you went back and fixed your data design, only you've spent
too much time and energy "patching up" until you get there... believe me, I
learned this the hard way (and I'm sure I'm not the only one).

HTH,
Nikos

 

[Nikos Yannacopoulos]

Make a simple query combining the two tables, and returning all fields
(PK field just once); change the query type to make-table, and run it to
make the new table.

The above assumes there is a record for each PK value in both tables,
otherwise you need to use an outer join (if there are records in the
volunteers table for which no record exists in the volunteer data table,
if I understand your current design correctly) to make sure you don't
miss out any records.

HTH,
Nikos

 

[Richard Harison]

Much thanks!
I had considered the Make Table Query. Just thought there might be a magic
button in the Tools menu somewhere. I'll get on it right away!

 

[Richard Harison]

Hello Nikos!
Amalgamation of the two tables went flawlessly. As it turns out, I figured out
a way to create the report and add data without any table linking at all. In
the form, I set the recordsource to an SQL statement I created. I did this
because the actual link was not a PK, but the volunteer's name. Problem was
that in one table there was a field that contained the entire name, but the
other table had the name in 2 fields (Lastname, Firstname). I set the SQL to
concatenate the 2 names and went from there. Works Great! Thanks again!

 

[Nikos Yannacopoulos]

Richard,

If you're still out there:

As it turns out, I figured out
a way to create the report and add data without any table linking at all. In
the form, I set the recordsource to an SQL statement I created.

You most likely did link the two tables in the SQL statement... you may
not be using a saved query (one that you can see / access through the
database window), but the result is the same.

I did this
because the actual link was not a PK, but the volunteer's name. Problem was
that in one table there was a field that contained the entire name, but the
other table had the name in 2 fields (Lastname, Firstname). I set the SQL to
concatenate the 2 names and went from there.

Concatenation would have worked just as fine in a saved query, but this
is not the issue here. Using people's name fields as a key (either as a
PK in a table, or as a PK-FK in joining tables) is strongly advised
against, for a number of reasons. For example, how many John Smiths are
there in Bell South's directory? How do you differentiate if you have
even as few as two in your database? Also, how can you guarantee Zlatko
Wojciechowski will always get spelled right?
A PK has to guarantee uniqueness. A very good choice of PK for a people
table is the SSN if you are in the US, provided you are allowed to use
it. If not, your next best choice is an autonumber field.

HTH,
Nikos

 

[Richard Harison]

Absolutely agreed! Fortunately this particular name list is short enough that
the chance of exact name duplication is slim enough to be non existent.
Additionally, A VB event could message "you already have a John Smith, please
add middle initial or other unique identifier." SSN is not an option. And I got
around the spelling issue by forcing the user to select the volunteer from a
combo box which contains the same concatenation. [last]&", "&[first].
Additionally the problem is this: We are not really dealing with a form/subform
one-to-many situation--where an autonumber would be the ideal choice. True, one
volunteer would have many shifts, but they way the form is filled out at present
is with the date as the leading field, not the volunteer, and I am dealing with
a lot of pre-populated tables. So there is no subform. It's working great and
I will of course consider revising the whole thing properly if I can figure out
an automated way to import the pre-existing data into the new format. Thanks

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George