Relationship

P

PL

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
 
C

Crystal (strive4peace)

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 :)
*
 
P

PL

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
 
P

PL

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
 
C

Crystal (strive4peace)

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 :)
*
 
M

MS

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..
 
J

John W. Vinson

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..
Click to expand...

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.
 
M

MS

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..
 
J

John W. Vinson

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..
Click to expand...

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.
 
M

MS

here it is..
Private Sub Command41_Click()

Client = Me.ClientCombo.Value
dureeTOTAL = DLookup("TOTAL", "client_total", "Client='" & Client & "'")
Me.txtdureeTOTAL.Value = dureeTOTAL

'RECHERCHER LA DATE DE FABRICATION
dateDebutfab = DLookup("DB", "client_date", "Client='" & Client & "'")
dateFinfab = DLookup("DF", "client_date", "Client='" & Client & "'")
Me.txtdatefab.Value = dateDebutfab & " à " & dateFinfab

'RECHERCHER L'HEURE TOTALE DE PRODUCTION SUIVANT LES CLIENTS
prodTOTAL = DLookup("TOTAL", "client_tache", "Client='" & Client & "' AND
numT=1")
'prodTOTAL1 = DLookup("TOTAL", "MOCN_total", "Client='" & Client & "'")
'prodTOTAL2 = DLookup("TOTAL", "client_total", "Client='" & Client & "'")
Me.txtprod.Value = prodTOTAL

'RECHERCHER L'HEURE TOTALE DE NON PRODUCTION SUIVANT LES CLIENTS
nonprodTOTAL = DLookup("TOTAL", "client_tache", "Client='" & Client & "' AND
numT=2")
Me.txtnonprod.Value = nonprodTOTAL



'CALCULER LE TRP DE PRODUCTION
Me.txtTRP.Value = (prodTOTAL / dureeTOTAL)

'CALCULER LE NON TRP
Me.txtNONTRP.Value = (nonprodTOTAL / dureeTOTAL)

'RECHERCHER LE TONNAGE POUR CHAQUE CLIENT ET CALCULER HEURE/TONNE
tonnage = DLookup("Tonnage", "client_tonnage", "Client='" & Client & "'")
Me.txt_tonnage.Value = (dureeTOTAL / tonnage)

'If (Me.txtNONTRP.Value = Null) Then Me.txtNONTRP.Value = 0

End Sub

i dont know is this helpful..
thank you in advance.
 
M

MS

HI, does any one can help me?

I want to make appear the value 0 when its null. How can i write the code in
VBA? i tried this one but nothing happens. Your help will very much
appreciated.

If prod_TOTAL = Null Then
Me.txt_Prod.Value = "0"
End If
 
J

John W. Vinson

HI, does any one can help me?

I want to make appear the value 0 when its null. How can i write the code in
VBA? i tried this one but nothing happens. Your help will very much
appreciated.

If prod_TOTAL = Null Then
Me.txt_Prod.Value = "0"
End If
Click to expand...

Null is a funny beast. It means "undefined, unknown, unspecified". As such
nothing is equal to NULL, or for that matter unequal to NULL - you don't know
what it is, so there's no way to decide!

The solution is to use either the NZ - Null To Zero - function, or the
IsNull() function:

Me.txt_Prod= NZ(prod_TOTAL, "0")

will return a text string zero to the txt_PROD control (if the field is
numeric rather than text, leave off the quote marks).

In other cases you can use the IsNull() VBA function:

If IsNull(prod_TOTAL) Then
<do something>
Else
<do something else>
End If

This will only work if prod_TOTAL is a Variant (a datatype which accepts
nulls).
 
M

MS

HI,
can somebody please help me. Every time I want to create a subform in a form
a notice pop out, it says: For loop not initialized.

I don't know why is this happening so suddenly but i cannot make subforms
that i want.
The same thing happens when i click on analyze in the ribbon.
Your help will be much appreciated.
 
M

MS

Im sorry, i dont understand what are you trying to say.. Could you specify
me more about it?
Thank you
 
L

Larry Daugherty

Look in Help for "For ... Next". Help is where I found soooo many
"secrets".

example:

dim x as integer

For x = 1 to 10
do something
next

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Create an S-Curve from Custom Field? 0
Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Section and Page numbering 0
Adding sequence numbers to paragraphs 0
Found few bugs in my daily routine of work in excel for MAC 0
Found few bugs in my daily routine of work in excel for MAC 0
Document Merge Formatting Issue 0
How to break up a book's endnotes by chapter in a file containing a whole book? 0

Top