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 |
#1
|
|||
|
|||
Trouble Creating Form
I am having real trouble working out how to create a form to link three tables.
I have: Artists_Table Artists ID (PK) (1) Artists Name Performance Table Track ID (PK) (many) Artist ID (PK) (many) Tracks_Table Track ID (PK) (1) Track Number Track Title Recording ID I have two other tables but my problem centres around these tables (i think because I have a joining table to split the many - many relationships). Could somebody please talk me through how i can get the artists table to interact with the tracks table? This is not happeneing at the moment. I keep trying to change the 'control source' for the primary keys from the diffent tables (i.e., Track ID) but i seem to be missing something very obvious. I am also trying to make the Artists Name a combo box but i am confused about whether it should look up a value, or the second option, and whether it should rememeber the value or store the value in a field. Thanks Adrian |
#2
|
|||
|
|||
Trouble Creating Form
Adrian,
Microsoft has a few ready made templates of this type. Maybe download one and de-construct it to what they have done. Here is a DVD collection template you could look at. http://office.microsoft.com/en-gb/te...186441033.aspx Richard "adrian007uk" wrote: I am having real trouble working out how to create a form to link three tables. I have: Artists_Table Artists ID (PK) (1) Artists Name Performance Table Track ID (PK) (many) Artist ID (PK) (many) Tracks_Table Track ID (PK) (1) Track Number Track Title Recording ID I have two other tables but my problem centres around these tables (i think because I have a joining table to split the many - many relationships). Could somebody please talk me through how i can get the artists table to interact with the tracks table? This is not happeneing at the moment. I keep trying to change the 'control source' for the primary keys from the diffent tables (i.e., Track ID) but i seem to be missing something very obvious. I am also trying to make the Artists Name a combo box but i am confused about whether it should look up a value, or the second option, and whether it should rememeber the value or store the value in a field. Thanks Adrian |
#3
|
|||
|
|||
Trouble Creating Form
I suggest we tackle one issue at a time.
First of all you must identical fields name in both table. For example, I created 2 tables as below: tblArtist (TABLE) ArtistID (PK) - text field type ArtistName - text field type tblTracks (TABLE) TrackID (PK) - text field type TrackNo - text field type TrackTitle - text field type RecordingID - text field type ArtistID - text field type (FIELD SIZE MUST BE THE SAME IN tblArtist) Then I created one-to-many relationship (tblArtist-to-tblTracks) using the ArtistID field. 1. Create a form via Form Wizard. 2. Select tblArtist from Tables/Queries combobox. 3. Move both the available fields to Selected fields. 4. Select tblTracks from Tables/Queries combobox in the same Form Wizard. 5. Move all available fields except ArtistID. 6. Click Next. 7. Click Next again. 8. Click Next again. 9. Click Next again. 10. Replace the Form name to frmArtist. 11. Replace the Subform name to frmTracksSubform. 12. Click Finish. You'll have your forms. Please confirm if you managed to get this form correct. Then we'll go the next stage. -- Maha Aruppthan Pappan Nacap Asia Pacific "adrian007uk" wrote: I am having real trouble working out how to create a form to link three tables. I have: Artists_Table Artists ID (PK) (1) Artists Name Performance Table Track ID (PK) (many) Artist ID (PK) (many) Tracks_Table Track ID (PK) (1) Track Number Track Title Recording ID I have two other tables but my problem centres around these tables (i think because I have a joining table to split the many - many relationships). Could somebody please talk me through how i can get the artists table to interact with the tracks table? This is not happeneing at the moment. I keep trying to change the 'control source' for the primary keys from the diffent tables (i.e., Track ID) but i seem to be missing something very obvious. I am also trying to make the Artists Name a combo box but i am confused about whether it should look up a value, or the second option, and whether it should rememeber the value or store the value in a field. Thanks Adrian |
#4
|
|||
|
|||
Trouble Creating Form
Hi Maha
I will attempt to make the changes you have suggested. I am confused as the only reason i created the "performance table" was to remove the many to many problem from artists to tarcks. However, you are now telling me to forget this issue. Adrian "Maha Arupputhan Pappan" wrote: I suggest we tackle one issue at a time. First of all you must identical fields name in both table. For example, I created 2 tables as below: tblArtist (TABLE) ArtistID (PK) - text field type ArtistName - text field type tblTracks (TABLE) TrackID (PK) - text field type TrackNo - text field type TrackTitle - text field type RecordingID - text field type ArtistID - text field type (FIELD SIZE MUST BE THE SAME IN tblArtist) Then I created one-to-many relationship (tblArtist-to-tblTracks) using the ArtistID field. 1. Create a form via Form Wizard. 2. Select tblArtist from Tables/Queries combobox. 3. Move both the available fields to Selected fields. 4. Select tblTracks from Tables/Queries combobox in the same Form Wizard. 5. Move all available fields except ArtistID. 6. Click Next. 7. Click Next again. 8. Click Next again. 9. Click Next again. 10. Replace the Form name to frmArtist. 11. Replace the Subform name to frmTracksSubform. 12. Click Finish. You'll have your forms. Please confirm if you managed to get this form correct. Then we'll go the next stage. -- Maha Aruppthan Pappan Nacap Asia Pacific "adrian007uk" wrote: I am having real trouble working out how to create a form to link three tables. I have: Artists_Table Artists ID (PK) (1) Artists Name Performance Table Track ID (PK) (many) Artist ID (PK) (many) Tracks_Table Track ID (PK) (1) Track Number Track Title Recording ID I have two other tables but my problem centres around these tables (i think because I have a joining table to split the many - many relationships). Could somebody please talk me through how i can get the artists table to interact with the tracks table? This is not happeneing at the moment. I keep trying to change the 'control source' for the primary keys from the diffent tables (i.e., Track ID) but i seem to be missing something very obvious. I am also trying to make the Artists Name a combo box but i am confused about whether it should look up a value, or the second option, and whether it should rememeber the value or store the value in a field. Thanks Adrian |
#5
|
|||
|
|||
Trouble Creating Form
Good Structure. It is well designed. The only change I would make would be
to remove any spaces in the names. It is best if field names and table names consist of only letters, numbers, and the underscore character. However, if you want to use spaces then you are going to have to use brackets around the names when you refer to the fields or tables. I'm a bit confused on what you mean by interact with the tracks table. Do you mean for data entry purposes - use forms and subforms or for reporting purposes. You would have a main form based on the Tracks table. This form would need to be in Single Form view. Then you need a second form based on the Performance table. This second form will be your subform on the tracks table and it will be a continuous form. On this form you can have a combobox whose control source is bound to [Performance Table].[Artist ID]. Its row source will be the Artists_Table (or a query based on that table). If the wizard is building the combobox then it will lookup the values from the table. Now add the second form as a subform to the Tracks form. When asked link the forms on the track id fields in the two tables. You should now be able to enter multiple artists for any track. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County adrian007uk wrote: I am having real trouble working out how to create a form to link three tables. I have: Artists_Table Artists ID (PK) (1) Artists Name Performance Table Track ID (PK) (many) Artist ID (PK) (many) Tracks_Table Track ID (PK) (1) Track Number Track Title Recording ID I have two other tables but my problem centres around these tables (i think because I have a joining table to split the many - many relationships). Could somebody please talk me through how i can get the artists table to interact with the tracks table? This is not happeneing at the moment. I keep trying to change the 'control source' for the primary keys from the diffent tables (i.e., Track ID) but i seem to be missing something very obvious. I am also trying to make the Artists Name a combo box but i am confused about whether it should look up a value, or the second option, and whether it should rememeber the value or store the value in a field. Thanks Adrian |
#6
|
|||
|
|||
Trouble Creating Form
"I'm a bit confused on what you mean by interact with the tracks table. Do
you mean for data entry purposes - use forms and subforms or for reporting purposes". I meant for data entry. I have another two tables (not listed in my orginal post: a Records table containing data on title, purchase price, notes etc and a music categories table). I do not have a problem linking the Records_Table to the Tracks_Table in a form. The wizard makes the Records_Table as the master form with the Tracks_Table as the sub-form (which is good). What i am trying to do is get the [artistID] as a combo box onto the records form. The problem is with the three tables i have detailed (I cannot get the primary key links to activate if that makes sense whern i start using the records form. It has someting to do with the trackID i think). Adrian "John Spencer" wrote: Good Structure. It is well designed. The only change I would make would be to remove any spaces in the names. It is best if field names and table names consist of only letters, numbers, and the underscore character. However, if you want to use spaces then you are going to have to use brackets around the names when you refer to the fields or tables. I'm a bit confused on what you mean by interact with the tracks table. Do you mean for data entry purposes - use forms and subforms or for reporting purposes. You would have a main form based on the Tracks table. This form would need to be in Single Form view. Then you need a second form based on the Performance table. This second form will be your subform on the tracks table and it will be a continuous form. On this form you can have a combobox whose control source is bound to [Performance Table].[Artist ID]. Its row source will be the Artists_Table (or a query based on that table). If the wizard is building the combobox then it will lookup the values from the table. Now add the second form as a subform to the Tracks form. When asked link the forms on the track id fields in the two tables. You should now be able to enter multiple artists for any track. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County adrian007uk wrote: I am having real trouble working out how to create a form to link three tables. I have: Artists_Table Artists ID (PK) (1) Artists Name Performance Table Track ID (PK) (many) Artist ID (PK) (many) Tracks_Table Track ID (PK) (1) Track Number Track Title Recording ID I have two other tables but my problem centres around these tables (i think because I have a joining table to split the many - many relationships). Could somebody please talk me through how i can get the artists table to interact with the tracks table? This is not happeneing at the moment. I keep trying to change the 'control source' for the primary keys from the diffent tables (i.e., Track ID) but i seem to be missing something very obvious. I am also trying to make the Artists Name a combo box but i am confused about whether it should look up a value, or the second option, and whether it should rememeber the value or store the value in a field. Thanks Adrian |
Thread Tools | |
Display Modes | |
|
|