Display all sample numbers in one record (1 Viewer)

[Sniper-BoOyA-]

Good morning,

I am working on a database which is supposed to be able to aid the people working in our laboratory by doing calculations, making graphs etc.

Everything works great, but theres just one thing that i can not figure out.

The basic idea is, that the manager of the lab registers samples which need to be tested. (they are numbered)

In this process the person in the lab selects a sample on which a test has to be done with a 'Proctor' to calculate the Moisture content and the density of the particular sample.

In order to do that they take some of the original sample and put it in a mold, then the proctor machine hits the sample a couple of times.

Anyways, sometimes they stir two samples together (if they are the same) because its it useless to do the proctor test on both of the samples when they are the same...

Anyway, on the form to select the samples that need to be tested (the selected sample will get a proctorID which is an autonumbering field) , the user also gets to select the ProctorID's. Which are finally stored in a different table.

tblproctor = original table where the proctorid's are stored
tblsampleproctor = table where the stirred samples are stored

example :

tblproctorid has ProctorID 1 on sample 1
tblsampleproctor has ProctorID 1 on sample 2 and 3

Which basiacallt means that sample 1,2 and 3 are stirred together as one sample.

To combine the 2 tables above i used the UNION ALL function :

SELECT monsternr, proctorid
FROM tblproctor
UNION ALL SELECT monsternr, proctorid
FROM tblsampleproctor
ORDER BY proctorid;

Which combines both tables and displays them as one.

Example :

ProctorID _______ Samplenr

1 _______________1
1________________2
1________________3

Now, what i am trying to do is to display all samplenr's corresponding to proctorID1 in one field so i can show all the corresponding samplenr's on a report.

As in:

ProctorID___________Samplenr
1 __________________1+2+3

But i cant get it to work.

Any help would be much appreciated.

Ps. Sorry for the long post. Just trying to clear things up here and there so you guys know what im trying to do here.

Thanks in Advance!

Regards,

Michael Schrijver

 

[apr pillai]

You need a VBA Program. Copy and paste the following Code into a Standard Module and save it:

Public Function LabAssist()
Dim db As Database, rst As Recordset, rst2 As Recordset
Dim tbldef As TableDef, fld As Field
Dim prev_id, cur_id, txt As String

Set db = CurrentDb
Set rst = db.OpenRecordset("UnionQueryName")

GoSub TblDefCreate

On Error GoTo LabAssist_Err

Set rst2 = db.OpenRecordset("tblproctor2")
rst.MoveFirst
cur_id = rst![proctorid]
prev_id = cur_id
Do While Not rst.EOF
     txt = ""
   Do While cur_id = prev_id And Not rst.EOF
       If Len(txt) = 0 Then
          txt = rst![samplenr]
       Else
          txt = txt & "+" & rst![samplenr]
       End If
       rst.MoveNext
       If Not rst.EOF Then
           prev_id = cur_id
           cur_id = rst![proctorid]
       End If
   Loop
   rst2.AddNew
   rst2![proctorid] = prev_id
   rst2![samplenr] = txt
   rst2.Update
   prev_id = cur_id
Loop
rst.Close
rst2.Close

Set rst = Nothing
Set rst2 = Nothing
Set db = Nothing

LabAssist_Exit:
Exit Function


TblDefCreate:
Set tbldef = db.CreateTableDef("tblproctor2")
Set fld = tbldef.CreateField(rst.Fields(0).Name, rst.Fields(0).Type, rst.Fields(0).Size)
tbldef.Fields.Append fld
Set fld = tbldef.CreateField(rst.Fields(1).Name, rst.Fields(1).Type, rst.Fields(1).Size)
tbldef.Fields.Append fld

On Error Resume Next
db.TableDefs.Append tbldef
If Err > 0 Then
     db.TableDefs.Delete "tblproctor2"
     Err.Clear
     GoTo TblDefCreate
End If

db.TableDefs.Refresh

Return


LabAssist_Err:
MsgBox Err.Description, , "LabAssist()"
Resume LabAssist_Exit
End Function

Change the line: Set rst = db.OpenRecordset("UnionQueryName") to replace "UnionQueryName" with the correct name.

Run the code from a Command Button Click Event Procedure from a Form.

Creates a Table tblproctor2 to write out records from the Union Query. The Table tblproctor2 is overwritten every time the code is run.

Sample run image is attached.

 

[vbaInet]

And here's a common function:

http://allenbrowne.com/func-concat.html

 

[Sniper-BoOyA-]

Sorry for the late reply.

I will have a look.

Been messing around with crosstable queries, but this might be easier.

Thanks again.

 

[Sniper-BoOyA-]

Dear Apr Pillai,

I would like to thank you for your time and effort. That vba code worked like a charm.

Thanks again!