code for setting a field value in the matrix

[detdu]

if I have two tables,,, name och job... and want it to look like this for an example

Job
Name1 1,2
Name2 3

Fist name is anna and second lisa... lets say that the job is programmer... how can i write this in the simplest and easiest way... the numbers are there ID:s... two different anna:s are working as a programmet... and one lisa... can someone give me ideas?

 

[CompGirl]

if you want the easiest way out then use crossroadtab. then make a normal query where you specify the raw fields. then you just put the boxes on your report and add something like this in the report code:

Dim ReportLabel(7) As String 'the amount

Private Sub Report_Open(Cancel As Integer)
Dim I As Integer
For I = 0 To 7
ReportLabel(I) = ""
Next I
Call CreateReportQuery
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim I As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("Crosstabqueryname&quot
indexx = 0
For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & &quot;[&quot; & fld.Name & &quot;] as Field&quot; & indexx & &quot;, &quot;
ReportLabel(indexx) = fld.Name
End If
indexx = indexx + 1
Next fld
For I = indexx To 7
FieldList = FieldList & &quot;null as Field&quot; & I & &quot;,&quot;
Next I
FieldList = Left(FieldList, Len(FieldList) - 2)

strSQL = &quot;Select &quot; & FieldList & &quot; From Crosstabqueryname&quot;

db.QueryDefs.Delete &quot;normalquery&quot;
Set qdf = db.CreateQueryDef(&quot;normalquery&quot;, strSQL)


Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_CreateQuery
End If
End Sub

Function FillLabel(LabelNumber As Integer) As String
FillLabel = Nz(ReportLabel(LabelNumber), &quot;&quot

End Function