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

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

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

Hi Nunya

Thanks for the info - can you tell me again in laymans' terms! So sorry, but I'm fairly new to Access!!

Jen

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

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