Lookup fields referring to tbls w/o relationship
in [Tables]
|
From: Pamela on 7 Jan 2010 12:00 I created a db and in reading now about db structure, I've seen a lot of mention of relationships. My db started out very simple with the tables that I used for relationships all being one-to-one and now I'm considering adding to it. In it, however, I also have some tables that I used (not w/ the Lookup Wizard but on my own) simply as lookup - populating cbos. I didn't seem to need to set relationships with these though for it to work and work well. Why didn't it require a relationship and what are the pros & cons of setting them up this way vs. w/ a relationship? Thanks so much for your help! Pamela
From: Jeff Boyce on 7 Jan 2010 14:16 Pamela Access is a relational database. It is optimized to work with well-normalized data. It is somewhat rare to have a number of tables related one-to-one in a well-normalized relational database. That usually only happens when you have "sub-typing". For example, you could have a list of equipment at your work, but of different types, each with different characteristics (e.g., Computers, Desks, Chairs, Bookcases, ...). You might decide to create one table to "inventory" them all, giving each a unique inventoryID, but use separate tables to hold the particulars on each different class/type of equipment (i.e., chairs don't have the same characteristics that computers do). That's a one-to-one situation. The only one who knows if the things about which you are collecting data are related is you. Access can't tell that. We can't help with that unless you describe your situation a bit more. More info, please... 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. "Pamela" <Pamela(a)discussions.microsoft.com> wrote in message news:3920FBBF-CDD4-4067-AF48-57A6C377EF7D(a)microsoft.com... >I created a db and in reading now about db structure, I've seen a lot of > mention of relationships. My db started out very simple with the tables > that > I used for relationships all being one-to-one and now I'm considering > adding > to it. In it, however, I also have some tables that I used (not w/ the > Lookup Wizard but on my own) simply as lookup - populating cbos. I didn't > seem to need to set relationships with these though for it to work and > work > well. Why didn't it require a relationship and what are the pros & cons > of > setting them up this way vs. w/ a relationship? > Thanks so much for your help! > Pamela
From: Pamela on 7 Jan 2010 15:56 Hi Jeff, In my research, I understand that it is always best to have items in a table relate to 1 topic only. In the case of our business, we inspect cars that have been in an accident for the insurance companies. In the case of this db, the only purpose it was originally to serve was to take info about the inspection and concatenate it into a paragraph complete with our legal verbage, disclaimers etc., which we then have to paste into the insurance company's online system. With this in mind, I broke the tables down into these: tblClaim, tblInspection (date of, etc) , tblLocation (where it took place), tblDamage (classifying the damage, the measurements of, etc) and tblEstimate. For this purpose, I felt a one-to-one was best. Each claim has 1 inspection, 1 location, 1 set of damage info, 1 estimate, etc all joined around our central assignment # which is the PK on all of the tbls and which I use for the relationships. I have other tables that I use to populate my cbos and those are really from where my question stems. I have tblInsuranceCompany which populates cboInsuranceCompany but there is no direct relationship that I needed to make for it to work and work well, as I stated before. Is it sufficient to leave it as it is or if I decide to expand upon my db will I run into issues because it doesn't have a direct relationship established? Thanks so much! Pamela "Jeff Boyce" wrote: > Pamela > > Access is a relational database. It is optimized to work with > well-normalized data. > > It is somewhat rare to have a number of tables related one-to-one in a > well-normalized relational database. That usually only happens when you > have "sub-typing". For example, you could have a list of equipment at your > work, but of different types, each with different characteristics (e.g., > Computers, Desks, Chairs, Bookcases, ...). You might decide to create one > table to "inventory" them all, giving each a unique inventoryID, but use > separate tables to hold the particulars on each different class/type of > equipment (i.e., chairs don't have the same characteristics that computers > do). That's a one-to-one situation. > > The only one who knows if the things about which you are collecting data are > related is you. Access can't tell that. We can't help with that unless you > describe your situation a bit more. > > More info, please... > > 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. > > "Pamela" <Pamela(a)discussions.microsoft.com> wrote in message > news:3920FBBF-CDD4-4067-AF48-57A6C377EF7D(a)microsoft.com... > >I created a db and in reading now about db structure, I've seen a lot of > > mention of relationships. My db started out very simple with the tables > > that > > I used for relationships all being one-to-one and now I'm considering > > adding > > to it. In it, however, I also have some tables that I used (not w/ the > > Lookup Wizard but on my own) simply as lookup - populating cbos. I didn't > > seem to need to set relationships with these though for it to work and > > work > > well. Why didn't it require a relationship and what are the pros & cons > > of > > setting them up this way vs. w/ a relationship? > > Thanks so much for your help! > > Pamela > > > . >
From: Jeff Boyce on 7 Jan 2010 20:28 Pamela Are you saying that your database covers more than one insurance company? If so, it seems to me (*from the outside!*) that one insurance company could be related to many claims (Company A covering 1,000 claims, Company B covering 525 claims, etc.). If that describes your situation, the there IS a relationship between the InsuranceCompany and the Claim. Does that help? 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. "Pamela" <Pamela(a)discussions.microsoft.com> wrote in message news:A6B99EB1-3FB7-489F-BC38-39C4938FF45D(a)microsoft.com... > Hi Jeff, > > In my research, I understand that it is always best to have items in a > table > relate to 1 topic only. In the case of our business, we inspect cars that > have been in an accident for the insurance companies. In the case of this > db, the only purpose it was originally to serve was to take info about the > inspection and concatenate it into a paragraph complete with our legal > verbage, disclaimers etc., which we then have to paste into the insurance > company's online system. > > With this in mind, I broke the tables down into these: tblClaim, > tblInspection (date of, etc) , tblLocation (where it took place), > tblDamage > (classifying the damage, the measurements of, etc) and tblEstimate. For > this > purpose, I felt a one-to-one was best. Each claim has 1 inspection, 1 > location, 1 set of damage info, 1 estimate, etc all joined around our > central > assignment # which is the PK on all of the tbls and which I use for the > relationships. I have other tables that I use to populate my cbos and > those > are really from where my question stems. I have tblInsuranceCompany which > populates cboInsuranceCompany but there is no direct relationship that I > needed to make for it to work and work well, as I stated before. Is it > sufficient to leave it as it is or if I decide to expand upon my db will I > run into issues because it doesn't have a direct relationship established? > Thanks so much! > Pamela > > "Jeff Boyce" wrote: > >> Pamela >> >> Access is a relational database. It is optimized to work with >> well-normalized data. >> >> It is somewhat rare to have a number of tables related one-to-one in a >> well-normalized relational database. That usually only happens when you >> have "sub-typing". For example, you could have a list of equipment at >> your >> work, but of different types, each with different characteristics (e.g., >> Computers, Desks, Chairs, Bookcases, ...). You might decide to create >> one >> table to "inventory" them all, giving each a unique inventoryID, but use >> separate tables to hold the particulars on each different class/type of >> equipment (i.e., chairs don't have the same characteristics that >> computers >> do). That's a one-to-one situation. >> >> The only one who knows if the things about which you are collecting data >> are >> related is you. Access can't tell that. We can't help with that unless >> you >> describe your situation a bit more. >> >> More info, please... >> >> 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. >> >> "Pamela" <Pamela(a)discussions.microsoft.com> wrote in message >> news:3920FBBF-CDD4-4067-AF48-57A6C377EF7D(a)microsoft.com... >> >I created a db and in reading now about db structure, I've seen a lot of >> > mention of relationships. My db started out very simple with the >> > tables >> > that >> > I used for relationships all being one-to-one and now I'm considering >> > adding >> > to it. In it, however, I also have some tables that I used (not w/ the >> > Lookup Wizard but on my own) simply as lookup - populating cbos. I >> > didn't >> > seem to need to set relationships with these though for it to work and >> > work >> > well. Why didn't it require a relationship and what are the pros & >> > cons >> > of >> > setting them up this way vs. w/ a relationship? >> > Thanks so much for your help! >> > Pamela >> >> >> . >>
From: Pamela on 7 Jan 2010 23:09
Hi Jeff, I must not be phrasing my ultimate question well...so sorry. I recognize that there COULD be...and yes, that would be a one-to-many - we deal w/ multiple insurance companies all of which have multiple claims. But how does it work with combo boxes like that that I didn't need a relationship between Claim & InsuranceCompany and what are the benefits of setting up that relationship? How will my data work differently with that relationship established and am I making those relationships a bigger deal than what they are? They always seem a little scary to me so if I can skip along without some of them, it's a little tempting but I don't want to hinder my db for the future. Thanks so much for clarifying this for me! Pamela "Jeff Boyce" wrote: > Pamela > > Are you saying that your database covers more than one insurance company? > If so, it seems to me (*from the outside!*) that one insurance company could > be related to many claims (Company A covering 1,000 claims, Company B > covering 525 claims, etc.). > > If that describes your situation, the there IS a relationship between the > InsuranceCompany and the Claim. > > Does that help? > > 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. > > "Pamela" <Pamela(a)discussions.microsoft.com> wrote in message > news:A6B99EB1-3FB7-489F-BC38-39C4938FF45D(a)microsoft.com... > > Hi Jeff, > > > > In my research, I understand that it is always best to have items in a > > table > > relate to 1 topic only. In the case of our business, we inspect cars that > > have been in an accident for the insurance companies. In the case of this > > db, the only purpose it was originally to serve was to take info about the > > inspection and concatenate it into a paragraph complete with our legal > > verbage, disclaimers etc., which we then have to paste into the insurance > > company's online system. > > > > With this in mind, I broke the tables down into these: tblClaim, > > tblInspection (date of, etc) , tblLocation (where it took place), > > tblDamage > > (classifying the damage, the measurements of, etc) and tblEstimate. For > > this > > purpose, I felt a one-to-one was best. Each claim has 1 inspection, 1 > > location, 1 set of damage info, 1 estimate, etc all joined around our > > central > > assignment # which is the PK on all of the tbls and which I use for the > > relationships. I have other tables that I use to populate my cbos and > > those > > are really from where my question stems. I have tblInsuranceCompany which > > populates cboInsuranceCompany but there is no direct relationship that I > > needed to make for it to work and work well, as I stated before. Is it > > sufficient to leave it as it is or if I decide to expand upon my db will I > > run into issues because it doesn't have a direct relationship established? > > Thanks so much! > > Pamela > > > > "Jeff Boyce" wrote: > > > >> Pamela > >> > >> Access is a relational database. It is optimized to work with > >> well-normalized data. > >> > >> It is somewhat rare to have a number of tables related one-to-one in a > >> well-normalized relational database. That usually only happens when you > >> have "sub-typing". For example, you could have a list of equipment at > >> your > >> work, but of different types, each with different characteristics (e.g., > >> Computers, Desks, Chairs, Bookcases, ...). You might decide to create > >> one > >> table to "inventory" them all, giving each a unique inventoryID, but use > >> separate tables to hold the particulars on each different class/type of > >> equipment (i.e., chairs don't have the same characteristics that > >> computers > >> do). That's a one-to-one situation. > >> > >> The only one who knows if the things about which you are collecting data > >> are > >> related is you. Access can't tell that. We can't help with that unless > >> you > >> describe your situation a bit more. > >> > >> More info, please... > >> > >> 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. > >> > >> "Pamela" <Pamela(a)discussions.microsoft.com> wrote in message > >> news:3920FBBF-CDD4-4067-AF48-57A6C377EF7D(a)microsoft.com... > >> >I created a db and in reading now about db structure, I've seen a lot of > >> > mention of relationships. My db started out very simple with the > >> > tables > >> > that > >> > I used for relationships all being one-to-one and now I'm considering > >> > adding > >> > to it. In it, however, I also have some tables that I used (not w/ the > >> > Lookup Wizard but on my own) simply as lookup - populating cbos. I > >> > didn't > >> > seem to need to set relationships with these though for it to work and > >> > work > >> > well. Why didn't it require a relationship and what are the pros & > >> > cons > >> > of > >> > setting them up this way vs. w/ a relationship? > >> > Thanks so much for your help! > >> > Pamela > >> > >> > >> . > >> > > > . > |