confusing relationships

Thanks David for your reply. I have just seen and posted my reply to Vincent,
i will try to do the things that you guys suggested.

David M C wrote:
Your contact table would have an AddressID field. You would then have another
table containing all the address information. This would also have an
AddressID field which will be the primary key. You then create a relationship
between the two tables (open the relationships window, add the two tables and
drag one AddressID field on to the other, click "enforce referential
integrity".

Each contact in the same household would have the same AddressID.

Dave

Please see inline..

[quoted text clipped - 41 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Yes Tom,

I will implement the suggested design from Vincent and put the screenshot of
the final design on the web for others to see.

Thanks,

kingnothing

Tom Ellison wrote:
Dear Kingnothing:

I have been looking at this site. Would you consider making it more useful?

Please make the width and height of the table rectangles large enough to be
able to see the width and height for all the columns and their names.

What I said about combining contact, spouse, and child into a single table
still stands, tentatively, as I have not really seen your design yet.
Please reveal it as I requested and post back here if you're interested.

Tom Ellison

Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design, right!!
[quoted text clipped - 32 lines]

kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

This might seem a little dumb (Ever after you explainin in such detail) but
here goes...

This question has always plagued me for ages...

Do you do the relationships in the tbles (as in right click on tables section
and do the relationships) or do you leave the tables there and create a query
in design view, add all the tables, and create relationships there?? I'm
talking about the major relationships that are a part of your design...
Like for example, in your answer, do i just create tables and forget abt the
relationships and then create a select query and do all the relationships
there??? I dont get it...

PS: I will put up the db once it is done...so that other people can have easy
access to it and learn from it..

Help Vincent!!

Regards,

kingnothing

Vincent Johns wrote:
Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

-- Vincent Johns
Please feel free to quote anything I say here.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.
[quoted text clipped - 10 lines]
did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Dear Vincent:

Create relationships in the relationship diagram. Start with a drag and
drop between the related columns in the two tables involved.

As I have seen your diagram, I would think this would be familiar if you
have done the work so far.

Tom Ellison


"kingnothing via AccessMonster.com" u18754@uwe wrote in message
news:5ddc5d11d9439@uwe...
This might seem a little dumb (Ever after you explainin in such detail)
but
here goes...

This question has always plagued me for ages...

Do you do the relationships in the tbles (as in right click on tables
section
and do the relationships) or do you leave the tables there and create a
query
in design view, add all the tables, and create relationships there?? I'm
talking about the major relationships that are a part of your design...
Like for example, in your answer, do i just create tables and forget abt
the
relationships and then create a select query and do all the relationships
there??? I dont get it...

PS: I will put up the db once it is done...so that other people can have
easy
access to it and learn from it..

Help Vincent!!

Regards,

kingnothing

Vincent Johns wrote:
Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

-- Vincent Johns
Please feel free to quote anything I say here.

Thanks a lot Vincent, I really appreciate all the work that has gone
into
this reply. It is really helping me a lot.
[quoted text clipped - 10 lines]
did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Thanks Vincent,

Thats what i meant, you can achieve Tools -- Relationships by right clicking
on an empty space in the sections where the tables live.

I am in the process of completely revamping the design, i will let you know
how i go

Regards,

kingnothing

Vincent Johns wrote:
Tom Ellison's advice is good, but actually you can create default
relationships in the Relationships window, and you can override them in
the Query Design View window. In some cases, you might even get by
without defining anything at all in the Relationships window -- but,
like Tom, I suggest that you set up as much as you can there first. If
you do so, then when you define Queries based on the related Tables, the
relationships will magically appear in Query Design View (saving you
some work). You can then alter or delete them, or add new ones, in
Query Design View (but you won't be able to specify Referential
Integrity there).

To answer your question, yes, you do need to define Tables, including at
least some of the fields in them, before you can define relationships
among those fields.

Incidentally, I'm not sure what you mean by "right click on tables
section". In my versions of Access, the Relationships window is
accessed via the Tools -- Relationships menu, and you can add any or
all of the Tables in your database to that window.

This might seem a little dumb (Ever after you explainin in such detail) but
here goes...
[quoted text clipped - 50 lines]

[...]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Now, I would not have called a JOIN created in a query a relationship.
Relationships are part of table design and are enforced. JOINs can be made
on enforced relationships or not.

Relationships are always based on columns in two different tables that are
exactly identical. Queries can use non-equi JOINs that are in no way
relationships. So, while the two overlap in many cases, they are not
equivalent.

Tom Ellison


"kingnothing via AccessMonster.com" u18754@uwe wrote in message
news:5dddaa133141e@uwe...
Thanks Vincent,

Thats what i meant, you can achieve Tools -- Relationships by right
clicking
on an empty space in the sections where the tables live.

I am in the process of completely revamping the design, i will let you
know
how i go

Regards,

kingnothing

Vincent Johns wrote:
Tom Ellison's advice is good, but actually you can create default
relationships in the Relationships window, and you can override them in
the Query Design View window. In some cases, you might even get by
without defining anything at all in the Relationships window -- but,
like Tom, I suggest that you set up as much as you can there first. If
you do so, then when you define Queries based on the related Tables, the
relationships will magically appear in Query Design View (saving you
some work). You can then alter or delete them, or add new ones, in
Query Design View (but you won't be able to specify Referential
Integrity there).

To answer your question, yes, you do need to define Tables, including at
least some of the fields in them, before you can define relationships
among those fields.

Incidentally, I'm not sure what you mean by "right click on tables
section". In my versions of Access, the Relationships window is
accessed via the Tools -- Relationships menu, and you can add any or
all of the Tables in your database to that window.

This might seem a little dumb (Ever after you explainin in such detail)
but
here goes...
[quoted text clipped - 50 lines]

[...]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

Hi Vincent, and all of you...thank you very much for helping me through. The
concepts have finally got into my head.
I have designed just the basic stuff and have put a screen shot here --
http://members.westnet.com.au/mukund/rel1.jpg

Person_1 == Inner Join for Spouse
Person_2 == Inner Join for Child

I hope everything is alright with this....

I could not enforce ref int as it was not allowing me to add data.

I realise this is just the begining....i will still need your help for the
later stages

Regards,

Vincent Johns wrote:
Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

-- Vincent Johns
Please feel free to quote anything I say here.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.
[quoted text clipped - 10 lines]
did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200603/1

I do have a couple of comments about your design.

Of course, you may name your fields whatever you wish, but I usually
give a primary key the same name as its Table, followed by "_ID" (and
avoid ending non-key fields with "ID"), and you are already doing that.
In addition, I give each foreign key a name that either matches that
of the matching primary key, or if there are two or more such in the
same Table, a name that ends with the primary key's name. So, instead
of key names like [Insurance].[Insurance_Person_ID], I would use
something more like [Insurance].[Contact_Person_ID],
[Insurance].[Spouse_Person_ID], or [Insurance].[Parent_Person_ID]. You
already know that these foreign keys are located in the [Insurance]
Table, so I think it's not necessary to repeat that information. The
"Person_ID" part of each name tells you that the key is supposed to
match the primary key in the [Person] Table.

More significant than the names you choose (though I think well-chosen
names can help considerably) is what each field means, how it relates to
the real world. What is the meaning of your
[Insurance].[Insurance_Child_ID] field? Since there is only one such
field in each [Insurance] record, I assume that it represents the
(unique) person who is a child who is associated with that policy; i.e.,
one policy in that Table cannot be associated with two children. Is
this what you want? Is it possible for there to be a record in
[Insurance] in which both the [Insurance].[Insurance_Child_ID] and
[Insurance].[Insurance_Spouse_ID] fields have non-null values? This
would mean that one policy covers both a spouse and a child, I suppose,
even though it cannot cover a spouse and two children. If you don't
want both fields to have non-null values in the same record, you can
write a Query which looks for that, but there might be an easier way to
enforce that restriction. (Since I don't know what your business rules
are, I'm just guessing here, but here is an area where clearly named
fields can call attention to places where they are used inconsistently.)

I have the same comment about the [Address] Table. Are you sure that
you want a one-child policy enforced for addresses, as you have done
here? There would, however, be no problem that I can see with having
both a spouse and a child attached to a given address.

My guess is that some of your links are backward -- for example, putting
a [Person].[Address_ID] into each [Person] record would allow you to
attach an address to everyone (even children), though you could leave
some empty to indicate, for example, that your last letter there was
returned as undeliverable. As you have it, you can list several
addresses for each child, or several insurance policies for a given
spouse, but I'm not convinced that that makes sense.

-- Vincent Johns
Please feel free to quote anything I say here.

kingnothing via AccessMonster.com wrote:
Hi Vincent, and all of you...thank you very much for helping me through. The
concepts have finally got into my head.
I have designed just the basic stuff and have put a screen shot here --
http://members.westnet.com.au/mukund/rel1.jpg

Person_1 == Inner Join for Spouse
Person_2 == Inner Join for Child

I hope everything is alright with this....

I could not enforce ref int as it was not allowing me to add data.

I realise this is just the begining....i will still need your help for the
later stages

Regards,

Vincent Johns wrote:

Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

-- Vincent Johns
Please feel free to quote anything I say here.


Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

[quoted text clipped - 10 lines]

did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

I'm Back,

This time, i have the completed product with me. I thank all of you for
providing invaluable help.

Find it here -- http://members.westnet.com.au/mukund/insurance.zip

This is no way the finished product, but i'm nearly there. I need to do a
fair bit with regards to reports and stuff, also have included a switchboard
(autogenerated) just for the heck of it.

Please review it and let me know...

Thanks
kingnothing

Vincent Johns wrote:
I do have a couple of comments about your design.

Of course, you may name your fields whatever you wish, but I usually
give a primary key the same name as its Table, followed by "_ID" (and
avoid ending non-key fields with "ID"), and you are already doing that.
In addition, I give each foreign key a name that either matches that
of the matching primary key, or if there are two or more such in the
same Table, a name that ends with the primary key's name. So, instead
of key names like [Insurance].[Insurance_Person_ID], I would use
something more like [Insurance].[Contact_Person_ID],
[Insurance].[Spouse_Person_ID], or [Insurance].[Parent_Person_ID]. You
already know that these foreign keys are located in the [Insurance]
Table, so I think it's not necessary to repeat that information. The
"Person_ID" part of each name tells you that the key is supposed to
match the primary key in the [Person] Table.

More significant than the names you choose (though I think well-chosen
names can help considerably) is what each field means, how it relates to
the real world. What is the meaning of your
[Insurance].[Insurance_Child_ID] field? Since there is only one such
field in each [Insurance] record, I assume that it represents the
(unique) person who is a child who is associated with that policy; i.e.,
one policy in that Table cannot be associated with two children. Is
this what you want? Is it possible for there to be a record in
[Insurance] in which both the [Insurance].[Insurance_Child_ID] and
[Insurance].[Insurance_Spouse_ID] fields have non-null values? This
would mean that one policy covers both a spouse and a child, I suppose,
even though it cannot cover a spouse and two children. If you don't
want both fields to have non-null values in the same record, you can
write a Query which looks for that, but there might be an easier way to
enforce that restriction. (Since I don't know what your business rules
are, I'm just guessing here, but here is an area where clearly named
fields can call attention to places where they are used inconsistently.)

I have the same comment about the [Address] Table. Are you sure that
you want a one-child policy enforced for addresses, as you have done
here? There would, however, be no problem that I can see with having
both a spouse and a child attached to a given address.

My guess is that some of your links are backward -- for example, putting
a [Person].[Address_ID] into each [Person] record would allow you to
attach an address to everyone (even children), though you could leave
some empty to indicate, for example, that your last letter there was
returned as undeliverable. As you have it, you can list several
addresses for each child, or several insurance policies for a given
spouse, but I'm not convinced that that makes sense.

-- Vincent Johns
Please feel free to quote anything I say here.

Hi Vincent, and all of you...thank you very much for helping me through. The
concepts have finally got into my head.
[quoted text clipped - 45 lines]

[...]

--
Message posted via http://www.accessmonster.com