?Update query

[RGFlade]

I know I have done something like this before but can't remember how.

I have a table (Employees) that contains employee data and has a unique ID
(EID) which is an autonumber field. There is a field (PCRType) that lists if
the employee is a Nurse, Tech, Sec, or Other.

I have another table (EduType) that has all the required education for the
staff (Year, Description, Method, Nurse (Y/N), Tech (Y/N), Sec (Y/N), Other
(Y/N)) and this table has an autonumber field for unique identifier
(EduTypeID).

I have a third table (EducationalRecord) that I want to create from the two
above. This needs the EID from Employee, EduTypeID from EduType and then
some other fields (DateAttend, DateExpire, etc) that get filled in as
employees complete the required education.

My question - how can I automate it so that the third table gets an entry
for every staff member - I need to have the third table stay this EID is a
Nurse (from Employee) and this education is required for a Nurse (from
EduType) and appends a record and replaces the EID and EduType in that new
record.

I know I have done something like this before and I'm sure it was with
Append Query but I can't figure out how I did it.

Any help will be greatly appreciated.
Thanks
Bob
--
Robert G. Flade, RN, MS
Director - Emergency Department
The Hospital of Central Connecticut
at New Britain General Hospital
New Britain, CT 06050-0100

 

[John W. Vinson]

I know I have done something like this before but can't remember how.

I have a table (Employees) that contains employee data and has a unique ID
(EID) which is an autonumber field. There is a field (PCRType) that lists if
the employee is a Nurse, Tech, Sec, or Other.

Ok... is this a Text field?

I have another table (EduType) that has all the required education for the
staff (Year, Description, Method, Nurse (Y/N), Tech (Y/N), Sec (Y/N), Other
(Y/N)) and this table has an autonumber field for unique identifier
(EduTypeID).

This table's design IS WRONG. You're storing data in fieldnames. If you add a
new PCRType (Database Administrator say) you'll need to redesign this table,
your queries, your forms, your reports... you've painted yourself into a
corner! If you have a many to many relationship, you need a table with one
*record* for each value, not one field.

What are the actual contents of this table? Is Description... what? a
description of the education required? In what format? What's the purpose of
[Year]?

I have a third table (EducationalRecord) that I want to create from the two
above. This needs the EID from Employee, EduTypeID from EduType and then
some other fields (DateAttend, DateExpire, etc) that get filled in as
employees complete the required education.

My question - how can I automate it so that the third table gets an entry
for every staff member - I need to have the third table stay this EID is a
Nurse (from Employee) and this education is required for a Nurse (from
EduType) and appends a record and replaces the EID and EduType in that new
record.

Why not just have a query - not a table - joining the tables? With your
current design you'll need four different queries, joining the four different
fields, but correcting the table design should resolve that. If you do need it
for other fields, then yes, an append query would work (or, again, with your
current design, four such queries).

I know I have done something like this before and I'm sure it was with
Append Query but I can't figure out how I did it.

John W. Vinson [MVP]

 

[RGFlade]

The "Nurse", "Tech", "Sec", or "Other" is a lookup field in the Employee table.

Description is a description of the educational needs (e.g. Advanced Cardiac
Life Support).

I need to have a record that states that all Employees who are X is assigned
classes Y but each person in the Employee table needs an entry in the new
table (EducationType) becuause each employee will have a different AttendDate
and ExpireDate. . .

--
Robert G. Flade, RN, MS
Director - Emergency Department
The Hospital of Central Connecticut
at New Britain General Hospital
New Britain, CT 06050-0100

I know I have done something like this before but can't remember how.

I have a table (Employees) that contains employee data and has a unique ID
(EID) which is an autonumber field. There is a field (PCRType) that lists if
the employee is a Nurse, Tech, Sec, or Other.

Ok... is this a Text field?

I have another table (EduType) that has all the required education for the
staff (Year, Description, Method, Nurse (Y/N), Tech (Y/N), Sec (Y/N), Other
(Y/N)) and this table has an autonumber field for unique identifier
(EduTypeID).

This table's design IS WRONG. You're storing data in fieldnames. If you add a
new PCRType (Database Administrator say) you'll need to redesign this table,
your queries, your forms, your reports... you've painted yourself into a
corner! If you have a many to many relationship, you need a table with one
*record* for each value, not one field.

What are the actual contents of this table? Is Description... what? a
description of the education required? In what format? What's the purpose of
[Year]?

I have a third table (EducationalRecord) that I want to create from the two
above. This needs the EID from Employee, EduTypeID from EduType and then
some other fields (DateAttend, DateExpire, etc) that get filled in as
employees complete the required education.

My question - how can I automate it so that the third table gets an entry
for every staff member - I need to have the third table stay this EID is a
Nurse (from Employee) and this education is required for a Nurse (from
EduType) and appends a record and replaces the EID and EduType in that new
record.

Why not just have a query - not a table - joining the tables? With your
current design you'll need four different queries, joining the four different
fields, but correcting the table design should resolve that. If you do need it
for other fields, then yes, an append query would work (or, again, with your
current design, four such queries).

I know I have done something like this before and I'm sure it was with
Append Query but I can't figure out how I did it.

John W. Vinson [MVP]

 

[John W. Vinson]

The "Nurse", "Tech", "Sec", or "Other" is a lookup field in the Employee table.

See http://www.mvps.org/access/lookupfields.htm for a critique of the Lookup
field datatype. It's concealed from your view, but what is actually in the
employees table is a Long Integer number, a link to the lookup table. This
means that your query will need to be a bit different.

Description is a description of the educational needs (e.g. Advanced Cardiac
Life Support).

Does each educational need apply to one or more EducationTypes? Or just to
one? I'm still trying to visualize how this table is used (and to suspect that
it's not properly normalized).

I need to have a record that states that all Employees who are X is assigned
classes Y but each person in the Employee table needs an entry in the new
table (EducationType) becuause each employee will have a different AttendDate
and ExpireDate. . .

You haven't said anything about a table of Classes, and I'm trying to see how
it would fit in!

John W. Vinson [MVP]

 

[Dale Fye]

1. Totally agree with John about your table structure, specifically the
EduType table, but you can work around that. Create a union query to
normalize the data in that table. It will look something like (BTW [Year] is
a reserved word in Access and should not be used as a field name, but if you
do, you need to wrap it in brackets[]):

qryEduType:
SELECT "Nurse" as PCRType, [Year], Description, Method
FROM eduType
WHERE [Nurse] = True
UNION ALL
SELECT "Tech" as PCRType, [Year], Description, Method
FROM eduType
WHERE [Tech] = True
....

repeat the UnionAll and the select for each of the Sec and Other fields

2. You should now be able to join Employees to qryEduType on the PCRType
field and generate the table you are looking for with a MakeTable query.

If this does not work, post back with the SQL strings you use for qryPCRType
and for the make table query.

HTH
Dale