Specify an Output format in a 1toMany Relationship

[Treason]

ok, this has stumped me for a while and I would love to overcome this..

I have 2 tables, one with employee ID's and other employee information, and another with Employee ID's and jobs. The Employee ID's from each table are joined in a One-To-Many relationship.

here's the example

Table1
[Emp_ID] [Emp_Name] [Address]
12345 John Doe 123 Mainst
43212 Jane Doe etc...

Table2
[Emp_ID] [Job]
12345 job12
12345 job34
12345 job42
43212 job01
43212 job93

Is there a way to create a query that will put the Emp_ID in one field and every Job that employee had in the next field without a delimiter. for example

Table3
[Emp_ID] [AllJobs]
12345 job12job34job42
43212 job01job93

I hope my question is clear, and I hope this is possible to create... any help is as always greatly appreciated.

 

[mrharvey]

Hi Treason,

Have you consider creating a cross-tab query on table2, linking to table1 (via EmpID)?

Michael

 

[Treason]

Well I never used crosstab queries before, I am still new at this... How can I get it to return the proper values? Seems like cross tabs queries are more used for Avg, Sums etc...

 

[mrharvey]

Hi Treason:

After re-reading your requirements and further consideration, I realized that a cross-tab query will not be the solution you need.
You need a little VBA-weenie-programming.
Here is my layout and propsed solution:
tbl - Emps: [Emp_ID], [Emp_Name], [Address]
tbl - EmpJobs: [Emp_ID], [Job]
tbl - EmpAllJobs: [Emp_ID], [AllJobs]

I've tested the VBA procedure shown below in Access 97 and 2000. If using Access 2000 or above, you need to make certain a library reference is set to DAO. E-mail, if you have questions (mrharvey@rapidsys.com)
Regards, Michael

=========================
Public Sub PopEmpAllJobs()

Dim db As dao.Database
Dim rs As dao.Recordset, rsOut As dao.Recordset
Dim lngSavEmp_ID As Long, strAllJobs As String

' This procedure is based upon Jet 3.51 DAO
' 2 tables used: EmpJobs (employee jobs normalized)
' EmpAllJobs (employee jobs pivoted work table)

lngSavEmp_ID = 0
Set db = CurrentDb()

' Clear all jobs work table
db.Execute "Delete * From EmpAllJobs;"

' Set recordset objects to the input (Emp JObs) and output (EmpAllJobs)
Set rs = db.OpenRecordset("EmpJobs", dbOpenDynaset, dbReadOnly)
Set rsOut = db.OpenRecordset("EmpAllJobs", dbOpenTable, dbAppendOnly)

Do While Not rs.EOF

If lngSavEmp_ID = 0 Then ' save first Emp_ID for break
lngSavEmp_ID = rs!Emp_ID
End If

If lngSavEmp_ID <> rs!Emp_ID Then ' check for level break
' If change in Emp_ID, write new record to EmpAllJobs with pivoted list of jobs
With rsOut
.AddNew
rsOut!Emp_ID = lngSavEmp_ID
rsOut!AllJobs = strAllJobs
.Update
End With
lngSavEmp_ID = rs!Emp_ID ' Save Emp_ID
strAllJobs = "" ' Clear field to hold jobs
Else
' Else - no change in Emp_ID, so save all jobs into string
strAllJobs = strAllJobs & rs!Job
End If
rs.MoveNext ' Next EmpJobs record
Loop

' After last record (and control group) write last pivoted record
With rsOut
.AddNew
rsOut!Emp_ID = lngSavEmp_ID
rsOut!AllJobs = strAllJobs
.Update
End With

' All done! Check the data!

End Sub
=========================
This is what my output looked like...

Emp_ID AllJobs
1 Job12Job13Job14Job15
2 Job17Job18Job19
3 Job21Job22

 

[Treason]

Mr. Harvey :

Great Coding! works fantastically...

Thank You So Very Much

 

[mrharvey]

You're welcome, I'm glad I could help.