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
|
|||
|
|||
More than two related tables seems to affect all relationships
I have 3 tables that I want to relate/link. When I link 2 of them, all's
seemingly good. When I introduce that 3rd table, it messes up the other two and doesn't work right itself. Is there some kind of validation procedure I'm supposed to do? For what it's worth, the linked fields are the same name, type, and size and constitute a one-to-one relationship. Help! |
#2
|
|||
|
|||
More than two related tables seems to affect all relationships
On Fri, 13 Jul 2007 11:34:03 -0700, Je''farc
wrote: I have 3 tables that I want to relate/link. When I link 2 of them, all's seemingly good. When I introduce that 3rd table, it messes up the other two and doesn't work right itself. Is there some kind of validation procedure I'm supposed to do? For what it's worth, the linked fields are the same name, type, and size and constitute a one-to-one relationship. Help! One to one relationships are QUITE uncommon. If you're not either Subclassing or using Table-driven field level security, or if neither term rings a bell, you probably should NOT be using one to one relationships. Since you chose not to post any information about the tables, or the "messes" that result, or your expectations, it's hard to give specific advice - but general advice would be that you probably DON'T want to do things this way. What is the nature of the tables? What real-life entity (thing, person or event) does each table represent? What do you expect from the relationship? All a relationship does is to PREVENT the addition of "orphan" records; if you're expecting new records to be automagically created, you'll be disappointed! John W. Vinson [MVP] |
#3
|
|||
|
|||
More than two related tables seems to affect all relationships
My challenge is to track (land use) development proposals for my
organization. To that end I'm keeping a variety of info, including application number (primary field), applicant name, address, application type, notes, outcome, etc. Make that Table1. Two other tables track specific pieces of info (conditions of approval and tree replacement requirements) for some/certain applications (Table2 and 3). Currently Table1 is kept in a table in a Word document of 117 pages (and growing) at close to 2 MB. It seems to me a terrible way to track this kind of info. I thought a db would be a more efficient way store this info and would allow me to tie Table1 to Tables2 and 3 based on the application number (i.e., I thought I could establish relationships between the three tables). At this time, any record in any of the tables would be unique; hence, the presumed one-to-one relationship. There are other types of development proposals that should be tracked, too, but they have their own unique ID (primary field). While the application numbers are my primary fields, one could make an argument that the parcel ID is a more universal primary since it could be applied to all applications (not just Table1) (i.e., all proposals have a location). The problem with that logic is that some applications utilize multiple parcels and parcels are divided over time. The bottom line is that I need a single db that tracks all this info and allows queries and reports. I need to develop some kind of tracking resource that allows the user to find, in one place, EVERYTHING there is to know about an application. It's entirely possible that I've not taken the right approach, but I don't know what else to do. "John W. Vinson" wrote: On Fri, 13 Jul 2007 11:34:03 -0700, Je''farc wrote: I have 3 tables that I want to relate/link. When I link 2 of them, all's seemingly good. When I introduce that 3rd table, it messes up the other two and doesn't work right itself. Is there some kind of validation procedure I'm supposed to do? For what it's worth, the linked fields are the same name, type, and size and constitute a one-to-one relationship. Help! One to one relationships are QUITE uncommon. If you're not either Subclassing or using Table-driven field level security, or if neither term rings a bell, you probably should NOT be using one to one relationships. Since you chose not to post any information about the tables, or the "messes" that result, or your expectations, it's hard to give specific advice - but general advice would be that you probably DON'T want to do things this way. What is the nature of the tables? What real-life entity (thing, person or event) does each table represent? What do you expect from the relationship? All a relationship does is to PREVENT the addition of "orphan" records; if you're expecting new records to be automagically created, you'll be disappointed! John W. Vinson [MVP] |
#4
|
|||
|
|||
More than two related tables seems to affect all relationships
On Mon, 16 Jul 2007 05:02:02 -0700, Je''farc
wrote: My challenge is to track (land use) development proposals for my organization. To that end I'm keeping a variety of info, including application number (primary field), applicant name, address, application type, notes, outcome, etc. Make that Table1. Two other tables track specific pieces of info (conditions of approval and tree replacement requirements) for some/certain applications (Table2 and 3). Currently Table1 is kept in a table in a Word document of 117 pages (and growing) at close to 2 MB. It seems to me a terrible way to track this kind of info. You'll get no argument in this Access group on that assertion! g I thought a db would be a more efficient way store this info and would allow me to tie Table1 to Tables2 and 3 based on the application number (i.e., I thought I could establish relationships between the three tables). At this time, any record in any of the tables would be unique; hence, the presumed one-to-one relationship. That suggests that you're saying that each proposal will include one and only one condition of approval, and one and only one tree replacement requirement. Is that accurate? Also... about the applicants. Can a proposal ever be submitted by TWO people? or even more? Perhaps that's another table. Or can one person ever submit two or more proposals? Perhaps that's a many to many relationship right there! There are other types of development proposals that should be tracked, too, but they have their own unique ID (primary field). While the application numbers are my primary fields, one could make an argument that the parcel ID is a more universal primary since it could be applied to all applications (not just Table1) (i.e., all proposals have a location). The problem with that logic is that some applications utilize multiple parcels and parcels are divided over time. So you have a many to many relationship from Parcels to Proposals, and even perhaps from parcels to parcels. The bottom line is that I need a single db that tracks all this info and allows queries and reports. I need to develop some kind of tracking resource that allows the user to find, in one place, EVERYTHING there is to know about an application. It's entirely possible that I've not taken the right approach, but I don't know what else to do. Access is certainly a better way than Word to do this, but I don't think you're yet taking full advantage of the relational capbilities of Access. John W. Vinson [MVP] |
#5
|
|||
|
|||
More than two related tables seems to affect all relationships
Many thanks for the feedback. It sounds like I may have a much bigger design
issue to resolve. "John W. Vinson" wrote: On Mon, 16 Jul 2007 05:02:02 -0700, Je''farc wrote: My challenge is to track (land use) development proposals for my organization. To that end I'm keeping a variety of info, including application number (primary field), applicant name, address, application type, notes, outcome, etc. Make that Table1. Two other tables track specific pieces of info (conditions of approval and tree replacement requirements) for some/certain applications (Table2 and 3). Currently Table1 is kept in a table in a Word document of 117 pages (and growing) at close to 2 MB. It seems to me a terrible way to track this kind of info. You'll get no argument in this Access group on that assertion! g I thought a db would be a more efficient way store this info and would allow me to tie Table1 to Tables2 and 3 based on the application number (i.e., I thought I could establish relationships between the three tables). At this time, any record in any of the tables would be unique; hence, the presumed one-to-one relationship. That suggests that you're saying that each proposal will include one and only one condition of approval, and one and only one tree replacement requirement. Is that accurate? Also... about the applicants. Can a proposal ever be submitted by TWO people? or even more? Perhaps that's another table. Or can one person ever submit two or more proposals? Perhaps that's a many to many relationship right there! There are other types of development proposals that should be tracked, too, but they have their own unique ID (primary field). While the application numbers are my primary fields, one could make an argument that the parcel ID is a more universal primary since it could be applied to all applications (not just Table1) (i.e., all proposals have a location). The problem with that logic is that some applications utilize multiple parcels and parcels are divided over time. So you have a many to many relationship from Parcels to Proposals, and even perhaps from parcels to parcels. The bottom line is that I need a single db that tracks all this info and allows queries and reports. I need to develop some kind of tracking resource that allows the user to find, in one place, EVERYTHING there is to know about an application. It's entirely possible that I've not taken the right approach, but I don't know what else to do. Access is certainly a better way than Word to do this, but I don't think you're yet taking full advantage of the relational capbilities of Access. John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|