A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup fields referring to tbls w/o relationship



 
 
Thread Tools Display Modes
  #1  
Old January 7th, 2010, 06:00 PM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Lookup fields referring to tbls w/o relationship

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
  #2  
Old January 7th, 2010, 08:16 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup fields referring to tbls w/o relationship

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" wrote in message
...
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



  #3  
Old January 7th, 2010, 09:56 PM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Lookup fields referring to tbls w/o relationship

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" wrote in message
...
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



.

  #4  
Old January 8th, 2010, 02:28 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup fields referring to tbls w/o relationship

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" wrote in message
...
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" wrote in message
...
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



.



  #5  
Old January 8th, 2010, 05:09 AM posted to microsoft.public.access.tablesdbdesign
Pamela
external usenet poster
 
Posts: 193
Default Lookup fields referring to tbls w/o relationship

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" wrote in message
...
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" wrote in message
...
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


.



.

  #6  
Old January 8th, 2010, 05:52 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup fields referring to tbls w/o relationship

Whether you explicitly draw the relationship to Access' attention or not,
the relationship exists.

I'm not sure I understand the issue you're describing/attributing to
combobox use, but if you KNOW there's a relationship among tables, why not
let Access in on it...?

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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


.



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.