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  

Relationship



 
 
Thread Tools Display Modes
  #1  
Old April 9th, 2009, 07:21 AM posted to microsoft.public.access.tablesdbdesign
PL
external usenet poster
 
Posts: 104
Default Relationship

For Access 2003,

1) What''s the difference betwwen the 3 options in the "join type" section?

2) What''s "cascade update related field"?

Thanks
  #2  
Old April 9th, 2009, 10:15 AM posted to microsoft.public.access.tablesdbdesign
Crystal (strive4peace)
external usenet poster
 
Posts: 27
Default Relationship

HI PL

Join Types:

Lets say you have the following tables:

1. People -- PID, LastName, FirstName
2. Phones -- PhoneID, PID, Phone

Lets also assume not everybody has a Phone

If you want a list of Names and Phone Numbers, you would do the first
option, just show records where the joined fields are equal. The
resulting data would only show People that HAVE a phone number (and
phone numbers that have a person)

If you want a list of everybody, regardless of whether or not they have
a phone number, you would choose to show all records from People and
just those records from Phones where the joined fields are equal.

options 2 and 3 are essentially the same -- they show all the records
from one table regardless of whether the other table has matching
record. The reason there are 2 of them is because there are 2 tables

~~~~~~~~~~~~~~`

"cascade update related field"

if you are not using autonumber for primary keys with a long integer
related key -- but instead are inputting natural keys, such as a
ProductCode -- then, if you have Cascade Update Related Fields chosen on
the relationship type, then if you change a ProductCode in the parent
table, that change will also be made in the related table



Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
(: have an awesome day
*


PL wrote:
For Access 2003,

1) What''s the difference betwwen the 3 options in the "join type" section?

2) What''s "cascade update related field"?

Thanks

  #3  
Old April 11th, 2009, 07:25 AM posted to microsoft.public.access.tablesdbdesign
PL
external usenet poster
 
Posts: 104
Default Relationship

Hi Crystal,

Thanks for the reply.

But will the Join Type affect the data in the 2 tables? I tried different
join types but the data in both tables remain the same.

Thank you

"Crystal (strive4peace)" wrote:

HI PL

Join Types:

Lets say you have the following tables:

1. People -- PID, LastName, FirstName
2. Phones -- PhoneID, PID, Phone

Lets also assume not everybody has a Phone

If you want a list of Names and Phone Numbers, you would do the first
option, just show records where the joined fields are equal. The
resulting data would only show People that HAVE a phone number (and
phone numbers that have a person)

If you want a list of everybody, regardless of whether or not they have
a phone number, you would choose to show all records from People and
just those records from Phones where the joined fields are equal.

options 2 and 3 are essentially the same -- they show all the records
from one table regardless of whether the other table has matching
record. The reason there are 2 of them is because there are 2 tables

~~~~~~~~~~~~~~`

"cascade update related field"

if you are not using autonumber for primary keys with a long integer
related key -- but instead are inputting natural keys, such as a
ProductCode -- then, if you have Cascade Update Related Fields chosen on
the relationship type, then if you change a ProductCode in the parent
table, that change will also be made in the related table



Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
(: have an awesome day
*


PL wrote:
For Access 2003,

1) What''s the difference betwwen the 3 options in the "join type" section?

2) What''s "cascade update related field"?

Thanks


  #4  
Old April 11th, 2009, 12:56 PM posted to microsoft.public.access.tablesdbdesign
Crystal (strive4peace)
external usenet poster
 
Posts: 27
Default Relationship

you're welcome, PL (what is your name?)

Join Types have no effect on the data, only on the display

Why are you asking this question? Please give an example of what you
are hoping to do

Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
(: have an awesome day
*


PL wrote:
Hi Crystal,

Thanks for the reply.

But will the Join Type affect the data in the 2 tables? I tried different
join types but the data in both tables remain the same.

Thank you

"Crystal (strive4peace)" wrote:

HI PL

Join Types:

Lets say you have the following tables:

1. People -- PID, LastName, FirstName
2. Phones -- PhoneID, PID, Phone

Lets also assume not everybody has a Phone

If you want a list of Names and Phone Numbers, you would do the first
option, just show records where the joined fields are equal. The
resulting data would only show People that HAVE a phone number (and
phone numbers that have a person)

If you want a list of everybody, regardless of whether or not they have
a phone number, you would choose to show all records from People and
just those records from Phones where the joined fields are equal.

options 2 and 3 are essentially the same -- they show all the records
from one table regardless of whether the other table has matching
record. The reason there are 2 of them is because there are 2 tables

~~~~~~~~~~~~~~`

"cascade update related field"

if you are not using autonumber for primary keys with a long integer
related key -- but instead are inputting natural keys, such as a
ProductCode -- then, if you have Cascade Update Related Fields chosen on
the relationship type, then if you change a ProductCode in the parent
table, that change will also be made in the related table



Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
(: have an awesome day
*


PL wrote:
For Access 2003,

1) What''s the difference betwwen the 3 options in the "join type" section?

2) What''s "cascade update related field"?

Thanks

  #5  
Old April 12th, 2009, 08:41 AM posted to microsoft.public.access.tablesdbdesign
PL
external usenet poster
 
Posts: 104
Default Relationship

Hi Crystal,

I am Pearl.

I am not trying to do achieve anything specifically. But I would just like
to learn more about the various functions of Access.

So what do you mean when you say "join type" affect the display? What's
display?
I tried the various join type but the data in the 2 table still remain the
same.

Thank you

"Crystal (strive4peace)" wrote:

you're welcome, PL (what is your name?)

Join Types have no effect on the data, only on the display

Why are you asking this question? Please give an example of what you
are hoping to do

Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
(: have an awesome day
*


PL wrote:
Hi Crystal,

Thanks for the reply.

But will the Join Type affect the data in the 2 tables? I tried different
join types but the data in both tables remain the same.

Thank you

"Crystal (strive4peace)" wrote:

HI PL

Join Types:

Lets say you have the following tables:

1. People -- PID, LastName, FirstName
2. Phones -- PhoneID, PID, Phone

Lets also assume not everybody has a Phone

If you want a list of Names and Phone Numbers, you would do the first
option, just show records where the joined fields are equal. The
resulting data would only show People that HAVE a phone number (and
phone numbers that have a person)

If you want a list of everybody, regardless of whether or not they have
a phone number, you would choose to show all records from People and
just those records from Phones where the joined fields are equal.

options 2 and 3 are essentially the same -- they show all the records
from one table regardless of whether the other table has matching
record. The reason there are 2 of them is because there are 2 tables

~~~~~~~~~~~~~~`

"cascade update related field"

if you are not using autonumber for primary keys with a long integer
related key -- but instead are inputting natural keys, such as a
ProductCode -- then, if you have Cascade Update Related Fields chosen on
the relationship type, then if you change a ProductCode in the parent
table, that change will also be made in the related table



Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
(: have an awesome day
*


PL wrote:
For Access 2003,

1) What''s the difference betwwen the 3 options in the "join type" section?

2) What''s "cascade update related field"?

Thanks


  #6  
Old April 13th, 2009, 01:45 AM posted to microsoft.public.access.tablesdbdesign
Crystal (strive4peace)
external usenet poster
 
Posts: 27
Default Relationship

Hi Pearl,

do you get a different number of records when you try different join
types? The only time you would notice is if one of the key fields is not
filled out or there is no match -- perhaps all of your information is
filled and you get the same number of records either way.

read this:

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

there are lots of screen shots!

Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal



*
(: have an awesome day
*


PL wrote:
Hi Crystal,

I am Pearl.

I am not trying to do achieve anything specifically. But I would just like
to learn more about the various functions of Access.

So what do you mean when you say "join type" affect the display? What's
display?
I tried the various join type but the data in the 2 table still remain the
same.

Thank you

  #7  
Old April 15th, 2009, 12:48 PM posted to microsoft.public.access.tablesdbdesign
MS
external usenet poster
 
Posts: 76
Default combobox

I am an access 2007 user and having a problem using it. Here is it, in my
form, i have a combo box which lists all name of workers, and another two
forms with combo boxes in each one which lists name of the clients and
machines. now, let say im working on the form which has all the names of the
clients in a combo box. i click on one of the workers and launch a
calculation which i have made using VBA, all the results that i expect pop
out, but when i close the form, the worker that i just chose just now REPLACE
the other worker randomly.. seriously i dont how to solve this problem and it
has been really problematic.. i wish you could help me.. thank you very much
for your help..


"Crystal (strive4peace)" wrote:

HI PL

Join Types:

Lets say you have the following tables:

1. People -- PID, LastName, FirstName
2. Phones -- PhoneID, PID, Phone

Lets also assume not everybody has a Phone

If you want a list of Names and Phone Numbers, you would do the first
option, just show records where the joined fields are equal. The
resulting data would only show People that HAVE a phone number (and
phone numbers that have a person)

If you want a list of everybody, regardless of whether or not they have
a phone number, you would choose to show all records from People and
just those records from Phones where the joined fields are equal.

options 2 and 3 are essentially the same -- they show all the records
from one table regardless of whether the other table has matching
record. The reason there are 2 of them is because there are 2 tables

~~~~~~~~~~~~~~`

"cascade update related field"

if you are not using autonumber for primary keys with a long integer
related key -- but instead are inputting natural keys, such as a
ProductCode -- then, if you have Cascade Update Related Fields chosen on
the relationship type, then if you change a ProductCode in the parent
table, that change will also be made in the related table



Warm Regards,
Crystal
remote programming and training

http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page tutorial that covers essentials in Access

*
(: have an awesome day
*


PL wrote:
For Access 2003,

1) What''s the difference betwwen the 3 options in the "join type" section?

2) What''s "cascade update related field"?

Thanks


  #8  
Old April 15th, 2009, 06:13 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default combobox

On Wed, 15 Apr 2009 04:48:01 -0700, MS wrote:

I am an access 2007 user and having a problem using it. Here is it, in my
form, i have a combo box which lists all name of workers, and another two
forms with combo boxes in each one which lists name of the clients and
machines. now, let say im working on the form which has all the names of the
clients in a combo box. i click on one of the workers and launch a
calculation which i have made using VBA, all the results that i expect pop
out, but when i close the form, the worker that i just chose just now REPLACE
the other worker randomly.. seriously i dont how to solve this problem and it
has been really problematic.. i wish you could help me.. thank you very much
for your help..


It sounds like you're using a *bound* combo box. A combo box on a form has a
Control Source property, which may be blank; if it's NOT blank, then the value
of the combo's Bound Column will replace the value in the Control Source field
in the table.

If you're not using the combo to edit data in the table, but rather for some
sort of calculation, leave the combo box *unbound* - i.e. nothing in its
Control Source.
--

John W. Vinson [MVP]
  #9  
Old April 15th, 2009, 11:21 PM posted to microsoft.public.access.tablesdbdesign
MS
external usenet poster
 
Posts: 76
Default combobox

Thank you for your reply, but i dont think its because of bound or unbound
control. I have changed it to unbound but that doesn't change anything, the
name still replace with another one in the combo list.. Do you have any other
idea?
thank you..

"John W. Vinson" wrote:

On Wed, 15 Apr 2009 04:48:01 -0700, MS wrote:

I am an access 2007 user and having a problem using it. Here is it, in my
form, i have a combo box which lists all name of workers, and another two
forms with combo boxes in each one which lists name of the clients and
machines. now, let say im working on the form which has all the names of the
clients in a combo box. i click on one of the workers and launch a
calculation which i have made using VBA, all the results that i expect pop
out, but when i close the form, the worker that i just chose just now REPLACE
the other worker randomly.. seriously i dont how to solve this problem and it
has been really problematic.. i wish you could help me.. thank you very much
for your help..


It sounds like you're using a *bound* combo box. A combo box on a form has a
Control Source property, which may be blank; if it's NOT blank, then the value
of the combo's Bound Column will replace the value in the Control Source field
in the table.

If you're not using the combo to edit data in the table, but rather for some
sort of calculation, leave the combo box *unbound* - i.e. nothing in its
Control Source.
--

John W. Vinson [MVP]

  #10  
Old April 16th, 2009, 12:23 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default combobox

On Wed, 15 Apr 2009 15:21:01 -0700, MS wrote:

Thank you for your reply, but i dont think its because of bound or unbound
control. I have changed it to unbound but that doesn't change anything, the
name still replace with another one in the combo list.. Do you have any other
idea?
thank you..


Care to post your code? It's a bit hard to debug it without seeing it.

Indicating the Control Source, Rowsource, and Bound Column of the combo should
help too.
--

John W. Vinson [MVP]
 




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 12:53 AM.


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