One-to-One Table keys
in [Tables]
|
From: oldblindpew on 4 Feb 2010 18:21 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
From: Jeff Boyce on 4 Feb 2010 19:03 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" <oldblindpew(a)discussions.microsoft.com> wrote in message news:1309F98A-3606-4F89-BD9F-27481DD7DFA0(a)microsoft.com... > 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
From: Jerry Whittle on 5 Feb 2010 10:04 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
From: oldblindpew on 5 Feb 2010 14:10 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
From: Jeff Boyce on 5 Feb 2010 14:34
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" <oldblindpew(a)discussions.microsoft.com> wrote in message news:E1C253EA-405F-49C3-8E0B-FDEBE21B6602(a)microsoft.com... > 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 |