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
|
|||
|
|||
Relationship feature/bug/accident
I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of course, maybe I'm just being dumb. The basic structure tracks professional literature for a department's internal library. There are three types of publications collected - books, magazines and journals. There is one primary table for all the data which is common to each type (archive number, physical location, date acquired...) and a separate sub-table for each of the three types, containing data which is unique to the type (books have an author, magazines do not...) Originally I had a primary key in the main table using an autonumber field and primary keys in the subtables using a compound key of consisting of the autonumber from the main table as a standard doubleprecision number and another autonumber field in the subtable. Access linked these up in three one-to-many relationships and everything looked great. Recently I realized that the autonumber fields in the subtables don't really do anything for me, since each subtable can have either one or no records for each record in the main table. That is, each piece of literature has exactly one entry in the main table and exactly one entry in exactly one of the three subtables. So carrying over the autonumber field from the main table is enough to uniquely identify the record. With that in mind, I removed the autonumber field from the subtables, leaving only the copy of the autonumber from the main table as the primary key. The relationship window still showed the links as one-to-many and everything I've written to date still worked. (Sorry to be so long-winded, but I want to make sure I explain this properly.) So here's the issue: When I delete such a relationship line, I can't put it back as anything other than a one-to-one relationship. I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry. But I'm leery of anything that smacks of witchcraft and a relationship that can't be created without 'fooling' the table with a false structure which is then removed again doesn't strike me as good practice. Is this correct behavior for the relationship window or have I discovered something weird? Can I set up a one-to-zero-or-one relationship somehow? I assumed that my form would simply create one record on the many side of the join and not address the fact that the structure could allow more than one. Or have I made a fundamental mistake in the design somewhere? Pete P.S. I'll be out of the office until the weekend, at least. This e-mail address is fake to keep spammers and their auto-harvesters out of my hair. If you need to get in touch personally, I am 'pdanes' and I use Yahoo mail. But please use the newsgroups whenever possible, so that all may benefit from the exchange of ideas. |
#2
|
|||
|
|||
Pete,
These are one-to-one relationships. Simple as that. What you have done is entirely appropriate. I don't quite understand your comment "I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry." The fact that two of the three "subtables" will not contain a record corresponding with any given main table record, is really immaterial. -- Steve Schapel, Microsoft Access MVP Peter Danes wrote: I have a strange (to me, anyway) relationship setup that I haven't found addressed in any of the forums, online help or any of my manuals. Of course, maybe I'm just being dumb. The basic structure tracks professional literature for a department's internal library. There are three types of publications collected - books, magazines and journals. There is one primary table for all the data which is common to each type (archive number, physical location, date acquired...) and a separate sub-table for each of the three types, containing data which is unique to the type (books have an author, magazines do not...) Originally I had a primary key in the main table using an autonumber field and primary keys in the subtables using a compound key of consisting of the autonumber from the main table as a standard doubleprecision number and another autonumber field in the subtable. Access linked these up in three one-to-many relationships and everything looked great. Recently I realized that the autonumber fields in the subtables don't really do anything for me, since each subtable can have either one or no records for each record in the main table. That is, each piece of literature has exactly one entry in the main table and exactly one entry in exactly one of the three subtables. So carrying over the autonumber field from the main table is enough to uniquely identify the record. With that in mind, I removed the autonumber field from the subtables, leaving only the copy of the autonumber from the main table as the primary key. The relationship window still showed the links as one-to-many and everything I've written to date still worked. (Sorry to be so long-winded, but I want to make sure I explain this properly.) So here's the issue: When I delete such a relationship line, I can't put it back as anything other than a one-to-one relationship. I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry. But I'm leery of anything that smacks of witchcraft and a relationship that can't be created without 'fooling' the table with a false structure which is then removed again doesn't strike me as good practice. Is this correct behavior for the relationship window or have I discovered something weird? Can I set up a one-to-zero-or-one relationship somehow? I assumed that my form would simply create one record on the many side of the join and not address the fact that the structure could allow more than one. Or have I made a fundamental mistake in the design somewhere? Pete P.S. I'll be out of the office until the weekend, at least. This e-mail address is fake to keep spammers and their auto-harvesters out of my hair. If you need to get in touch personally, I am 'pdanes' and I use Yahoo mail. But please use the newsgroups whenever possible, so that all may benefit from the exchange of ideas. |
#3
|
|||
|
|||
On Tue, 6 Sep 2005 19:30:08 +0200, "Peter Danes"
wrote: I have a strange (to me, anyway) relationship setup that I haven't found addressed in any of the forums, online help or any of my manuals. Of course, maybe I'm just being dumb. Well, true Subclassing - which you're using, and a classic case of it - is somewhat tricky. You've got it almost right though. The basic structure tracks professional literature for a department's internal library. There are three types of publications collected - books, magazines and journals. There is one primary table for all the data which is common to each type (archive number, physical location, date acquired...) and a separate sub-table for each of the three types, containing data which is unique to the type (books have an author, magazines do not...) Subclassing in its classic form: an Entity with subclasses, where all subclasses have some attributes in common but each subclass has other attributes unique to that class. Strictly speaking, the term "one to one" is shorthand for "One to (zero or one)", since the child table Magazines will not contain any record where the main table refers to a Book. Originally I had a primary key in the main table using an autonumber field and primary keys in the subtables using a compound key of consisting of the autonumber from the main table as a standard doubleprecision number and another autonumber field in the subtable. Access linked these up in three one-to-many relationships and everything looked great. YOu don't need either the second autonumber nor the standard doubleprecision number - just a Long Integer foreign key/primary key linked to the main table. Recently I realized that the autonumber fields in the subtables don't really do anything for me, since each subtable can have either one or no records for each record in the main table. That is, each piece of literature has exactly one entry in the main table and exactly one entry in exactly one of the three subtables. So carrying over the autonumber field from the main table is enough to uniquely identify the record. With that in mind, I removed the autonumber field from the subtables, leaving only the copy of the autonumber from the main table as the primary key. The relationship window still showed the links as one-to-many and everything I've written to date still worked. It should be one to one: you should be linking the main table Autonumber to the single-field long integer Primary Key of the related table. (Sorry to be so long-winded, but I want to make sure I explain this properly.) So here's the issue: When I delete such a relationship line, I can't put it back as anything other than a one-to-one relationship. I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry. But I'm leery of anything that smacks of witchcraft and a relationship that can't be created without 'fooling' the table with a false structure which is then removed again doesn't strike me as good practice. Nothing fake about it; you're doing exactly the right thing. Is this correct behavior for the relationship window or have I discovered something weird? Can I set up a one-to-zero-or-one relationship somehow? I assumed that my form would simply create one record on the many side of the join and not address the fact that the structure could allow more than one. Or have I made a fundamental mistake in the design somewhere? Well... no. There will not be any records automagically created. The relationship doesn't *create* any records; it merely prevents you from creating invalid records. You presumably will have three subforms, one for Books, one for Magazines, and one for Journals. To be squeaky clean, you may need some VBA code to ensure that you can only create *one* child record in one of the tables (i.e. you should not have any main table records which have both a Book and a Journal related record). I don't know any good way to prevent this just using the relationships window (in SQL/Server you could use a constraint, and you might be able to do so in the latest versions of Access). John W. Vinson[MVP] |
#4
|
|||
|
|||
Hello Steve,
thank you for your comments. Perhaps I don't correctly understand the one-to-one relationship. (I've never used it for anything.) I thought that a one-to-one meant that there had to be exactly one record in each table on each side of the relationship. Although, now that I think about it, that would sort of mean that you could never add anything, since during each add, the 'other' table wouldn't yet have the corresponding record, so the relationship would never permit any adds. Do you mean, then, that a one-to-one relationship can have one record on one side and nothing on the other? Is there a no 'master' and 'slave' arrangement like in the one-to-many? Can either side have one and the other none? Sorry to be so obtuse, but as I said, I've never used it for anything and most books say it's rarely appropriate, that such things generally belong in one table. Maybe this is one of the exceptions. Pete "Steve Schapel" píse v diskusním príspevku ... Pete, These are one-to-one relationships. Simple as that. What you have done is entirely appropriate. I don't quite understand your comment "I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry." The fact that two of the three "subtables" will not contain a record corresponding with any given main table record, is really immaterial. -- Steve Schapel, Microsoft Access MVP Peter Danes wrote: I have a strange (to me, anyway) relationship setup that I haven't found addressed in any of the forums, online help or any of my manuals. Of course, maybe I'm just being dumb. The basic structure tracks professional literature for a department's internal library. There are three types of publications collected - books, magazines and journals. There is one primary table for all the data which is common to each type (archive number, physical location, date acquired...) and a separate sub-table for each of the three types, containing data which is unique to the type (books have an author, magazines do not...) Originally I had a primary key in the main table using an autonumber field and primary keys in the subtables using a compound key of consisting of the autonumber from the main table as a standard doubleprecision number and another autonumber field in the subtable. Access linked these up in three one-to-many relationships and everything looked great. Recently I realized that the autonumber fields in the subtables don't really do anything for me, since each subtable can have either one or no records for each record in the main table. That is, each piece of literature has exactly one entry in the main table and exactly one entry in exactly one of the three subtables. So carrying over the autonumber field from the main table is enough to uniquely identify the record. With that in mind, I removed the autonumber field from the subtables, leaving only the copy of the autonumber from the main table as the primary key. The relationship window still showed the links as one-to-many and everything I've written to date still worked. (Sorry to be so long-winded, but I want to make sure I explain this properly.) So here's the issue: When I delete such a relationship line, I can't put it back as anything other than a one-to-one relationship. I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry. But I'm leery of anything that smacks of witchcraft and a relationship that can't be created without 'fooling' the table with a false structure which is then removed again doesn't strike me as good practice. Is this correct behavior for the relationship window or have I discovered something weird? Can I set up a one-to-zero-or-one relationship somehow? I assumed that my form would simply create one record on the many side of the join and not address the fact that the structure could allow more than one. Or have I made a fundamental mistake in the design somewhere? Pete P.S. I'll be out of the office until the weekend, at least. This e-mail address is fake to keep spammers and their auto-harvesters out of my hair. If you need to get in touch personally, I am 'pdanes' and I use Yahoo mail. But please use the newsgroups whenever possible, so that all may benefit from the exchange of ideas. |
#5
|
|||
|
|||
Hello John,
thank you for the explanations. It apppears that I simply had my head stuck in the fertilizer generator again. Well, true Subclassing - which you're using, and a classic case of it - is somewhat tricky. You've got it almost right though. I've seen the term subclassing here and there, but never knew what it meant and it was always in a context that made it sound pretty involved. Strictly speaking, the term "one to one" is shorthand for "One to (zero or one)", since the child table Magazines will not contain any record where the main table refers to a Book. Yes, and I should have figured that out myself, since a simultaneous add to two tables is impossible. (Or is it? A join query...Still, even SQL at some level has to put a record into one table and then into the other.) YOu don't need either the second autonumber nor the standard doubleprecision number - just a Long Integer foreign key/primary key linked to the main table. Sorry about the confusion, I did get that one right, actually. I'm using a Czech OS and I mistranslated the data type in my original post. Well... no. There will not be any records automagically created. The relationship doesn't *create* any records; it merely prevents you from creating invalid records. I understand that - my wording there was a little awkward. Much of my confusion came from Access leaving the lines in the relationship window as one-to-many even after I had altered the subtables. Is that an Access mistake? Does changing the tables and not changing the relationships leave the DB in some indeterminate 'between' state, or does Access change the relationship to function correctly and simply neglect to update the relationship window? Or does it leave them displayed that way deliberately for some reason? You presumably will have three subforms, one for Books, one for Magazines, and one for Journals. Exactly. One main form with three subforms and some other doodads which are not pertinent to this discussion. The subforms display nothing at all when there is no record, which I find a little aggressive. My preference would be to have the fields displayed but disabled; it seems more in line with standard Windows functionality, but it's not a big enough issue for me to waste time circumventing it. And at least the user will have no possible confusion with which subform to fill out. To be squeaky clean, you may need some VBA code to ensure that you can only create *one* child record in one of the tables (i.e. you should not have any main table records which have both a Book and a Journal related record). Yes, I have that currently under construction and no problems. That sort of stuff is well within my capabilities. I don't know any good way to prevent this just using the relationships window (in SQL/Server you could use a constraint, and you might be able to do so in the latest versions of Access). This is a stand-alone single-user app built with A2K, but I can handle it with VBA just fine. I think I'll build a small test DB to experiment with to make sure I've got it all clear. Thank you for explaining it so well. Pete I have a strange (to me, anyway) relationship setup that I haven't found addressed in any of the forums, online help or any of my manuals. Of course, maybe I'm just being dumb. Well, true Subclassing - which you're using, and a classic case of it - is somewhat tricky. You've got it almost right though. The basic structure tracks professional literature for a department's internal library. There are three types of publications collected - books, magazines and journals. There is one primary table for all the data which is common to each type (archive number, physical location, date acquired...) and a separate sub-table for each of the three types, containing data which is unique to the type (books have an author, magazines do not...) Subclassing in its classic form: an Entity with subclasses, where all subclasses have some attributes in common but each subclass has other attributes unique to that class. Strictly speaking, the term "one to one" is shorthand for "One to (zero or one)", since the child table Magazines will not contain any record where the main table refers to a Book. Originally I had a primary key in the main table using an autonumber field and primary keys in the subtables using a compound key of consisting of the autonumber from the main table as a standard doubleprecision number and another autonumber field in the subtable. Access linked these up in three one-to-many relationships and everything looked great. YOu don't need either the second autonumber nor the standard doubleprecision number - just a Long Integer foreign key/primary key linked to the main table. Recently I realized that the autonumber fields in the subtables don't really do anything for me, since each subtable can have either one or no records for each record in the main table. That is, each piece of literature has exactly one entry in the main table and exactly one entry in exactly one of the three subtables. So carrying over the autonumber field from the main table is enough to uniquely identify the record. With that in mind, I removed the autonumber field from the subtables, leaving only the copy of the autonumber from the main table as the primary key. The relationship window still showed the links as one-to-many and everything I've written to date still worked. It should be one to one: you should be linking the main table Autonumber to the single-field long integer Primary Key of the related table. (Sorry to be so long-winded, but I want to make sure I explain this properly.) So here's the issue: When I delete such a relationship line, I can't put it back as anything other than a one-to-one relationship. I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry. But I'm leery of anything that smacks of witchcraft and a relationship that can't be created without 'fooling' the table with a false structure which is then removed again doesn't strike me as good practice. Nothing fake about it; you're doing exactly the right thing. Is this correct behavior for the relationship window or have I discovered something weird? Can I set up a one-to-zero-or-one relationship somehow? I assumed that my form would simply create one record on the many side of the join and not address the fact that the structure could allow more than one. Or have I made a fundamental mistake in the design somewhere? Well... no. There will not be any records automagically created. The relationship doesn't *create* any records; it merely prevents you from creating invalid records. You presumably will have three subforms, one for Books, one for Magazines, and one for Journals. To be squeaky clean, you may need some VBA code to ensure that you can only create *one* child record in one of the tables (i.e. you should not have any main table records which have both a Book and a Journal related record). I don't know any good way to prevent this just using the relationships window (in SQL/Server you could use a constraint, and you might be able to do so in the latest versions of Access). John W. Vinson[MVP] |
#6
|
|||
|
|||
You've got one table with a Primary Key on it, and the other table with a
Foreign Key pointing to that first record. That means that you still need to have a record in the first table before you can have one in the second table: you can't have only a record in the second table. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Peter Danes" wrote in message ... Hello Steve, thank you for your comments. Perhaps I don't correctly understand the one-to-one relationship. (I've never used it for anything.) I thought that a one-to-one meant that there had to be exactly one record in each table on each side of the relationship. Although, now that I think about it, that would sort of mean that you could never add anything, since during each add, the 'other' table wouldn't yet have the corresponding record, so the relationship would never permit any adds. Do you mean, then, that a one-to-one relationship can have one record on one side and nothing on the other? Is there a no 'master' and 'slave' arrangement like in the one-to-many? Can either side have one and the other none? Sorry to be so obtuse, but as I said, I've never used it for anything and most books say it's rarely appropriate, that such things generally belong in one table. Maybe this is one of the exceptions. Pete "Steve Schapel" píse v diskusním príspevku ... Pete, These are one-to-one relationships. Simple as that. What you have done is entirely appropriate. I don't quite understand your comment "I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry." The fact that two of the three "subtables" will not contain a record corresponding with any given main table record, is really immaterial. -- Steve Schapel, Microsoft Access MVP Peter Danes wrote: I have a strange (to me, anyway) relationship setup that I haven't found addressed in any of the forums, online help or any of my manuals. Of course, maybe I'm just being dumb. The basic structure tracks professional literature for a department's internal library. There are three types of publications collected - books, magazines and journals. There is one primary table for all the data which is common to each type (archive number, physical location, date acquired...) and a separate sub-table for each of the three types, containing data which is unique to the type (books have an author, magazines do not...) Originally I had a primary key in the main table using an autonumber field and primary keys in the subtables using a compound key of consisting of the autonumber from the main table as a standard doubleprecision number and another autonumber field in the subtable. Access linked these up in three one-to-many relationships and everything looked great. Recently I realized that the autonumber fields in the subtables don't really do anything for me, since each subtable can have either one or no records for each record in the main table. That is, each piece of literature has exactly one entry in the main table and exactly one entry in exactly one of the three subtables. So carrying over the autonumber field from the main table is enough to uniquely identify the record. With that in mind, I removed the autonumber field from the subtables, leaving only the copy of the autonumber from the main table as the primary key. The relationship window still showed the links as one-to-many and everything I've written to date still worked. (Sorry to be so long-winded, but I want to make sure I explain this properly.) So here's the issue: When I delete such a relationship line, I can't put it back as anything other than a one-to-one relationship. I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry. But I'm leery of anything that smacks of witchcraft and a relationship that can't be created without 'fooling' the table with a false structure which is then removed again doesn't strike me as good practice. Is this correct behavior for the relationship window or have I discovered something weird? Can I set up a one-to-zero-or-one relationship somehow? I assumed that my form would simply create one record on the many side of the join and not address the fact that the structure could allow more than one. Or have I made a fundamental mistake in the design somewhere? Pete P.S. I'll be out of the office until the weekend, at least. This e-mail address is fake to keep spammers and their auto-harvesters out of my hair. If you need to get in touch personally, I am 'pdanes' and I use Yahoo mail. But please use the newsgroups whenever possible, so that all may benefit from the exchange of ideas. |
#7
|
|||
|
|||
Pete,
Yes, there is a master/slave arrangenment with defined one-to-one relationships where Referential Integrity is enforced. If you do this isn the Relationships window, by dragging from a field in one table to the field in the other, the "line of command" is determined by the direction of the drag, if you see what I mean. In your case, if you drag from the Autonumber primary key field of the main table to the Number (Long) field in a subtable, and then Create the relationship with Referential Integrity enforced, well then you will be able to enter whatever records you want in the main table, without a corresponding record in the subtable. But you won't be allowed to enter a record in the subtable without a related record in the main table. If you did not enforce RI, then you would be able to enter records in either table without a corresponding record in the other... but I can't think of a case where this would be applicable... certainly not in your database. As regards "most books say it's rarely appropriate", this is true. And the scenario you have described, sometimes called a sub-classing design, is one of those situations where it is appropriate. -- Steve Schapel, Microsoft Access MVP Peter Danes wrote: Hello Steve, thank you for your comments. Perhaps I don't correctly understand the one-to-one relationship. (I've never used it for anything.) I thought that a one-to-one meant that there had to be exactly one record in each table on each side of the relationship. Although, now that I think about it, that would sort of mean that you could never add anything, since during each add, the 'other' table wouldn't yet have the corresponding record, so the relationship would never permit any adds. Do you mean, then, that a one-to-one relationship can have one record on one side and nothing on the other? Is there a no 'master' and 'slave' arrangement like in the one-to-many? Can either side have one and the other none? Sorry to be so obtuse, but as I said, I've never used it for anything and most books say it's rarely appropriate, that such things generally belong in one table. Maybe this is one of the exceptions. |
#8
|
|||
|
|||
Hello Steve,
You're right, I just tried it. If I drag from the main table to the sub table, it works fine. If I drag from the sub table to the main table, it refuses to enforce RI. What determines the direction? Is it that one has an autonumber PK field and the other simply a long integer PK? I just tried linking some other key fields to see what would happen and got 1-N, 1-1 and Undetermined for various combinations of fields, some key and some not. I also tried it on two autonumber PK fields and it refused to create the relationship at all. I only did about a dozen, not enough to get a real sense of what is determining the result. Do you know of any websites that go into detail on this? I have Getz, Litwin and Gilbert's 2-volume developer's handbook, but I don't recall seeing this in there anywhere. And the 1-1 line in the relationship window doesn't seem to give any indication of the controlling direction once the relationship is established. Is there some way to tell by looking, or do you have to dig into the tabledefs and know what attributes of the fields to look for? Pete "Steve Schapel" píse v diskusním príspevku ... Pete, Yes, there is a master/slave arrangenment with defined one-to-one relationships where Referential Integrity is enforced. If you do this isn the Relationships window, by dragging from a field in one table to the field in the other, the "line of command" is determined by the direction of the drag, if you see what I mean. In your case, if you drag from the Autonumber primary key field of the main table to the Number (Long) field in a subtable, and then Create the relationship with Referential Integrity enforced, well then you will be able to enter whatever records you want in the main table, without a corresponding record in the subtable. But you won't be allowed to enter a record in the subtable without a related record in the main table. If you did not enforce RI, then you would be able to enter records in either table without a corresponding record in the other... but I can't think of a case where this would be applicable... certainly not in your database. As regards "most books say it's rarely appropriate", this is true. And the scenario you have described, sometimes called a sub-classing design, is one of those situations where it is appropriate. -- Steve Schapel, Microsoft Access MVP Peter Danes wrote: Hello Steve, thank you for your comments. Perhaps I don't correctly understand the one-to-one relationship. (I've never used it for anything.) I thought that a one-to-one meant that there had to be exactly one record in each table on each side of the relationship. Although, now that I think about it, that would sort of mean that you could never add anything, since during each add, the 'other' table wouldn't yet have the corresponding record, so the relationship would never permit any adds. Do you mean, then, that a one-to-one relationship can have one record on one side and nothing on the other? Is there a no 'master' and 'slave' arrangement like in the one-to-many? Can either side have one and the other none? Sorry to be so obtuse, but as I said, I've never used it for anything and most books say it's rarely appropriate, that such things generally belong in one table. Maybe this is one of the exceptions. |
#9
|
|||
|
|||
Hello Doug,
thank you for your comments. I obviously didn't understand the 1-1 relationship very well, but I think I'm starting to get a grip on it. It seems simple on the surface, but there is more to it than I first thought. Pete "Douglas J. Steele" píše v diskusním příspěvku ... You've got one table with a Primary Key on it, and the other table with a Foreign Key pointing to that first record. That means that you still need to have a record in the first table before you can have one in the second table: you can't have only a record in the second table. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Peter Danes" wrote in message ... Hello Steve, thank you for your comments. Perhaps I don't correctly understand the one-to-one relationship. (I've never used it for anything.) I thought that a one-to-one meant that there had to be exactly one record in each table on each side of the relationship. Although, now that I think about it, that would sort of mean that you could never add anything, since during each add, the 'other' table wouldn't yet have the corresponding record, so the relationship would never permit any adds. Do you mean, then, that a one-to-one relationship can have one record on one side and nothing on the other? Is there a no 'master' and 'slave' arrangement like in the one-to-many? Can either side have one and the other none? Sorry to be so obtuse, but as I said, I've never used it for anything and most books say it's rarely appropriate, that such things generally belong in one table. Maybe this is one of the exceptions. Pete "Steve Schapel" píse v diskusním príspevku ... Pete, These are one-to-one relationships. Simple as that. What you have done is entirely appropriate. I don't quite understand your comment "I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry." The fact that two of the three "subtables" will not contain a record corresponding with any given main table record, is really immaterial. -- Steve Schapel, Microsoft Access MVP Peter Danes wrote: I have a strange (to me, anyway) relationship setup that I haven't found addressed in any of the forums, online help or any of my manuals. Of course, maybe I'm just being dumb. The basic structure tracks professional literature for a department's internal library. There are three types of publications collected - books, magazines and journals. There is one primary table for all the data which is common to each type (archive number, physical location, date acquired...) and a separate sub-table for each of the three types, containing data which is unique to the type (books have an author, magazines do not...) Originally I had a primary key in the main table using an autonumber field and primary keys in the subtables using a compound key of consisting of the autonumber from the main table as a standard doubleprecision number and another autonumber field in the subtable. Access linked these up in three one-to-many relationships and everything looked great. Recently I realized that the autonumber fields in the subtables don't really do anything for me, since each subtable can have either one or no records for each record in the main table. That is, each piece of literature has exactly one entry in the main table and exactly one entry in exactly one of the three subtables. So carrying over the autonumber field from the main table is enough to uniquely identify the record. With that in mind, I removed the autonumber field from the subtables, leaving only the copy of the autonumber from the main table as the primary key. The relationship window still showed the links as one-to-many and everything I've written to date still worked. (Sorry to be so long-winded, but I want to make sure I explain this properly.) So here's the issue: When I delete such a relationship line, I can't put it back as anything other than a one-to-one relationship. I don't want a strict one-to-one, because for every main record, two of the three tables will have no entry. But I'm leery of anything that smacks of witchcraft and a relationship that can't be created without 'fooling' the table with a false structure which is then removed again doesn't strike me as good practice. Is this correct behavior for the relationship window or have I discovered something weird? Can I set up a one-to-zero-or-one relationship somehow? I assumed that my form would simply create one record on the many side of the join and not address the fact that the structure could allow more than one. Or have I made a fundamental mistake in the design somewhere? Pete P.S. I'll be out of the office until the weekend, at least. This e-mail address is fake to keep spammers and their auto-harvesters out of my hair. If you need to get in touch personally, I am 'pdanes' and I use Yahoo mail. But please use the newsgroups whenever possible, so that all may benefit from the exchange of ideas. |
#10
|
|||
|
|||
On Tue, 6 Sep 2005 23:22:46 +0200, "Peter Danes"
wrote: Hello John, thank you for the explanations. Additional comments inline... I understand that - my wording there was a little awkward. Much of my confusion came from Access leaving the lines in the relationship window as one-to-many even after I had altered the subtables. Is that an Access mistake? Does changing the tables and not changing the relationships leave the DB in some indeterminate 'between' state, or does Access change the relationship to function correctly and simply neglect to update the relationship window? Or does it leave them displayed that way deliberately for some reason? I'd say it's just a bug, or an overlooked feature. The relationship window doesn't get reconstructed when the table structure changes, it seems. Whether the relationship actually changes or not is sort of irrelevant - if there is a unique Index on the foreign key, you can't add a second record anyhow; so regardless of what the relationship window shows, it's effectively one to one. You presumably will have three subforms, one for Books, one for Magazines, and one for Journals. Exactly. One main form with three subforms and some other doodads which are not pertinent to this discussion. The subforms display nothing at all when there is no record, which I find a little aggressive. My preference would be to have the fields displayed but disabled; it seems more in line with standard Windows functionality, but it's not a big enough issue for me to waste time circumventing it. And at least the user will have no possible confusion with which subform to fill out. A blank subform sounds suspiciously like one for which the Recordsource is not updateable. If you've changed the table structure and the relationships, you probably need to redefine each Subform's Recordsource to point to the (newly redesigned) tables; also check the Master/Child Link Fields. My guess is that the form was not updated to reflect the changed table structure. To be squeaky clean, you may need some VBA code to ensure that you can only create *one* child record in one of the tables (i.e. you should not have any main table records which have both a Book and a Journal related record). Yes, I have that currently under construction and no problems. That sort of stuff is well within my capabilities. Excellent. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Relationship feature/bug/accident | Peter Danes | General Discussion | 22 | September 11th, 2005 11:15 PM |
Impossible? Relationship / Join Quandary | SteveTyco | Database Design | 1 | May 5th, 2005 01:58 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |
Setting dual relationship with tool connector | Carlos | Visio | 0 | May 20th, 2004 12:51 AM |