If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
One-to-One Table keys
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 |
#3
|
|||
|
|||
One-to-One Table keys
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 |
#4
|
|||
|
|||
One-to-One Table keys
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 |
#5
|
|||
|
|||
One-to-One Table keys
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 |
#6
|
|||
|
|||
One-to-One Table keys
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 |
#7
|
|||
|
|||
One-to-One Table keys
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 . |
#8
|
|||
|
|||
One-to-One Table keys
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 . |
#9
|
|||
|
|||
One-to-One Table keys
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] |
#10
|
|||
|
|||
One-to-One Table keys
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] . |
Thread Tools | |
Display Modes | |
|
|