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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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] |
#12
|
|||
|
|||
2 primary keys?
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] |
#13
|
|||
|
|||
2 primary keys? Additional Question
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] |
#14
|
|||
|
|||
2 primary keys? - Attached Word File Relationship
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] |
#15
|
|||
|
|||
2 primary keys?
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. -- |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Quotes to Orders to Invoice design - Help!! | Keith | Database Design | 28 | May 1st, 2006 05:44 PM |
Display the primary key(s) automatically in new record | Jade5 | Using Forms | 5 | March 16th, 2006 05:50 PM |
Problem with keys? | MeWivFree | General Discussion | 19 | December 11th, 2005 07:29 PM |
Creating composite primary keys in SQL | PJW | General Discussion | 2 | October 21st, 2005 10:12 AM |
Primary Keys | Lisa Z | Using Forms | 2 | January 31st, 2005 11:33 PM |