One-to-One Table keys

Table1 is the parent table, Table2 is the child table. Table3 is joined to
Table2 in a one-to-one relation. The purpose of Table3 is for additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2 must also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary in view
of the one-to-one relationship. The records in 3 are like extensions of the
records in 2, and could appear to somehow inherit the relationship back to
the parent table.

Thanks,
OldBlindPew

This may have already been covered in an earlier thread...

If the only reason to use Table3 is to get more fields connected to the
"child" record in Table2, there's a good chance your table structure is not
well-normalized. So what, you ask? So, Access' features and functions are
optimized for well-normalized data, and both you and Access will have to
work much harder to overcome 'sheet data (large number of fields).

If you'll post an example with a bit of data (feel free to munge it if it is
proprietary or private), folks here might get a better picture of what you
have and what you are trying to do.

The Form/Subform construction works well when what you have is a one-to-many
relationship (parent table to child table).

It doesn't work so well if your tables are related one-to-one (your tables 2
& 3).

Hmmm? If you need a way to see data you have on Table3, couldn't you use a
query to join Table 2 and Table 3, then display the fields from both in your
subform?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
Table1 is the parent table, Table2 is the child table. Table3 is joined
to
Table2 in a one-to-one relation. The purpose of Table3 is for additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2 must also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary in
view
of the one-to-one relationship. The records in 3 are like extensions of
the
records in 2, and could appear to somehow inherit the relationship back to
the parent table.

Thanks,
OldBlindPew

If Table3 is only to hold a single record for each record in Table2 and its
only purpose is hold data that only applies to a subset of the records in
Table2, I would much rather keep all the data in Table2 and leave the
unneeded fields null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"oldblindpew" wrote:

Table1 is the parent table, Table2 is the child table. Table3 is joined to
Table2 in a one-to-one relation. The purpose of Table3 is for additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2 must also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary in view
of the one-to-one relationship. The records in 3 are like extensions of the
records in 2, and could appear to somehow inherit the relationship back to
the parent table.

Thanks,
OldBlindPew

The purpose of Table3 is to hold information applicable to only SOME of the
records in Table2. I didn't say there would be a record in 3 for every
record in 2. The whole point of having a separate Table3 is to have matching
records only as needed, to avoid storing lots of empty values.
--Pew

"Jerry Whittle" wrote:

If Table3 is only to hold a single record for each record in Table2 and its
only purpose is hold data that only applies to a subset of the records in
Table2, I would much rather keep all the data in Table2 and leave the
unneeded fields null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"oldblindpew" wrote:

Table1 is the parent table, Table2 is the child table. Table3 is joined to
Table2 in a one-to-one relation. The purpose of Table3 is for additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2 must also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary in view
of the one-to-one relationship. The records in 3 are like extensions of the
records in 2, and could appear to somehow inherit the relationship back to
the parent table.

Thanks,
OldBlindPew

That makes sense.

If you create a query that joins Table2 and Table3, and make it
"directional" (all the records from T2 and ANY that match in T3), you could
use the query as a source for a form.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
The purpose of Table3 is to hold information applicable to only SOME of
the
records in Table2. I didn't say there would be a record in 3 for every
record in 2. The whole point of having a separate Table3 is to have
matching
records only as needed, to avoid storing lots of empty values.
--Pew

"Jerry Whittle" wrote:

If Table3 is only to hold a single record for each record in Table2 and
its
only purpose is hold data that only applies to a subset of the records in
Table2, I would much rather keep all the data in Table2 and leave the
unneeded fields null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"oldblindpew" wrote:

Table1 is the parent table, Table2 is the child table. Table3 is
joined to
Table2 in a one-to-one relation. The purpose of Table3 is for
additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail
info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2 must
also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary in
view
of the one-to-one relationship. The records in 3 are like extensions
of the
records in 2, and could appear to somehow inherit the relationship back
to
the parent table.

Thanks,
OldBlindPew

You would be better off storing it all in Table2 then. Access doesn't waste
space. If you have an empty text field in a record, no space is used.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

The purpose of Table3 is to hold information applicable to only SOME of the
records in Table2. I didn't say there would be a record in 3 for every
record in 2. The whole point of having a separate Table3 is to have matching
records only as needed, to avoid storing lots of empty values.
--Pew

"Jerry Whittle" wrote:

If Table3 is only to hold a single record for each record in Table2 and its
only purpose is hold data that only applies to a subset of the records in
Table2, I would much rather keep all the data in Table2 and leave the
unneeded fields null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"oldblindpew" wrote:

Table1 is the parent table, Table2 is the child table. Table3 is joined to
Table2 in a one-to-one relation. The purpose of Table3 is for additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2 must also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary in view
of the one-to-one relationship. The records in 3 are like extensions of the
records in 2, and could appear to somehow inherit the relationship back to
the parent table.

Thanks,
OldBlindPew

So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is
saying no, put Table3 fields inside Table2?

Pew

"Jeff Boyce" wrote:

That makes sense.

If you create a query that joins Table2 and Table3, and make it
"directional" (all the records from T2 and ANY that match in T3), you could
use the query as a source for a form.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
The purpose of Table3 is to hold information applicable to only SOME of
the
records in Table2. I didn't say there would be a record in 3 for every
record in 2. The whole point of having a separate Table3 is to have
matching
records only as needed, to avoid storing lots of empty values.
--Pew

"Jerry Whittle" wrote:

If Table3 is only to hold a single record for each record in Table2 and
its
only purpose is hold data that only applies to a subset of the records in
Table2, I would much rather keep all the data in Table2 and leave the
unneeded fields null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"oldblindpew" wrote:

Table1 is the parent table, Table2 is the child table. Table3 is
joined to
Table2 in a one-to-one relation. The purpose of Table3 is for
additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail
info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2 must
also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary in
view
of the one-to-one relationship. The records in 3 are like extensions
of the
records in 2, and could appear to somehow inherit the relationship back
to
the parent table.

Thanks,
OldBlindPew


.

Nope, I'm telling how you could accomplish what you said you wanted to with
the table structure you gave us.

If you want to modify your table structure per Jerry's suggestions, you'd
need to modify your query.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is
saying no, put Table3 fields inside Table2?

Pew

"Jeff Boyce" wrote:

That makes sense.

If you create a query that joins Table2 and Table3, and make it
"directional" (all the records from T2 and ANY that match in T3), you
could
use the query as a source for a form.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" wrote in message
...
The purpose of Table3 is to hold information applicable to only SOME of
the
records in Table2. I didn't say there would be a record in 3 for every
record in 2. The whole point of having a separate Table3 is to have
matching
records only as needed, to avoid storing lots of empty values.
--Pew

"Jerry Whittle" wrote:

If Table3 is only to hold a single record for each record in Table2
and
its
only purpose is hold data that only applies to a subset of the records
in
Table2, I would much rather keep all the data in Table2 and leave the
unneeded fields null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"oldblindpew" wrote:

Table1 is the parent table, Table2 is the child table. Table3 is
joined to
Table2 in a one-to-one relation. The purpose of Table3 is for
additional
fields that only apply to a subset of the records in Table2.

A form/subform is used to view Table1 header info with Table2 detail
info.
I think I want a similar form/subform to view Table1/Table3.

Q: Does this mean the same key field used to relate Tables 1 & 2
must
also
be present in Table3, making Table3 another child to Table1?

It seems this must be so; on the other hand it might be unnecessary
in
view
of the one-to-one relationship. The records in 3 are like
extensions
of the
records in 2, and could appear to somehow inherit the relationship
back
to
the parent table.

Thanks,
OldBlindPew


.

On Fri, 5 Feb 2010 15:06:04 -0800, oldblindpew
wrote:

So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is
saying no, put Table3 fields inside Table2?

This is at least a 25-year old argument about how to handle Subclassing. It
can be done either way - with one table with a lot of NULL fields, or with two
tables in a directional one-to-one relationship.

Either one works.

Both have advantages, and both have disadvantages - the one-table approach, as
you rightly say, has a lot of NULL fields. Fortunately in Access'
implementation they don't take up any space in your database, but they do make
the table less elegant.

The two-table approach makes your queries more complicated whenever you need
to include the subclass table; if it's rarely needed, this may not be a big
deal. It can also impact performace since Access must pull in both tables and
the indexes on the primary and foreign key fields.

Take your pick!
--

John W. Vinson [MVP]

Thanks, John for your plainspoken reply.

It is inevitable, I suppose, that having so many ways to skin the cat
complicates the instruction manual.

Based on the doctrine that Records are Cheap but Fields are Expensive, I had
surmised that having more tables was preferable for both programming effort
and performance, despite all the complications necessary to relate them. Now
it sounds like the opposite is true, at least in this particular case.

Am I correct in saying that the answer to my original question is that there
is no need to repeat in Table 3 the foreign keys from Table2, since I will be
joining these tables anyway by means of a query?

Also, this discussion prompts me to ask, is it better in general to specify
the whole record as a RowSource, even though I don't need all the fields, or
should one specify only the fields needed? In other words, is it more
trouble for Access to pick out the specific fields than it is just to grab
the whole table?

Thanks
Pew

"John W. Vinson" wrote:

On Fri, 5 Feb 2010 15:06:04 -0800, oldblindpew
wrote:

So... Is Jeff saying yes, it makes sense to have Table3, while Jerry is
saying no, put Table3 fields inside Table2?

This is at least a 25-year old argument about how to handle Subclassing. It
can be done either way - with one table with a lot of NULL fields, or with two
tables in a directional one-to-one relationship.

Either one works.

Both have advantages, and both have disadvantages - the one-table approach, as
you rightly say, has a lot of NULL fields. Fortunately in Access'
implementation they don't take up any space in your database, but they do make
the table less elegant.

The two-table approach makes your queries more complicated whenever you need
to include the subclass table; if it's rarely needed, this may not be a big
deal. It can also impact performace since Access must pull in both tables and
the indexes on the primary and foreign key fields.

Take your pick!
--

John W. Vinson [MVP]
.