Grouping text strings in query result (1 Viewer)

ponneri · Oct 15, 2017

Hi.
I have a table that records material received. The main fields are MatID, MatName, DateRecd, ClrdOn, ClrdTo, Status. Any material can come any number of times in a month.

Each time it comes, it is stored as a record. Example :
MatID, MatName, DateRecd, ClrdOn, ClrdTo, Status
M121, Steel rods,12-sep-17,14-Sep-17,Returned to supplier, Material defective
M121,Steel rods,17-Sep-17,18-Sep-17,Sent to Lab, Material satisfactory
M121,Steel rods,20-Sep-17,22-Sep-17,Sent to Foundry, Material as per specs

At any given time, I need a query that shows material received, date wise grouped on MatID with the Status column showing the entire history of material received, as follows:

M121,Steel Rods, Recd on 12-sep-17 and Returned to supplier on 14-Sep-17, as Material defective
Recd on 17-sep-17 and Sent to Lab on 18-Sep-17, as Material satisfactory
.... and so on

What I mean is, the Status column in query result must contain entire history as text info string. Is this possible ?

Any help appreciated.

CJ_London · Oct 15, 2017

take a look at the concat related function

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

be aware of the 255 char limit for strings, not tested but the variant datatype might work OK for a memo datatype

ponneri · Oct 16, 2017

Thanks ! I'll try that.

arnelgp · Oct 16, 2017

you need to make a special function
to gather all the three fields in
single history field.

put this in a Standard Module:

Code:

Public Function concatStatus(matID As Variant) As String
	Dim db As DAO.Database
	Dim rs As DAO.RecordSet
	Dim strReturn As String
	Set db =  CurrentDB
	Set rs = db.OpenRecordSet( _
		"SELECT DateRecd, ClrdOn, ClrdTo, Status FROM yourTable " & _
		"WHERE MatID =  " & madID & " ORDER BY DateRecd;", _
		dbOpenSnapShot)
	WITH rs
		IF NOT (.BOF AND .EOF) Then .MoveFirst
		WHILE NOT .EOF
			strReturn = strReturn & !DateRecd & " : Received"
			strReturn = strReturn & (vbCrLf + !ClrdOn + " : " + !Status)
			strReturn = strReturn & (vbCrLf + !ClrdTo + " : " + !Status) & vbCrLf
			.MoveNext
		Wend
		.Close
	End With
	Set rs = Nothing
	Set db = Nothing
	concatStatus = strReturn
End Function

YOUR query should look:

SELECT MatID, MatName, Status:concatStatus(MatID) FROM yourTable GROUP BY MatID, MatName, concatStatus(MatID)

'***
Remember to change "yourTable" with the name of your table.

Grouping text strings in query result (1 Viewer)

ponneri

Registered User.

CJ_London

Super Moderator

ponneri

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread