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
|
|||
|
|||
Help Setting up Tables/Forms
I wonder if anyone can give me a start on a database I am setting up:
I have set up a table with staff names (first name and surname in separate fields) and details. From that table I have created a form, and a subform within it listing all training courses each member of staff has completed. At the moment, the tables are linked via a unique ID number for each member of staff. When staff attend a course, I have to individually find each person and their ID number, then type the ID number and the course in the other table. What I would like to be able to do is: in the (sub?)table I would like to be able to type in the NAME and the course for each attendee, so I don't have to cross reference for every single person. However, I have tried to link the tables together by names but this doesn't seem to work. Any ideas anyone? Many thanks for any help... Jen |
#2
|
|||
|
|||
Help Setting up Tables/Forms
Jen,
Try these tables: TblStaff StaffID Details TblCourse CourseID Details TblCourseCompletedByStaff CourseCompletedByStaffID StaffID CourseID Build a form/subform where the main form is based on TblStaff and the subform is based on TblCourseCompletedByStaff. Use a combobox whose rowsource is TblCourse in the subform to select the course. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Jen" wrote in message ... I wonder if anyone can give me a start on a database I am setting up: I have set up a table with staff names (first name and surname in separate fields) and details. From that table I have created a form, and a subform within it listing all training courses each member of staff has completed. At the moment, the tables are linked via a unique ID number for each member of staff. When staff attend a course, I have to individually find each person and their ID number, then type the ID number and the course in the other table. What I would like to be able to do is: in the (sub?)table I would like to be able to type in the NAME and the course for each attendee, so I don't have to cross reference for every single person. However, I have tried to link the tables together by names but this doesn't seem to work. Any ideas anyone? Many thanks for any help... Jen |
#3
|
|||
|
|||
Help Setting up Tables/Forms
In the subform, set the Link Master Fields property to the name of the staff
ID number in your staff table, and set the Link Child Fields to the name of the staff ID field in your training completed table. Then, the ID field will be populated automatically in your subform. Good luck. "Jen" wrote in message ... I wonder if anyone can give me a start on a database I am setting up: I have set up a table with staff names (first name and surname in separate fields) and details. From that table I have created a form, and a subform within it listing all training courses each member of staff has completed. At the moment, the tables are linked via a unique ID number for each member of staff. When staff attend a course, I have to individually find each person and their ID number, then type the ID number and the course in the other table. What I would like to be able to do is: in the (sub?)table I would like to be able to type in the NAME and the course for each attendee, so I don't have to cross reference for every single person. However, I have tried to link the tables together by names but this doesn't seem to work. Any ideas anyone? Many thanks for any help... Jen |
#4
|
|||
|
|||
Help Setting up Tables/Forms
Hi Nunya
Thanks for the info - can you tell me again in laymans' terms! So sorry, but I'm fairly new to Access!! Jen |
#5
|
|||
|
|||
Help Setting up Tables/Forms
open the main form in design view. double click in the little box in the
upper left corner (where the horizontal and vertical rulers meet); this will open the Properties box, if it's not already open. still in form design view, click once on the subform to select it. the Properties box now reflects the subform control properties. click on the Data tab in the Properties box. in Link Master Fields, enter the name of the unique ID number field from the staff table (that should be the table listed as the Source Object of the main form). in Link Child Fields, enter the name of the matching ID number field from the "other" table. suggest you set up 3 tables, as described by PC Datasheet's post. then you can use a combo box for the course field in the subform. ultimately, the only data entry you should have to do in each record in the subform is to pick a course name from the droplist. hth "Jen" wrote in message ... Hi Nunya Thanks for the info - can you tell me again in laymans' terms! So sorry, but I'm fairly new to Access!! Jen |
#6
|
|||
|
|||
Help Setting up Tables/Forms
On Wed, 28 Apr 2004 06:06:04 -0700, Jen
wrote: At the moment, the tables are linked via a unique ID number for each member of staff. When staff attend a course, I have to individually find each person and their ID number, then type the ID number and the course in the other table. What I would like to be able to do is: in the (sub?)table I would like to be able to type in the NAME and the course for each attendee, so I don't have to cross reference for every single person. However, I have tried to link the tables together by names but this doesn't seem to work. If you're working in table datasheets... DON'T. Instead create a Form based on the Staff table, with a Subform based on the attendance table. The Forms Wizard will help you set this up - just choose both tables and select the option to create a subform. This will show you the staff member's name and any other desired information on the mainform, and that person's courses on the subform. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|