Still Working on tables

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

I'm still missing something which must be be very basic.
How does access know (or make the decision) what the relationship is
between two tables. Check this out ......
Company Table
pkCompanyID
CompanyName
Contacts Table
pkContactID
LastName
Trades Table
pkTradeID
Trade
CompanyContacts Table
pkCompanyID
pkContactID
Resposibility
CompanyTrade Table
pkCompanyID
pkTradeID

In the relationship window when I join the CompanyTable.pkCompanyID
and CompanyContacts.pkCompanyID access correctly (I believe) gives me
a 1:M, but when I join the similar fields relating Company and Trade,
access gives me a 1:1

What possible condition(s) would cause this?

Assume simple forms for each of above tables. With both
CompanyContacts and TradeContacts as subforms in the Company main
form, is there any reason that only one subform at a time links to the
pkCompanyID. Parent/Child show correctly on both subforms.

Thanks
 
TeeSee said:
I'm still missing something which must be be very basic.
How does access know (or make the decision) what the relationship is
between two tables. Check this out ......
Company Table
pkCompanyID
CompanyName
Contacts Table
pkContactID
LastName
Trades Table
pkTradeID
Trade
CompanyContacts Table
pkCompanyID
pkContactID
Resposibility
CompanyTrade Table
pkCompanyID
pkTradeID

In the relationship window when I join the CompanyTable.pkCompanyID
and CompanyContacts.pkCompanyID access correctly (I believe) gives me
a 1:M, but when I join the similar fields relating Company and Trade,
access gives me a 1:1

What possible condition(s) would cause this?
Click to expand...

A 1-1 relationship is where you are linking on the primary
keys (or at least unique fields) in both tables. This means
that the CompanyTrade table's primary key is probably
incorrect. It should be a compound key with both the
pkCompanyID and pkTradeID fields. Check the Indexes window
to make sure the primary key takes two lines for the two
fields.
Assume simple forms for each of above tables. With both
CompanyContacts and TradeContacts as subforms in the Company main
form, is there any reason that only one subform at a time links to the
pkCompanyID. Parent/Child show correctly on both subforms.
Click to expand...

That, I don't see happening. Double check to make sure the
LinkMaster/Child properties only use the CompanyID field.
 
TeeSee said:
I'm still missing something which must be be very basic.
How does access know (or make the decision) what the relationship is
between two tables. Check this out ......
Company Table
 pkCompanyID
 CompanyName
Contacts Table
 pkContactID
 LastName
Trades Table
 pkTradeID
 Trade
CompanyContacts Table
 pkCompanyID
 pkContactID
 Resposibility
CompanyTrade Table
 pkCompanyID
 pkTradeID
Click to expand...
In the relationship window when I join the CompanyTable.pkCompanyID
and CompanyContacts.pkCompanyID access correctly (I believe) gives me
a 1:M, but when I join the similar fields relating Company and Trade,
access gives me a 1:1
Click to expand...
What possible condition(s) would cause this?
Click to expand...

A 1-1 relationship is where you are linking on the primary
keys (or at least unique fields) in both tables.  This means
that the CompanyTrade table's primary key is probably
incorrect.  It should be a compound key with both the
pkCompanyID and pkTradeID fields.  Check the Indexes window
to make sure the primary key takes two lines for the two
fields.


Assume simple forms for each of above tables. With both
CompanyContacts and TradeContacts as subforms in the Company main
form, is there any reason that only one subform at a time links to the
pkCompanyID. Parent/Child show correctly on both subforms.
Click to expand...

That, I don't see happening.  Double check to make sure the
LinkMaster/Child properties only use the CompanyID field.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Marsh ... Thanks for the reply
As you know there are four columns in the indexes window key/nokey,
IndexName, FieldName, SortOrder. I will ignore SortOrder!
With tblCompanyTrade open in design view and the indexes wee window
showing here is the situation in the order of left to right indexes
w'dow.

key/nokey, IndexName, FieldName
blank, CompanyID, pkCompanyID
key, PrimaryKey, pkCompanyID
key, Blank, pkTradeID
blank, TradeID, pkTradeID
As you can see (I think) I do have a double PK

Any thoughts?
 
TeeSee said:
As you know there are four columns in the indexes window key/nokey,
IndexName, FieldName, SortOrder. I will ignore SortOrder!
With tblCompanyTrade open in design view and the indexes wee window
showing here is the situation in the order of left to right indexes
w'dow.

key/nokey, IndexName, FieldName
blank, CompanyID, pkCompanyID
key, PrimaryKey, pkCompanyID
key, Blank, pkTradeID
blank, TradeID, pkTradeID
As you can see (I think) I do have a double PK
Click to expand...


Actually, I didn't know that (A2003 dies not have the key
column). I presume you are using A2007 and I haven't done
more than a little playing around with it. I doubt that
makes a difference, unless you are using multi-valued
fields, so let's proceed

Is either the CompanyID or TradeID indexes marked as Unique?

Which fields between the two table are connected in the
relationship window?
 
As you know there are four columns in the indexes window key/nokey,
IndexName, FieldName, SortOrder. I will ignore SortOrder!
With tblCompanyTrade open in design view and the indexes wee window
showing here is the situation in the order of left to right indexes
w'dow.
Click to expand...
key/nokey, IndexName, FieldName
blank, CompanyID, pkCompanyID
key, PrimaryKey, pkCompanyID
key, Blank, pkTradeID
blank, TradeID, pkTradeID
As you can see (I think) I do have a double PK
Click to expand...

Actually, I didn't know that (A2003 dies not have the key
column).  I presume you are using A2007 and I haven't done
more than a little playing around with it.  I doubt that
makes a difference, unless you are using multi-valued
fields, so let's proceed

Is either the CompanyID or TradeID indexes marked as Unique?

Which fields between the two table are connected in the
relationship window?

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Marshall.
I am using A2003. What was it that made you think that I might be
using A2007.
Because you asked the question I did find that the pkCompanyID was
unique. When setting this up I wondered about whether it shd be or not
and since this is a linking table it makes sense to me now that it is
not. (I think)
I now have my 1:M relationship.
From tblCompany the pkCompanyID joins with pkCompanyID on the
tblCompanyTrade table and from the tblTrades the pkTradesID joins with
pkTradesID on the tblCompanyTrade table
Thanks again for your assistance.
 
TeeSee said:
I am using A2003. What was it that made you think that I might be
using A2007.
Click to expand...

I don't see a key column in the Indexes window. I don't
consider the little entry selector to be a column so I had
assumed you were seeing a feature I didn't see. Just a
matter of different people using different words to desribe
something.
Because you asked the question I did find that the pkCompanyID was
unique. When setting this up I wondered about whether it shd be or not
and since this is a linking table it makes sense to me now that it is
not. (I think)
I now have my 1:M relationship.
From tblCompany the pkCompanyID joins with pkCompanyID on the
tblCompanyTrade table and from the tblTrades the pkTradesID joins with
pkTradesID on the tblCompanyTrade table
Click to expand...


It might(?) make it a little easier for you to keep things
straight if you did not use a name with PK in it when the
field is used as a foreign key. Would it make more sense to
you if the tblCompanyTrade fields were named fkCompanyID and
fkTradesID?

Regardless of all that, you recognized an odd situation,
asked a good question and got it sorted out, so all's good.
 
I am using A2003. What was it that made you think that I might be
using A2007.
Click to expand...

I don't see a key column in the Indexes window.  I don't
consider the little entry selector to be a column so I had
assumed you were seeing a feature I didn't see.  Just a
matter of different people using different words to desribe
something.
Because you asked the question I did find that the pkCompanyID was
unique. When setting this up I wondered about whether it shd be or not
and since this is a linking table it makes sense to me now that it is
not. (I think)
I now have my 1:M relationship.
From tblCompany the pkCompanyID joins with pkCompanyID on the
tblCompanyTrade table and from the tblTrades the pkTradesID joins with
pkTradesID on the tblCompanyTrade table
Click to expand...

It might(?) make it a little easier for you to keep things
straight if you did not use a name with PK in it when the
field is used as a foreign key.  Would it make more sense to
you if the tblCompanyTrade fields were named fkCompanyID and
fkTradesID?

Regardless of all that, you recognized an odd situation,
asked a good question and got it sorted out, so all's good.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks again Marshall. I am attempting to use "fk" when it is a
foreign key in a table where it is not the "pk". In the
tblCompanyTrades both CompanyID and TradeID constitutes the "pk" so I
named them that way although they are a "fk" to both of the other
tables. What's a wee guy to do?
Is there a rule or guideline that I should typically follow?
 
TeeSee said:
Thanks again Marshall. I am attempting to use "fk" when it is a
foreign key in a table where it is not the "pk". In the
tblCompanyTrades both CompanyID and TradeID constitutes the "pk" so I
named them that way although they are a "fk" to both of the other
tables. What's a wee guy to do?
Is there a rule or guideline that I should typically follow?
Click to expand...


The only solid rule is that you do something that makes some
kind of sense and that you be consistent throughout your
programs.

I have a client with a database where I am the fourth
programmer to work on it. It's a mess with four different
programming styles and naming conventions and it can be
difficult for me to figure out how things are done. I
change things around when I am modifying a procedure or
whatever, but it's time consuming and dangerous. At least I
redid all the forms and report designs so users don't have a
mish mash way of working. The point of all that whinging is
that is it way better to "do it right" at the beginning.
OTOH, everything evolves as we learn so don't get locked
into a way that no longer meets your needs. It's all about
judgement calls.
 
Back
Top