2 primary keys?

On Thu, 25 May 2006 10:50:00 +0800, "Bob Betts"
wrote:

Thanks ... I'll try this too and see what happens .... wait I think I did
... the relationship changed to indeterminate??

Since you don't specify what fields you used in either table, what you
defined as the key, and how you created the relationship, all I can
say is "well, you did something wrong".

IF you...

Ctrl-click the two fields in the "ONE" side table in design mode and
select the Key icon;

and

in the relationships window, select BOTH fields in the joining-fields
window, matching like to like, and then save the relationship,

you shouldn't have this problem.

John W. Vinson[MVP]

Thanks John ....I missed the dragging both part in the relationship table
...... :-)

I've read a couple of books and never encountered this ... Newsgroups sure
are very beneficial ... real scenarios ... real solutions .... :-)


"John Vinson" wrote in message
...
On Thu, 25 May 2006 10:50:00 +0800, "Bob Betts"
wrote:

Thanks ... I'll try this too and see what happens .... wait I think I did
... the relationship changed to indeterminate??

Since you don't specify what fields you used in either table, what you
defined as the key, and how you created the relationship, all I can
say is "well, you did something wrong".

IF you...

Ctrl-click the two fields in the "ONE" side table in design mode and
select the Key icon;

and

in the relationships window, select BOTH fields in the joining-fields
window, matching like to like, and then save the relationship,

you shouldn't have this problem.

John W. Vinson[MVP]

Hi John ... your instructions did work from the first table ... but
connecting to succeeding tables confused me.....

Table 1 Table 2
Table 3 Table 4

tblUSERS tblCABINETS tblDRAWER
tbFILES

USERNO USERNO CABINETNO
DRAWERNO
USERFNAME CABINETNO DRAWERNO
FOLDERCODE1
USERMIDINITIAL CABDES
FOLDERTITLE
USERLNAME CABLOCATION
ACTIVEYR
USERID
INACTIVEYR
PASSWORD
INACTIVELOC
USERDEP
REMARKS
USERDIV

I was able to connect table 1 to table 2 ... Set USERNO as primary key then
went to Table 2 and Set both USERNO and CABINETNO as PRIMARY and dragged
them in the relationship screen... IT worked!!!!! Problem starts in Table 3
....... since Table 2 is connected to table 3 via CABINETNO ... I can't do
the same right? For every cabinet there are multiple drawers and multiple
drawers with files .. no problem with files since the would be unique ....

Do I click CABINETNO and DRAWER with keys and drag them to CABINETNO in
table 2?

Thanks.

"John Vinson" wrote in message
...
On Thu, 25 May 2006 10:50:00 +0800, "Bob Betts"
wrote:

Thanks ... I'll try this too and see what happens .... wait I think I did
... the relationship changed to indeterminate??

Since you don't specify what fields you used in either table, what you
defined as the key, and how you created the relationship, all I can
say is "well, you did something wrong".

IF you...

Ctrl-click the two fields in the "ONE" side table in design mode and
select the Key icon;

and

in the relationships window, select BOTH fields in the joining-fields
window, matching like to like, and then save the relationship,

you shouldn't have this problem.

John W. Vinson[MVP]

Oops fter reviewing the one i posted it seems that the format of the text
went haywire ... attaching wordfile for reference .... connecting table 2 to
table 3 causes an indeterminate ... dragging two fields from table 3 to 2
asks me for a paired field??? it says connecting to blank row ..








"John Vinson" wrote in message
...
On Thu, 25 May 2006 10:50:00 +0800, "Bob Betts"
wrote:

Thanks ... I'll try this too and see what happens .... wait I think I did
... the relationship changed to indeterminate??

Since you don't specify what fields you used in either table, what you
defined as the key, and how you created the relationship, all I can
say is "well, you did something wrong".

IF you...

Ctrl-click the two fields in the "ONE" side table in design mode and
select the Key icon;

and

in the relationships window, select BOTH fields in the joining-fields
window, matching like to like, and then save the relationship,

you shouldn't have this problem.

John W. Vinson[MVP]

Got it .... !!!!! Assigned keys to each table .... enabling me to input
same cabinet name for different users ... Tried the tables and works
perfectly ... problem is how do i transfer this to a form I mean .... what
i wanted in a form is a combobox where i can select the cabinets and show
only the drawers and files connected to that user... I tried using a subform
wizard ... but it only works fine for the cabinets .... what i want is that
if i select a corresponding cabinet ... it would only show the corresponding
drawers attached to it.





"Bob Betts" wrote in message
...
Yes ... it seems that you were able to get my question.... someone posted
here about two primary keys ... I tried that too and the relationship
became
indeterminate ....

I'm trying to do a file indexing program ... one user ... many drawer ...
per drawer many cabinets ... per cabinet ... mutiple hanging folders ... I
thought of just assigning them a 1-100 cabinets per user ... assigning a
range so they won't use the same number as work around ... or put a
department accronym in each number like IT-001 to make it unique .. but
this solves only for the cabinet and drawer level ... What if someone from
two people from IT uses IT-0001 ... .. hmmmm I'll try to read your post
again...





"Craig Alexander Morrison" wrote in
message ...
I am not sure what you are asking but let me try to answer the question I
think you may be asking.

Are you asking how can I design the "database" (as opposed to the
application) so that One Person can only be assigned to One Drawer in any
One Cabinet. I am also assuming that the Person can be associated with
many Cabinet Drawers.

If that is the question then (assuming a Cabinet can have many Drawers,
and Drawers in different Cabinets can have the same name/number)

You need a Cabinet table with say a Primary Key of CabinetName this is
related in a One to Many with Drawers where the Drawer can only belong to
one Cabinet and the Primary Key will be a compound key made up of
CabinetName (the PK from the Cabinet table) and DrawerName. These two
fields CabinetName and DrawerName are the Primary Key of Drawer. (This
(the Compound Key) allows other drawers in other cabinets to have the
same
Drawer Name say Miscellaneous)

You state that only one person can be assigned to any given
Cabinet/Drawer
so then you include a single field in the CabinetDrawer table to relate
to
the Persons table. You include the Primary Key of the Persons table as a
foreign key in the CabinetDrawer table and this ensures that a
CabinetDrawer can only have one (or no) Persons assigned to it.

This rule (One Person to any Given Cabinet Drawer) should be enforced in
the database as opposed to the application. This distinction may seem
strange if all your system is in one MDB file however the Tables and
Relationships are the database and everything else Queries, Forms,
Reports
etc are the application. It is fairly common practice to create two MDB
files one containing the application and one containing the database.

Database Design by email is dangerous please check that the assumptions I
expressed at the start are in line with your understanding of the problem
domain.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Bob Betts" wrote in message
...
Whoa ... complicated answers ... yes I agree that I must read more on
table normalization ... anyway ... Yes my understanding is that you
assign a field with a primary key for indexing ...referring back to my
question ... What i want if the same user inputs same cabinet name and
drawer name the application will not allow him or her.



"Craig Alexander Morrison" wrote
in
message ...
If one is using Access and have little knowledge of Normalistion I
don't
think making the distinction between keys and indexes is going to
confuse them less.

However keys are implemented as indexes would have been more correct,
Thanks.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Jamie Collins" wrote in message
oups.com...

Craig Alexander Morrison wrote:
keys are indexes

It's logical vs physical. It would be less confusing to say, in Jet
keys are implemented using indexes. However, I think the OP is
confused
enough g, are they really interested in physical implementation
issues?

Jamie.

--