Extract data from a record in a table

[jvanderw]

Here is my problem.
I have a table called "tblemplInfo" with the following fields:
Employee ID
Name
001
002
003
004
005
.
.
.
099

All of the fields with a number name are Yes/No fields. What I want to do is search each record in the table and each time there is a "yes" or check in the Yes/No field, I want to move the following information to a table called "tblJCinfo":
Employee ID into a field called Employee ID
Name into a field called Name
the name of the field with the "Yes" value into a field called Job Code

Example:
If the first record in tblEmplInfo is:
Employee ID: 12345
Name: John Doe
001: Yes
002: No
003: No
004: Yes
005: No
.
.
.
099: No
Then the following information would be the first record moved into tblJCinfo:
Employee ID: 12345
Name: John Doe
Job Code: 001

The second record moved into tblJCinfo would be:
Employee ID: 12345
Name: John Doe
Job Code: 004

I am assuming that this will have to be done in a module, and it would be ideal if the code would loop through all of the records in tblEmplInfo.

Thanks for the help!

 

[TelcoBT]

It may be possible to use SQL, but I would go for code and
iterate through the table records
and for each record iterate through the fields. One possible design is shown below NB The syntax below is CERTAINLY incorrect, as when coding I use the IDE to the full and let it pick up my typos and show me the Object hierarchies etc (- lazy, but that's what it's there for! ) and I'm writing this quickly in a text editor after a week of working in other languages with no means of checking. BUT a) the design should be fine, and b) debugging can be hours of fun ;-)

dim rs as DAO.Recordset
dim strSQL as string
dim varMyField

'** not sure about adOpenTable argument below,
'** you need to check spelling etc

set rs=CurrendDb.openrecordset(tblEmplInfo, adOpenTable)

rs.moveFirst

do While not(rs.eof) and not(rs.bof)

'** here again,below, check syntax of rs and it's field objects
'** I can't an Access isn't on this machine and I can't
'** remember precise object model.

for each varMyField in rs.fields
if varMyField.type is YesNo then
if varMyField.Value then '** I assume Yes = true
strSQL = "INSERT INTO tblJCinfo ([Employee ID],Name,[Job Code]) VALUES (" & rs("Employee ID&quot, rs("Name&quot, varMyField.Name & &quot"

'** varMyField.Name should be the '001' etc.

currentDB.Execute(strSQL)
end if
else
'** do nothing, as it isn't a yesNo field
endif
next

rs.moveNext

loop


rs.close
set rs=nothing


one last point, IF my guess at what you are doing is correct, then I think your design could be improved, I would suggest that you look at moving the job codes from being Fields in a table to a separate table with at least
the following 3 fields

employeeId, JobCode, YesNo

employeeId would be a foreign key into the tblEmplInfo table, this should make for a more flexible database and make life easier as simpler SQL queries could be used to search and update, rather than the code above.

Must go, got some debugging of my own to do!

 

[jvanderw]

Thanks for the help. The code that you supplied got me far enough to get the rest. Here is what the final product looks like.

Option Compare Database
Option Explicit
Public Sub getjobcode()
Dim rs As Recordset
Dim strSQL As String
Dim varMyField
Dim rs2 As Recordset

Dim db As Database
Set db = CurrentDb

Set rs = db.OpenRecordset("tblEmployeeID&quot
Set rs2 = db.OpenRecordset("EmpJobCodes &quot
rs.MoveFirst

Do While Not (rs.EOF) And Not (rs.BOF)

For Each varMyField In rs.Fields

If varMyField.Value = True Then
rs2.AddNew
rs2![Emp ID] = rs![id]
rs2![job code] = varMyField.Name
rs2.Update
Else
End If
Next

rs.MoveNext
Loop
rs.Close

Set rs = Nothing
End Sub