Training Database

[Dragon]

Im not an expert in database design so could someone help
me with this question.

i need to build a database which lists all staff members
and records what courses they have been on.

I know i have to create one table which lists all the
staff members and another table that lists all the
courses. This is where it starts to get hazy. Am i right
in thinking i have to create another table used to link
the other 2 together? whhat is the correct way to go
about this?

i was then thinking of creating a form that displayed
staff members and a sub form to show which courses they
had been on.

does this sound like the best way to approach it or am i
missing something out?

thanks in advance

 

[Nikos Yannacopoulos]

You certainly seem to be on the right track. Assuming each course may be
taken by several employees, I would suggest a three table design, something
like:

[tblSTAFF]
Staff_ID (Primary Key)
LastName
FirstName
MiddleName
Department
Job_Title
(etc.)

[tblCOURSES]
Course_ID (Primary Key)
Course_Title
Instructor
(etc.)

[tblATTENDANCE]
Staff_ID (Foreign Key)
Course_ID (Foreign Key)
Date
(etc.)

Where the tables are joined oin the common field names. Add any other fields
you require.

Now, on the form: it depends on what you want to do, really (courses by
employee, employees by course by date etc.). The form/subform approach is a
reasonable one for data entry/maintenace, while for "showing" which courses
each has been on, as you put it, you might consider a report.

HTH,
Nikos

 

[Dragon]

Thanks for your reply

i tried out the layout you suggested but im not sure what
you mean by 'Foreign Key'. Do i have to turn the field
into a foreign key like you do with a primary key?

I created the three tables and created relationships
between. I then created a form to show employees and
courses they have been on but it didnt seem to work when i
started to enter data (error messages came up)

any tips where im going wrong?

thanks in advance

-----Original Message-----
You certainly seem to be on the right track. Assuming each course may be
taken by several employees, I would suggest a three table design, something
like:

[tblSTAFF]
Staff_ID (Primary Key)
LastName
FirstName
MiddleName
Department
Job_Title
(etc.)

[tblCOURSES]
Course_ID (Primary Key)
Course_Title
Instructor
(etc.)

[tblATTENDANCE]
Staff_ID (Foreign Key)
Course_ID (Foreign Key)
Date
(etc.)

Where the tables are joined oin the common field names. Add any other fields
you require.

Now, on the form: it depends on what you want to do, really (courses by
employee, employees by course by date etc.). The form/subform approach is a
reasonable one for data entry/maintenace, while for "showing" which courses
each has been on, as you put it, you might consider a report.

HTH,
Nikos

Im not an expert in database design so could someone help
me with this question.

i need to build a database which lists all staff members
and records what courses they have been on.

I know i have to create one table which lists all the
staff members and another table that lists all the
courses. This is where it starts to get hazy. Am i right
in thinking i have to create another table used to link
the other 2 together? whhat is the correct way to go
about this?

i was then thinking of creating a form that displayed
staff members and a sub form to show which courses they
had been on.

does this sound like the best way to approach it or am i
missing something out?

thanks in advance

.

 

[Nikos Yannacopoulos]

Foreign Key means the field is (part of) a primary key in another table, and
used to cross-reference the two. No, you don't have to do anything about it.

What error messages doo you get in the form?
What is its structure (simple form or with subform)?
What is the recordsource (tables or query)?

Nikos

Thanks for your reply

i tried out the layout you suggested but im not sure what
you mean by 'Foreign Key'. Do i have to turn the field
into a foreign key like you do with a primary key?

I created the three tables and created relationships
between. I then created a form to show employees and
courses they have been on but it didnt seem to work when i
started to enter data (error messages came up)

any tips where im going wrong?

thanks in advance

-----Original Message-----
You certainly seem to be on the right track. Assuming each course may be
taken by several employees, I would suggest a three table design, something
like:

[tblSTAFF]
Staff_ID (Primary Key)
LastName
FirstName
MiddleName
Department
Job_Title
(etc.)

[tblCOURSES]
Course_ID (Primary Key)
Course_Title
Instructor
(etc.)

[tblATTENDANCE]
Staff_ID (Foreign Key)
Course_ID (Foreign Key)
Date
(etc.)

Where the tables are joined oin the common field names. Add any other fields
you require.

Now, on the form: it depends on what you want to do, really (courses by
employee, employees by course by date etc.). The form/subform approach is a
reasonable one for data entry/maintenace, while for "showing" which courses
each has been on, as you put it, you might consider a report.

HTH,
Nikos

Im not an expert in database design so could someone help
me with this question.

i need to build a database which lists all staff members
and records what courses they have been on.

I know i have to create one table which lists all the
staff members and another table that lists all the
courses. This is where it starts to get hazy. Am i right
in thinking i have to create another table used to link
the other 2 together? whhat is the correct way to go
about this?

i was then thinking of creating a form that displayed
staff members and a sub form to show which courses they
had been on.

does this sound like the best way to approach it or am i
missing something out?

thanks in advance

.

 

[Guest]

Hi,

Ok....bare with me on this.

I Created the three tables as you instructed.

I then used the form wizard to create a simple form with
subform and included all the fields from the 'Staff' table
and the 'bookings' table.

I then edited the subform so that the Course ID field was
a combo box so that you could use it to pick out what
course a particular person had been on. So, i ended up
with the main form showing staff details and the subform
would be used to keep records of the courses they had been
on.

it all looked fine until i started to enter some data.
When i tried to enter a record into the subform and choose
a course for someone, it allowed me to do it until i
closed out and went back into the form.

thats when i get the error :

"The expression is typed incorrectly, or is too complex to
be evaluated. For example, a numeric expressionmay
contain too many complicated elements. try simplifying
the expression by assigning parts of the expression to
variables"

lol......i wish i knew what that meant

-----Original Message-----
Foreign Key means the field is (part of) a primary key in another table, and
used to cross-reference the two. No, you don't have to do anything about it.

What error messages doo you get in the form?
What is its structure (simple form or with subform)?
What is the recordsource (tables or query)?

Nikos

Thanks for your reply

i tried out the layout you suggested but im not sure what
you mean by 'Foreign Key'. Do i have to turn the field
into a foreign key like you do with a primary key?

I created the three tables and created relationships
between. I then created a form to show employees and
courses they have been on but it didnt seem to work when i
started to enter data (error messages came up)

any tips where im going wrong?

thanks in advance

-----Original Message-----
You certainly seem to be on the right track. Assuming each course may be
taken by several employees, I would suggest a three

table
design, something

like:

[tblSTAFF]
Staff_ID (Primary Key)
LastName
FirstName
MiddleName
Department
Job_Title
(etc.)

[tblCOURSES]
Course_ID (Primary Key)
Course_Title
Instructor
(etc.)

[tblATTENDANCE]
Staff_ID (Foreign Key)
Course_ID (Foreign Key)
Date
(etc.)

Where the tables are joined oin the common field names. Add any other fields
you require.

Now, on the form: it depends on what you want to do, really (courses by
employee, employees by course by date etc.). The form/subform approach is a
reasonable one for data entry/maintenace, while for "showing" which courses
each has been on, as you put it, you might consider a report.

HTH,
Nikos


Im not an expert in database design so could someone help
me with this question.

i need to build a database which lists all staff members
and records what courses they have been on.

I know i have to create one table which lists all the
staff members and another table that lists all the
courses. This is where it starts to get hazy. Am i right
in thinking i have to create another table used to link
the other 2 together? whhat is the correct way to go
about this?

i was then thinking of creating a form that displayed
staff members and a sub form to show which courses they
had been on.

does this sound like the best way to approach it or am i
missing something out?

thanks in advance


.

.

 

[Nikos Yannacopoulos]

You seem to be on the right track...pls check if the following hold true, or
change if not:

(1) Main form recordsource: table tblSTAFF (or whatever you call it)
(2) Subform recordsource: table tblBOOKINGS (I take it this is the one I
referred to as tblATTENDANCE)
(3) Combo box rowsource is table tblCOURSES, but Control source is field
Course_ID of table tblBOOKINGS
(4) Subform link field (child and master) is Staff_ID or whatever it is
called in tables tblBOOKINGS and tblSTAFF respectively.

To check:
(1) Open the form in design view, click on the grey square in the
cross-section of horizontal and vertical rulers (top left hand side) of the
main form. The square should now have a smaller black square in it. Display
properties (the properties window should read Form at the top). Select tab
Data and check Recordsource.
(2) While in the main form design view, click on the grey square in the
cross-section of horizontal and vertical rulers (top left hand side) of the
subform (it may require a second click). The square should now have a
smaller black square in it. Display properties (the properties window should
read Form at the top, again). Select tab Data and check Recordsource.
(3) Select the combo box, display properties. The properties window top
should read Combo Box: combo name. On the Data tab check control source and
rowsource. The rowsource property may have an SQL statement instead of the
table name. If the SQL statement reads from the right table (tblCOURSES),
it's also OK.
(4) Select the subform by clikcking anywhere in it, provided your prior
selection was outside the subform. The properties window top should read
Subform / Subreport: Subform name. Check the field names in Link Child
Fileds and Link Master Fields.

HTH,
Nikos

Hi,

Ok....bare with me on this.

I Created the three tables as you instructed.

I then used the form wizard to create a simple form with
subform and included all the fields from the 'Staff' table
and the 'bookings' table.

I then edited the subform so that the Course ID field was
a combo box so that you could use it to pick out what
course a particular person had been on. So, i ended up
with the main form showing staff details and the subform
would be used to keep records of the courses they had been
on.

it all looked fine until i started to enter some data.
When i tried to enter a record into the subform and choose
a course for someone, it allowed me to do it until i
closed out and went back into the form.

thats when i get the error :

"The expression is typed incorrectly, or is too complex to
be evaluated. For example, a numeric expressionmay
contain too many complicated elements. try simplifying
the expression by assigning parts of the expression to
variables"

lol......i wish i knew what that meant

-----Original Message-----
Foreign Key means the field is (part of) a primary key in another table, and
used to cross-reference the two. No, you don't have to do anything about it.

What error messages doo you get in the form?
What is its structure (simple form or with subform)?
What is the recordsource (tables or query)?

Nikos

Thanks for your reply

i tried out the layout you suggested but im not sure what
you mean by 'Foreign Key'. Do i have to turn the field
into a foreign key like you do with a primary key?

I created the three tables and created relationships
between. I then created a form to show employees and
courses they have been on but it didnt seem to work when i
started to enter data (error messages came up)

any tips where im going wrong?

thanks in advance


-----Original Message-----
You certainly seem to be on the right track. Assuming
each course may be
taken by several employees, I would suggest a three table
design, something
like:

[tblSTAFF]
Staff_ID (Primary Key)
LastName
FirstName
MiddleName
Department
Job_Title
(etc.)

[tblCOURSES]
Course_ID (Primary Key)
Course_Title
Instructor
(etc.)

[tblATTENDANCE]
Staff_ID (Foreign Key)
Course_ID (Foreign Key)
Date
(etc.)

Where the tables are joined oin the common field names.
Add any other fields
you require.

Now, on the form: it depends on what you want to do,
really (courses by
employee, employees by course by date etc.). The
form/subform approach is a
reasonable one for data entry/maintenace, while
for "showing" which courses
each has been on, as you put it, you might consider a
report.

HTH,
Nikos


message
Im not an expert in database design so could someone
help
me with this question.

i need to build a database which lists all staff members
and records what courses they have been on.

I know i have to create one table which lists all the
staff members and another table that lists all the
courses. This is where it starts to get hazy. Am i
right
in thinking i have to create another table used to link
the other 2 together? whhat is the correct way to go
about this?

i was then thinking of creating a form that displayed
staff members and a sub form to show which courses they
had been on.

does this sound like the best way to approach it or am i
missing something out?

thanks in advance


.

.

 

[Dragon]

Thanks again for your help here....i really appreciate it.

Unfortunately my data matched all 4 of your points. so im
still at a loss. If I try to enter a course for someone
it throws up this error message. It goes back to normal
again if I clear the bookings table. Yet if i go into the
bookings table itself, its recording the correct
information. so its seems its definately a problem with
the form.

I might attempt the whole thing again just in case i have
accidently changed something by accident.

-----Original Message-----
You seem to be on the right track...pls check if the following hold true, or
change if not:

(1) Main form recordsource: table tblSTAFF (or whatever you call it)
(2) Subform recordsource: table tblBOOKINGS (I take it this is the one I
referred to as tblATTENDANCE)
(3) Combo box rowsource is table tblCOURSES, but Control source is field
Course_ID of table tblBOOKINGS
(4) Subform link field (child and master) is Staff_ID or whatever it is
called in tables tblBOOKINGS and tblSTAFF respectively.

To check:
(1) Open the form in design view, click on the grey square in the
cross-section of horizontal and vertical rulers (top left hand side) of the
main form. The square should now have a smaller black square in it. Display
properties (the properties window should read Form at the top). Select tab
Data and check Recordsource.
(2) While in the main form design view, click on the grey square in the
cross-section of horizontal and vertical rulers (top left hand side) of the
subform (it may require a second click). The square should now have a
smaller black square in it. Display properties (the properties window should
read Form at the top, again). Select tab Data and check Recordsource.
(3) Select the combo box, display properties. The properties window top
should read Combo Box: combo name. On the Data tab check control source and
rowsource. The rowsource property may have an SQL statement instead of the
table name. If the SQL statement reads from the right table (tblCOURSES),
it's also OK.
(4) Select the subform by clikcking anywhere in it, provided your prior
selection was outside the subform. The properties window top should read
Subform / Subreport: Subform name. Check the field names in Link Child
Fileds and Link Master Fields.

HTH,
Nikos

Hi,

Ok....bare with me on this.

I Created the three tables as you instructed.

I then used the form wizard to create a simple form with
subform and included all the fields from the 'Staff' table
and the 'bookings' table.

I then edited the subform so that the Course ID field was
a combo box so that you could use it to pick out what
course a particular person had been on. So, i ended up
with the main form showing staff details and the subform
would be used to keep records of the courses they had been
on.

it all looked fine until i started to enter some data.
When i tried to enter a record into the subform and choose
a course for someone, it allowed me to do it until i
closed out and went back into the form.

thats when i get the error :

"The expression is typed incorrectly, or is too complex to
be evaluated. For example, a numeric expressionmay
contain too many complicated elements. try simplifying
the expression by assigning parts of the expression to
variables"

lol......i wish i knew what that meant

-----Original Message-----
Foreign Key means the field is (part of) a primary key

in
another table, and

used to cross-reference the two. No, you don't have to

do
anything about it.

What error messages doo you get in the form?
What is its structure (simple form or with subform)?
What is the recordsource (tables or query)?

Nikos

Thanks for your reply

i tried out the layout you suggested but im not sure what
you mean by 'Foreign Key'. Do i have to turn the field
into a foreign key like you do with a primary key?

I created the three tables and created relationships
between. I then created a form to show employees and
courses they have been on but it didnt seem to work when i
started to enter data (error messages came up)

any tips where im going wrong?

thanks in advance


-----Original Message-----
You certainly seem to be on the right track. Assuming
each course may be
taken by several employees, I would suggest a three table
design, something
like:

[tblSTAFF]
Staff_ID (Primary Key)
LastName
FirstName
MiddleName
Department
Job_Title
(etc.)

[tblCOURSES]
Course_ID (Primary Key)
Course_Title
Instructor
(etc.)

[tblATTENDANCE]
Staff_ID (Foreign Key)
Course_ID (Foreign Key)
Date
(etc.)

Where the tables are joined oin the common field names.
Add any other fields
you require.

Now, on the form: it depends on what you want to do,
really (courses by
employee, employees by course by date etc.). The
form/subform approach is a
reasonable one for data entry/maintenace, while
for "showing" which courses
each has been on, as you put it, you might consider a
report.

HTH,
Nikos


message
Im not an expert in database design so could someone
help
me with this question.

i need to build a database which lists all staff members
and records what courses they have been on.

I know i have to create one table which lists all the
staff members and another table that lists all the
courses. This is where it starts to get hazy. Am i
right
in thinking i have to create another table used to link
the other 2 together? whhat is the correct way to go
about this?

i was then thinking of creating a form that displayed
staff members and a sub form to show which courses they
had been on.

does this sound like the best way to approach it

or
am i

missing something out?

thanks in advance


.



.

.

 

[Tim Ferguson]

Foreign Key means the field is (part of) a primary key in another
table, and used to cross-reference the two.

Umm, no. It has to match the Primary Key, the whole Primary Key and nothing
but the Primary Key in the other table. Okay, strictly speaking it's any
Unique Key, but there is rarely a need to have more than one in a table.
But in any case, it must be an exact match: whole, complete and not in
part.

No, you don't have to do anything about it.

Umm: yes you do. You have to create the relationship in the Relationships
window and Enforce Referential Integrity. This is what a foreign key
actually means.

HTH


Tim F

 

[pp240z \(removethis\)]

You have the right idea. You need an ID number
identifying the course that the staff member took and that
is entered into the staff table. A combo box works well
to list out the courses in the separate table. When you
choose the course in the combo box, it will store the
course ID number in the corresponding field you have in
the staff table. You will need a separate primary ID
number for each staff member. The subform idea is correct
as well.

 

[Nikos Yannacopoulos]

Don't know what else to suggest... you're welcome to zip your database and
e-mail it so I can have a look (provided it's not sensitive), if you want.

Nikos

Thanks again for your help here....i really appreciate it.

Unfortunately my data matched all 4 of your points. so im
still at a loss. If I try to enter a course for someone
it throws up this error message. It goes back to normal
again if I clear the bookings table. Yet if i go into the
bookings table itself, its recording the correct
information. so its seems its definately a problem with
the form.

I might attempt the whole thing again just in case i have
accidently changed something by accident.

-----Original Message-----
You seem to be on the right track...pls check if the following hold true, or
change if not:

(1) Main form recordsource: table tblSTAFF (or whatever you call it)
(2) Subform recordsource: table tblBOOKINGS (I take it this is the one I
referred to as tblATTENDANCE)
(3) Combo box rowsource is table tblCOURSES, but Control source is field
Course_ID of table tblBOOKINGS
(4) Subform link field (child and master) is Staff_ID or whatever it is
called in tables tblBOOKINGS and tblSTAFF respectively.

To check:
(1) Open the form in design view, click on the grey square in the
cross-section of horizontal and vertical rulers (top left hand side) of the
main form. The square should now have a smaller black square in it. Display
properties (the properties window should read Form at the top). Select tab
Data and check Recordsource.
(2) While in the main form design view, click on the grey square in the
cross-section of horizontal and vertical rulers (top left hand side) of the
subform (it may require a second click). The square should now have a
smaller black square in it. Display properties (the properties window should
read Form at the top, again). Select tab Data and check Recordsource.
(3) Select the combo box, display properties. The properties window top
should read Combo Box: combo name. On the Data tab check control source and
rowsource. The rowsource property may have an SQL statement instead of the
table name. If the SQL statement reads from the right table (tblCOURSES),
it's also OK.
(4) Select the subform by clikcking anywhere in it, provided your prior
selection was outside the subform. The properties window top should read
Subform / Subreport: Subform name. Check the field names in Link Child
Fileds and Link Master Fields.

HTH,
Nikos

Hi,

Ok....bare with me on this.

I Created the three tables as you instructed.

I then used the form wizard to create a simple form with
subform and included all the fields from the 'Staff' table
and the 'bookings' table.

I then edited the subform so that the Course ID field was
a combo box so that you could use it to pick out what
course a particular person had been on. So, i ended up
with the main form showing staff details and the subform
would be used to keep records of the courses they had been
on.

it all looked fine until i started to enter some data.
When i tried to enter a record into the subform and choose
a course for someone, it allowed me to do it until i
closed out and went back into the form.

thats when i get the error :

"The expression is typed incorrectly, or is too complex to
be evaluated. For example, a numeric expressionmay
contain too many complicated elements. try simplifying
the expression by assigning parts of the expression to
variables"

lol......i wish i knew what that meant

-----Original Message-----
Foreign Key means the field is (part of) a primary key in
another table, and
used to cross-reference the two. No, you don't have to do
anything about it.

What error messages doo you get in the form?
What is its structure (simple form or with subform)?
What is the recordsource (tables or query)?

Nikos

message
Thanks for your reply

i tried out the layout you suggested but im not sure
what
you mean by 'Foreign Key'. Do i have to turn the field
into a foreign key like you do with a primary key?

I created the three tables and created relationships
between. I then created a form to show employees and
courses they have been on but it didnt seem to work
when i
started to enter data (error messages came up)

any tips where im going wrong?

thanks in advance


-----Original Message-----
You certainly seem to be on the right track. Assuming
each course may be
taken by several employees, I would suggest a three
table
design, something
like:

[tblSTAFF]
Staff_ID (Primary Key)
LastName
FirstName
MiddleName
Department
Job_Title
(etc.)

[tblCOURSES]
Course_ID (Primary Key)
Course_Title
Instructor
(etc.)

[tblATTENDANCE]
Staff_ID (Foreign Key)
Course_ID (Foreign Key)
Date
(etc.)

Where the tables are joined oin the common field names.
Add any other fields
you require.

Now, on the form: it depends on what you want to do,
really (courses by
employee, employees by course by date etc.). The
form/subform approach is a
reasonable one for data entry/maintenace, while
for "showing" which courses
each has been on, as you put it, you might consider a
report.

HTH,
Nikos


message
Im not an expert in database design so could someone
help
me with this question.

i need to build a database which lists all staff
members
and records what courses they have been on.

I know i have to create one table which lists all the
staff members and another table that lists all the
courses. This is where it starts to get hazy. Am i
right
in thinking i have to create another table used to
link
the other 2 together? whhat is the correct way to go
about this?

i was then thinking of creating a form that displayed
staff members and a sub form to show which courses
they
had been on.

does this sound like the best way to approach it or
am i
missing something out?

thanks in advance


.



.

.

 

[Dragon]

Thats probably why it wasnt working then....i didnt
enforce referential integrity!!

thanks for the help guys