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 |
#11
|
|||
|
|||
Address Design Structure
Thanks very much for your input, Lynn...this stuff is so hard to get at
first, but SO important to get! Again, thanks to people like you taking the time to respond, is so very appreciated! Billiam "Lynn Trapp" wrote: On Fri, 1 Aug 2008 08:02:10 -0700, Billiam wrote: examples in our organization include: Husband Wife and child, all instructors, at the same home address, and any combination of work, billing and shipping addresses. Also, many instructors at one school is another scenario. Our business rules allow a maximum of 5 street addresses, and most use 4 addresses. The possible need for up to 5 street addresses is definitely reaching the point where you would need to separate them into a separate table. I would recommend that you use the approach that Graham Mandeno has outlined for you. Lynn Trapp www.ltcomputerdesigns.com |
#12
|
|||
|
|||
Address Design Structure
Billiam,
Thank you for your courtesy! From my point of view, your wanting to wrangle with your database over time is more than fine and I applaud your wanting to learn how to use Access properly so that you can really unleash its huge potential. I am sure your ambition will get you over the learning curve hump and eventually get you to that point. My point in offering you my help was only to offer you an option of getting what you want done if you did not have the time to put into the aforementioned process. As you pursue your goal, post any questions you have to the newsgroup and maybe we will meet again. Good Luck! Steve "Billiam" wrote in message ... Thank you again for your offer, Steve. This free forum, however, suits my limited budget as a non-profit org. In addition, I think the real point is that I do have a working non-normalized database, I am here to learn how to use Access properly so that I can really unleash its huge potential. Sometimes the best learned lessons are the ones that you have to wrangle with over time, and I have found that the very knowledgeable and talented people here ( who do offer their services for free ) to be very helpful,and thought provoking. I certainly appreciate their efforts and applaud their dedication to what must sometimes be a very frustrating experience for them dealing with us newbies! Billiam "Steve" wrote: I offered to help you on July 24 and you chose to listen to Klatuu instead. You did not get the help you need in the thread on July 24 so here you are a week later and no further ahead. What are you going to do if you do not again get the help you need in this thread? Steve "Billiam" wrote in message ... With the help of this forum, I am trying to correctly design my tables to be normalized. I would like to break my design down into sections to work on just for ease of discussion and for the rest of people trying to figure this all out it may make it clearer to them also. I am a beginner in Access and TRYING HARD to figure it out so that I can properly design a database instead of the 55 field non-normlized one I currently use(:-0 Our non-profit organization has instructors. They may have many addresses and therefore address types. Here are the table designs so far: One instructor can have many addresses One address can have many instructors Therefore this is a many to many realtionship, and so will be solved with a junction/intermediate table (called instructorAddresses) tblInstructors InstructorID (autonumber, primary key,Long Integer, ref. integrity and cascade updates) SpecialID# (text field as this badge number starts with a series of zeroes) FirstName (text field) MidName (text field) LastName (text field) Sex (or should this be a lookup table? If so, what relationship do I create and where? I know some of you are saying "well an instructor can only be one sex at a time" which is true, but what do you do if an instructor is also transgendered and you need to keep a history of when that instructor was male, and when they were female) tblAddresses AddressID (autonumber,primary key,Long Integer, ref. integrity and cascade updates) AddressLine1 AddressLine2 AddressLine3 City PostalCode ProvinceStateCounty Country tblInstructorAddresses (the junction or intermediate table) InstructorAddressID (autonumber,primary key,Long Integer, ref. integrity and cascade updates) InstructorID (Foreign Key, number, Long integer, Duplicates OK,) addressID (Foreign Key, number, Long integer, Duplicates OK,) I have a one to many relationship created from tblInstructors to tblInstructorAddresses I have a one to many relationship created from tblAddresses to tbl InstructorAddresses Now, I obviously need an addressType Table (Home, Work, Shipping,Billing etc): tblAddressType AddressTypeID (autonumber,primary key,Long Integer, ref. integrity and cascade updates) AddressType (Text ) FromDate (date) ToDate (date) One Address can have many address types (i.e 123 maple Ave can be home and shipping and billing) One AddressType has many Addresses (i.e the shipping address type can vary between a home address or a school address for one instructor depending on the day) Am I right that this is a many to many relationship, or am I wrong that AddressType has many addresses? Am I modeling this correctly as a many to many relationship? Billiam |
#13
|
|||
|
|||
Address Design Structure
"Steve" wrote in message
... My point in offering you my help was only to offer you an option of getting what you want done WRONG. Your "point" was just another feeble attempt to solicit work from a group that is set up for FREE peer to peer support. John... |
#14
|
|||
|
|||
Address Design Structure
Hi Billiam
Following your preference, my answers are inline... It is not a technical possibility but a fact, and I do need to track the history as the person is submitting to a Government office. I understand Government requirements - say no more! G While I do not believe it is likely to happen with another instructor, what do you think is the best way to handle/link Patrick/Patricia's (not real name) records since the first name and middle names have also changed, and I have to produce reports of this person's Instructor Activity as either male or female. Hmmm... this adds a whole new dimension. Actually, names can change for other reasons too, such as marriage. Just thinking by the seat of my pants here, I'm wondering if it would be better to create a new instructor record when one of the pieces of basic information (name/gender) changes, with a DateSuperseded field, so that all historic records can be linked to the current record. What do you think? If I understand you correctly, we are treating this as a many to many relationship, and resolving that with the junction table InstructorGenderHistory. So one instructor can have many genders (as in Pat/Patricia) One gender can have many Instructors. Yes, that's the idea. What was/is confusing me is that really an instructor can only be one sex at a time, so wouldn't that make it a one to one relationship??? And if so, all I would do is have a gender reference table with a Start date field? The StartDate field cannot be in the reference table, because all the males don't start being males on the same day! The "point-in-time" value is ascertained by the StartDate field in the junction table... Note that you don't need an end date. The current gender (your most common query) is the record with the latest start date and the gender at a given date in the past is the record with the latest start date BEFORE the given date. Okay, that makes sense. .... yes, I see the penny has dropped now :-) The actual gender code could be a simple M or F, or it could be a foreign key to a reference table (lookup tables have bad connotations!). Do you mean they sometimes cause confusion between lookup fields and lookup tables? Yes, reference table/lookup table are the same thing. I just prefer "reference" :-) Often it is useful to have a static table of other gender-related terms, such as pronouns (he/she, him/her, his/hers, etc). Yes, this reference table is exactly what I would like, and the personal pronouns are a great idea. Glad you like it :-) On the address question, the correct place for your AddressType FK field is in the junction table (tblInstructorAddresses). Say you have two instructors: 1=John and 2=Mary. They live together at 123 Maple Avenue (AddressID 1) John works at 456 Elm Street (AddressID 2) Mary works at 789 Oak Street (AddressID 3) Your junction table has these fields: iadID (optional primary key) iadInstructor (FK to tblInstructors) iadAddressType (simple code H/W/S/B or FK to tblAddressTypes) iadAddress (FK to addresses) iadStartDate (only if you need to keep address change history) Your data looks like this: (1) 1 H 1 (John lives at address 1) (2) 2 H 1 (Mary lives at address 1) (3) 1 W 2 (John works at address 2) (4) 2 W 3 (Mary works at address 3) The address type field is like the gender field - it's up to you whether you have a reference table or keep it as a simple one-letter code. Whatever you decide, the history tracking dates DO NOT belong in your reference table, but in the junction table. If you want an instructor to have at most ONE address of each type, then make a unique composite key of iadInstructor and iadAddresstype. Just a point on cascading relationships - it often makes sense to have cascading deletes between a main table and a junction table, but it never makes sense to have cascading updates on an autonumber field, because it is impossible to update an autonumber field. Sorry, that was an error on my part, as I know an autonumber field cannot be updated. On this note, I was concerned that since i need to keep track of the history of addresses if selecting cascade updates/deletions would delete the history...I am correct that this is not the case, right? If they change their address that would remain in the InstructorAddress junction table since their is the unique StartDate field? I sounds like you would not want to cascade deletes. In any case, the related "many-side" records would olny be deleted if either the tblInstructors record or the tblAddresses record were deleted. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand |
#15
|
|||
|
|||
Address Design Structure
Thanks for sticking with me Graham! My answers are inline.
"Graham Mandeno" wrote: Hmmm... this adds a whole new dimension. Actually, names can change for other reasons too, such as marriage. Just thinking by the seat of my pants here, I'm wondering if it would be better to create a new instructor record when one of the pieces of basic information (name/gender) changes, with a DateSuperseded field, so that all historic records can be linked to the current record. What do you think? Graham, I think that sounds great, but before I start changing things I also want you to be aware of another problem I have. Some Instructors wish to have their information posted on our website (about 60 percent of them). The information that is posted is: First and Last name, Phone#, Cities they teach in, an e-mail address, and a website link, and their qualifications. (BTW, I have tblQualifications in a many to many with tblnstructors and a junction table InstructorQualifications). Here is my problem...instructors must be entered in by their Given names, and as you probably know, not everyone uses their given name as their first name. Also, the cities entered in for address are not necessarily where the instructor teaches. As you can probably see, then, it is difficult to build the report to export to the website of all the instructors that wish to be on the public website. In my non-normalized database, I actually had a different table called "PublicInfo", but obviously there is a lot of stuff being repeated doing it that way. I know it sounds ridiculous, but should I have a first names table? If I have a seperate table of cities, could I somehow use a kind of multiple combo box to choose a maximum of three cities from the city table for each instructor that wishes to be on the website , on an Instructor Info form? Is there an easier way to solve this...as I said, I am really new to access, and could use your experience, Graham!!! Many, many Thanks, Billiam |
#16
|
|||
|
|||
Address Design Structure
Hi Billiam
What I usually do is add a PreferredName field. Ninety percent of the time this is blank, in which case FirstName is used. It handles the problem of diminutives as well as people who are known by a middle name. You can go overboard with normalising. I would not pull a field with a repeating value out into another table unless it truly represents another entity that could be used for grouping and selection. Even though you might have hundreds of records in a Persons table with FirstName "John", this common attribute does not relate these records to each other in any logical way, so there is no sense in having a separate table of FirstNames. However, the cities in which an instructor teaches is an attribute that could well be used to relate instructor records, so it makes sense to have a separate table. It should be many-to-many, by the way, just like Qualifications. You don't want to limit an instructor to three cities because of the restriction in your design. I have written a sample database demonstrating an easy way to manage many-to-many relationships, using listboxes and combo boxes on a form. My colleague, Ken Snell, has kindly put this up on his website at http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of the page, in the section "Easy Maintenance of "Many-To-Many" Data with a Form". I suggest you download it and see if it will serve your purpose. Don't worry too much about the code in the class module named "MtoMListHandler" - just look at the forms. Then you can import MtoMListHandler into your database and set up your forms in a similar way. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... Thanks for sticking with me Graham! My answers are inline. "Graham Mandeno" wrote: Hmmm... this adds a whole new dimension. Actually, names can change for other reasons too, such as marriage. Just thinking by the seat of my pants here, I'm wondering if it would be better to create a new instructor record when one of the pieces of basic information (name/gender) changes, with a DateSuperseded field, so that all historic records can be linked to the current record. What do you think? Graham, I think that sounds great, but before I start changing things I also want you to be aware of another problem I have. Some Instructors wish to have their information posted on our website (about 60 percent of them). The information that is posted is: First and Last name, Phone#, Cities they teach in, an e-mail address, and a website link, and their qualifications. (BTW, I have tblQualifications in a many to many with tblnstructors and a junction table InstructorQualifications). Here is my problem...instructors must be entered in by their Given names, and as you probably know, not everyone uses their given name as their first name. Also, the cities entered in for address are not necessarily where the instructor teaches. As you can probably see, then, it is difficult to build the report to export to the website of all the instructors that wish to be on the public website. In my non-normalized database, I actually had a different table called "PublicInfo", but obviously there is a lot of stuff being repeated doing it that way. I know it sounds ridiculous, but should I have a first names table? If I have a seperate table of cities, could I somehow use a kind of multiple combo box to choose a maximum of three cities from the city table for each instructor that wishes to be on the website , on an Instructor Info form? Is there an easier way to solve this...as I said, I am really new to access, and could use your experience, Graham!!! Many, many Thanks, Billiam |
#17
|
|||
|
|||
Address Design Structure
Thanks again for your time and advice. I will definately look at the Ken
Snell database, thanks for that link. On the topic of Address structure, I am wondering if the following is the correct way to design the following as i seem to be getting overwhelmed a bit by the realtionships: City, ZipPostalCode, ProvinceStateCounty, Country One City can have many ZipPostalCode One ZipPostalCode can have many cities Therefore a many to many relationship which I would solve using a junction table Called CityZipPostalCode. I am really unsure what to do next, Graham. Do I need to define a relationship further between city and StateProvinceCounty, as 1 city can be in more than one StateProvinceCounty and one StateProvinceCounty can have many cities...If I do, is this another junction table??? Also, one city can be in many countries and one country can have many cities??? Finally, how and where do I relate StateProvinceCounty to the Countries table... as i do not know if many StateProvinceCounty are in many Countries (should have studued Geography more ,g see ideas spinning around in mostly empty cranial cavity causing what little brain there is *PAIN* Note: I do search on city quite a bit, so figure it is important to set this up right, finally! Billiam "Graham Mandeno" wrote: Hi Billiam What I usually do is add a PreferredName field. Ninety percent of the time this is blank, in which case FirstName is used. It handles the problem of diminutives as well as people who are known by a middle name. You can go overboard with normalising. I would not pull a field with a repeating value out into another table unless it truly represents another entity that could be used for grouping and selection. Even though you might have hundreds of records in a Persons table with FirstName "John", this common attribute does not relate these records to each other in any logical way, so there is no sense in having a separate table of FirstNames. However, the cities in which an instructor teaches is an attribute that could well be used to relate instructor records, so it makes sense to have a separate table. It should be many-to-many, by the way, just like Qualifications. You don't want to limit an instructor to three cities because of the restriction in your design. I have written a sample database demonstrating an easy way to manage many-to-many relationships, using listboxes and combo boxes on a form. My colleague, Ken Snell, has kindly put this up on his website at http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of the page, in the section "Easy Maintenance of "Many-To-Many" Data with a Form". I suggest you download it and see if it will serve your purpose. Don't worry too much about the code in the class module named "MtoMListHandler" - just look at the forms. Then you can import MtoMListHandler into your database and set up your forms in a similar way. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... Thanks for sticking with me Graham! My answers are inline. "Graham Mandeno" wrote: Hmmm... this adds a whole new dimension. Actually, names can change for other reasons too, such as marriage. Just thinking by the seat of my pants here, I'm wondering if it would be better to create a new instructor record when one of the pieces of basic information (name/gender) changes, with a DateSuperseded field, so that all historic records can be linked to the current record. What do you think? Graham, I think that sounds great, but before I start changing things I also want you to be aware of another problem I have. Some Instructors wish to have their information posted on our website (about 60 percent of them). The information that is posted is: First and Last name, Phone#, Cities they teach in, an e-mail address, and a website link, and their qualifications. (BTW, I have tblQualifications in a many to many with tblnstructors and a junction table InstructorQualifications). Here is my problem...instructors must be entered in by their Given names, and as you probably know, not everyone uses their given name as their first name. Also, the cities entered in for address are not necessarily where the instructor teaches. As you can probably see, then, it is difficult to build the report to export to the website of all the instructors that wish to be on the public website. In my non-normalized database, I actually had a different table called "PublicInfo", but obviously there is a lot of stuff being repeated doing it that way. I know it sounds ridiculous, but should I have a first names table? If I have a seperate table of cities, could I somehow use a kind of multiple combo box to choose a maximum of three cities from the city table for each instructor that wishes to be on the website , on an Instructor Info form? Is there an easier way to solve this...as I said, I am really new to access, and could use your experience, Graham!!! Many, many Thanks, Billiam |
#18
|
|||
|
|||
Address Design Structure
Hi Billiam
Like I said, you can go overboard with normalisation! As far as I know, a city can NOT be in two countries. The London in England is not the same as the London in Ontario, Canada. They bear no more relationship to one another that my uncle George to George Bush. So, if you have a table of Cities, London would have two records, distinguishable by other fields such as StateProvinceCounty and/or Country. You can also fall into the trap of making a box that requires many objects to be distorted in order to fit it. For example, some countries do not have any states/provinces/counties. Also, some countries have a concept of a suburb within a city (while smaller towns don't have suburbs) and other countries have the concept of a suburb or town as being the "city" within a state. It's difficult to provide a definitive answer! I can only caution you against (a) over-normalising and (b) creating a design that doesn't fit all cases. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... Thanks again for your time and advice. I will definately look at the Ken Snell database, thanks for that link. On the topic of Address structure, I am wondering if the following is the correct way to design the following as i seem to be getting overwhelmed a bit by the realtionships: City, ZipPostalCode, ProvinceStateCounty, Country One City can have many ZipPostalCode One ZipPostalCode can have many cities Therefore a many to many relationship which I would solve using a junction table Called CityZipPostalCode. I am really unsure what to do next, Graham. Do I need to define a relationship further between city and StateProvinceCounty, as 1 city can be in more than one StateProvinceCounty and one StateProvinceCounty can have many cities...If I do, is this another junction table??? Also, one city can be in many countries and one country can have many cities??? Finally, how and where do I relate StateProvinceCounty to the Countries table... as i do not know if many StateProvinceCounty are in many Countries (should have studued Geography more ,g see ideas spinning around in mostly empty cranial cavity causing what little brain there is *PAIN* Note: I do search on city quite a bit, so figure it is important to set this up right, finally! Billiam "Graham Mandeno" wrote: Hi Billiam What I usually do is add a PreferredName field. Ninety percent of the time this is blank, in which case FirstName is used. It handles the problem of diminutives as well as people who are known by a middle name. You can go overboard with normalising. I would not pull a field with a repeating value out into another table unless it truly represents another entity that could be used for grouping and selection. Even though you might have hundreds of records in a Persons table with FirstName "John", this common attribute does not relate these records to each other in any logical way, so there is no sense in having a separate table of FirstNames. However, the cities in which an instructor teaches is an attribute that could well be used to relate instructor records, so it makes sense to have a separate table. It should be many-to-many, by the way, just like Qualifications. You don't want to limit an instructor to three cities because of the restriction in your design. I have written a sample database demonstrating an easy way to manage many-to-many relationships, using listboxes and combo boxes on a form. My colleague, Ken Snell, has kindly put this up on his website at http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of the page, in the section "Easy Maintenance of "Many-To-Many" Data with a Form". I suggest you download it and see if it will serve your purpose. Don't worry too much about the code in the class module named "MtoMListHandler" - just look at the forms. Then you can import MtoMListHandler into your database and set up your forms in a similar way. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... Thanks for sticking with me Graham! My answers are inline. "Graham Mandeno" wrote: Hmmm... this adds a whole new dimension. Actually, names can change for other reasons too, such as marriage. Just thinking by the seat of my pants here, I'm wondering if it would be better to create a new instructor record when one of the pieces of basic information (name/gender) changes, with a DateSuperseded field, so that all historic records can be linked to the current record. What do you think? Graham, I think that sounds great, but before I start changing things I also want you to be aware of another problem I have. Some Instructors wish to have their information posted on our website (about 60 percent of them). The information that is posted is: First and Last name, Phone#, Cities they teach in, an e-mail address, and a website link, and their qualifications. (BTW, I have tblQualifications in a many to many with tblnstructors and a junction table InstructorQualifications). Here is my problem...instructors must be entered in by their Given names, and as you probably know, not everyone uses their given name as their first name. Also, the cities entered in for address are not necessarily where the instructor teaches. As you can probably see, then, it is difficult to build the report to export to the website of all the instructors that wish to be on the public website. In my non-normalized database, I actually had a different table called "PublicInfo", but obviously there is a lot of stuff being repeated doing it that way. I know it sounds ridiculous, but should I have a first names table? If I have a seperate table of cities, could I somehow use a kind of multiple combo box to choose a maximum of three cities from the city table for each instructor that wishes to be on the website , on an Instructor Info form? Is there an easier way to solve this...as I said, I am really new to access, and could use your experience, Graham!!! Many, many Thanks, Billiam |
#19
|
|||
|
|||
Address Design Structure
Hi Graham,
Thanks again for the great advice. I had read somewhere else that a city "could be in 2 different countries, and so was confused...thanks for clearing that up. I really appreciate you sticking with me on this, and for your timely responses! I will keep plugging away at it. Have a great day, Billiam "Graham Mandeno" wrote: Hi Billiam Like I said, you can go overboard with normalisation! As far as I know, a city can NOT be in two countries. The London in England is not the same as the London in Ontario, Canada. They bear no more relationship to one another that my uncle George to George Bush. So, if you have a table of Cities, London would have two records, distinguishable by other fields such as StateProvinceCounty and/or Country. You can also fall into the trap of making a box that requires many objects to be distorted in order to fit it. For example, some countries do not have any states/provinces/counties. Also, some countries have a concept of a suburb within a city (while smaller towns don't have suburbs) and other countries have the concept of a suburb or town as being the "city" within a state. It's difficult to provide a definitive answer! I can only caution you against (a) over-normalising and (b) creating a design that doesn't fit all cases. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... Thanks again for your time and advice. I will definately look at the Ken Snell database, thanks for that link. On the topic of Address structure, I am wondering if the following is the correct way to design the following as i seem to be getting overwhelmed a bit by the realtionships: City, ZipPostalCode, ProvinceStateCounty, Country One City can have many ZipPostalCode One ZipPostalCode can have many cities Therefore a many to many relationship which I would solve using a junction table Called CityZipPostalCode. I am really unsure what to do next, Graham. Do I need to define a relationship further between city and StateProvinceCounty, as 1 city can be in more than one StateProvinceCounty and one StateProvinceCounty can have many cities...If I do, is this another junction table??? Also, one city can be in many countries and one country can have many cities??? Finally, how and where do I relate StateProvinceCounty to the Countries table... as i do not know if many StateProvinceCounty are in many Countries (should have studued Geography more ,g see ideas spinning around in mostly empty cranial cavity causing what little brain there is *PAIN* Note: I do search on city quite a bit, so figure it is important to set this up right, finally! Billiam "Graham Mandeno" wrote: Hi Billiam What I usually do is add a PreferredName field. Ninety percent of the time this is blank, in which case FirstName is used. It handles the problem of diminutives as well as people who are known by a middle name. You can go overboard with normalising. I would not pull a field with a repeating value out into another table unless it truly represents another entity that could be used for grouping and selection. Even though you might have hundreds of records in a Persons table with FirstName "John", this common attribute does not relate these records to each other in any logical way, so there is no sense in having a separate table of FirstNames. However, the cities in which an instructor teaches is an attribute that could well be used to relate instructor records, so it makes sense to have a separate table. It should be many-to-many, by the way, just like Qualifications. You don't want to limit an instructor to three cities because of the restriction in your design. I have written a sample database demonstrating an easy way to manage many-to-many relationships, using listboxes and combo boxes on a form. My colleague, Ken Snell, has kindly put this up on his website at http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of the page, in the section "Easy Maintenance of "Many-To-Many" Data with a Form". I suggest you download it and see if it will serve your purpose. Don't worry too much about the code in the class module named "MtoMListHandler" - just look at the forms. Then you can import MtoMListHandler into your database and set up your forms in a similar way. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Billiam" wrote in message ... Thanks for sticking with me Graham! My answers are inline. "Graham Mandeno" wrote: Hmmm... this adds a whole new dimension. Actually, names can change for other reasons too, such as marriage. Just thinking by the seat of my pants here, I'm wondering if it would be better to create a new instructor record when one of the pieces of basic information (name/gender) changes, with a DateSuperseded field, so that all historic records can be linked to the current record. What do you think? Graham, I think that sounds great, but before I start changing things I also want you to be aware of another problem I have. Some Instructors wish to have their information posted on our website (about 60 percent of them). The information that is posted is: First and Last name, Phone#, Cities they teach in, an e-mail address, and a website link, and their qualifications. (BTW, I have tblQualifications in a many to many with tblnstructors and a junction table InstructorQualifications). Here is my problem...instructors must be entered in by their Given names, and as you probably know, not everyone uses their given name as their first name. Also, the cities entered in for address are not necessarily where the instructor teaches. As you can probably see, then, it is difficult to build the report to export to the website of all the instructors that wish to be on the public website. In my non-normalized database, I actually had a different table called "PublicInfo", but obviously there is a lot of stuff being repeated doing it that way. I know it sounds ridiculous, but should I have a first names table? If I have a seperate table of cities, could I somehow use a kind of multiple combo box to choose a maximum of three cities from the city table for each instructor that wishes to be on the website , on an Instructor Info form? Is there an easier way to solve this...as I said, I am really new to access, and could use your experience, Graham!!! Many, many Thanks, Billiam |
|
Thread Tools | |
Display Modes | |
|
|