Create Multiple Table Rows From Single Table Row

[Giles]

Folks - Hope you can help.

Working with Access 2007 (SP1) on XP (SP2) and Vista (SP1).

I have a table (tblRoleTraining) with records for a number of new roles.
Each role has a training requirement against it.

There are 36 possible training modules and the need to complete the training
is provided as an indicator in the field for each role - e.g.

Role = "Programmer"
M1 (for Module 1) = Y
M2 = Y
M3 = N
etc.
etc.
M36 = C

For the moment I am unable to change the structure of this table (it is
periodically imported from another system and I can't get that changed (yet!))

What I need to be able to do is to create a new record in a second table
(tblTrainingDemand) when one of our people is assigned to a new role via a
form.

The tblTrainingDemand table is (better) structured as follows:

localID - autonum field to keep the record unique
HQID - headquarters id
MNo - Training module number
MReq - The training required (Y/N/C/X [Yes, No, Completed, eXcused]) - this
is directly lifted from the tblRoleTraining above.

So - when Joe Soap becomes a programmer I need to be able to create 36 new
records in the tblTrainingDemand table based on the generic training demand
from the first table.

So - I know what I want to do - just don't know how!

I am guessing that there are two ways - convert the input table from it's
current format to something that I can then use for an append query or to do
something (now I'm struggling!) that will enable the creation of new records
when a new role is added for the user.

Please help.

 

[KARL DEWEY]

This union query will take your flat file and change it for you. Just
complete the rest in the middle and insert your HQID instead of XYZ.
SELECT "XYZ" AS HQID, tblRoleTraining.Role, 1 AS MNo, tblRoleTraining.M1
AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 2 AS MNo,
tblRoleTraining.M2 AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 3 AS MNo,
tblRoleTraining.M3 AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 4 AS MNo,
tblRoleTraining.M4 AS MReq
FROM tblRoleTraining
............
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 35 AS MNo,
tblRoleTraining.M35 AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 36 AS MNo,
tblRoleTraining.M36 AS MReq
FROM tblRoleTraining:

 

[Giles]

Karl,

Thanks - will give this a try and let you know how I get on.

Rgrds

Giles

This union query will take your flat file and change it for you. Just
complete the rest in the middle and insert your HQID instead of XYZ.
SELECT "XYZ" AS HQID, tblRoleTraining.Role, 1 AS MNo, tblRoleTraining.M1
AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 2 AS MNo,
tblRoleTraining.M2 AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 3 AS MNo,
tblRoleTraining.M3 AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 4 AS MNo,
tblRoleTraining.M4 AS MReq
FROM tblRoleTraining
...........
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 35 AS MNo,
tblRoleTraining.M35 AS MReq
FROM tblRoleTraining
UNION ALL SELECT "XYZ" AS HQID, tblRoleTraining.Role, 36 AS MNo,
tblRoleTraining.M36 AS MReq
FROM tblRoleTraining:

--
KARL DEWEY
Build a little - Test a little

Folks - Hope you can help.

Working with Access 2007 (SP1) on XP (SP2) and Vista (SP1).

I have a table (tblRoleTraining) with records for a number of new roles.
Each role has a training requirement against it.

There are 36 possible training modules and the need to complete the training
is provided as an indicator in the field for each role - e.g.

Role = "Programmer"
M1 (for Module 1) = Y
M2 = Y
M3 = N
etc.
etc.
M36 = C

For the moment I am unable to change the structure of this table (it is
periodically imported from another system and I can't get that changed (yet!))

What I need to be able to do is to create a new record in a second table
(tblTrainingDemand) when one of our people is assigned to a new role via a
form.

The tblTrainingDemand table is (better) structured as follows:

localID - autonum field to keep the record unique
HQID - headquarters id
MNo - Training module number
MReq - The training required (Y/N/C/X [Yes, No, Completed, eXcused]) - this
is directly lifted from the tblRoleTraining above.

So - when Joe Soap becomes a programmer I need to be able to create 36 new
records in the tblTrainingDemand table based on the generic training demand
from the first table.

So - I know what I want to do - just don't know how!

I am guessing that there are two ways - convert the input table from it's
current format to something that I can then use for an append query or to do
something (now I'm struggling!) that will enable the creation of new records
when a new role is added for the user.

Please help.