Concatenate multiple child records, with criteria 1

[Jade3942]

I'm trying to produce a crosstab report with some concatenated data... I've successfully implemented Duanes alias style crosstab report, but come a bit unstuck where I now have potential multiple values at each matrix intersection.
Rather than produce seperate fields for the extra values it would be easier on the eye if I could concatenate the various child records (all from one child table, linked by autonumber field) into the single displayable field.

I've tried using the basic concatenate function (again Duanes) but this seems to pull ALL the child records into the display field, admittedly it does this correctly by each employees ID...but I need to further filter those child records by job start date is there a way to apply date criteria to this function?
So that the function will only concatenate the data by employee id and then by job start date for each given date?

Any help at all would be appreciated..

 

[dhookom]

This should be possible. It's difficult to help without your table and field names as well as your existing SQL views.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Use a WHERE clause in the Concatenete function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Jade3942]

The SQL for the query that combines data for the crosstab to work on is as follows:

SELECT [ISSUE LOG].[ISSUE BRANCH ID], [JOB DETAIL].[START DATE], [RING AROUND].[TEMP ID], [RING AROUND].OUTCOME, CLIENT.[COMPANY NAME], [JOB DETAIL].[START TIME], [JOB DETAIL].[END TIME], TEMPS.[FULL NAME], [job detail].[issue id] & Chr(13) & Chr(10) & [company name] & Chr(13) & Chr(10) & "[" & [start time] & "]" & " - " & "[" & [end time] & "]" & Chr(13) & Chr(10) & [skill name] & Chr(13) & Chr(10) & [start date] & Chr(13) & Chr(10) & [type] AS Booking, [ISSUE LOG].[ISSUE ID], [start date] & " " & [start time] AS startddhh, [Copy Of SKILL LIST].[SKILL NAME2], [SKILL LIST].[SKILL NAME], [RING AROUND].[SLOT CANCELLED], [RING AROUND].[SLOT COVERED], [RING AROUND].[SLOT MODDED]
FROM (([SKILL LIST] INNER JOIN (TEMPS INNER JOIN (CLIENT INNER JOIN ([ISSUE LOG] INNER JOIN ([JOB DETAIL] INNER JOIN [RING AROUND] ON [JOB DETAIL].[JOB DETAIL ID] = [RING AROUND].[JOB DETAIL ID]) ON [ISSUE LOG].[ISSUE ID] = [JOB DETAIL].[ISSUE ID]) ON CLIENT.[CLIENT ID] = [ISSUE LOG].[ISSUE CLIENT ID]) ON TEMPS.[TEMP ID] = [RING AROUND].[TEMP ID]) ON [SKILL LIST].ID = [JOB DETAIL].SKILL) INNER JOIN [Copy Of SKILL LIST] ON TEMPS.[PRIMARY SKILL] = [Copy Of SKILL LIST].ID) INNER JOIN [ISSUE TYPES] ON [ISSUE LOG].[ISSUE TYPE] = [ISSUE TYPES].ID
WHERE ((([ISSUE LOG].[ISSUE BRANCH ID])=[Forms]![Main Page]![List305]) AND (([JOB DETAIL].[START DATE]) Between [Forms]![Main Page]![Text299] And [Forms]![Main Page]![Text301]) AND (([RING AROUND].OUTCOME)=8 Or ([RING AROUND].OUTCOME)=15) AND (([RING AROUND].[SLOT CANCELLED])=0) AND (([RING AROUND].[SLOT COVERED])=0) AND (([RING AROUND].[SLOT MODDED])=0));


The Crosstab takes the results of the above and does the following

PARAMETERS [Forms]![Main Page]![List305] Short, [Forms]![Main Page]![Text299] DateTime, [Forms]![Main Page]![Text301] DateTime;
TRANSFORM First([CROSS TAB1].Booking) AS FirstOfBooking
SELECT [CROSS TAB1].[FULL NAME], [CROSS TAB1].[SKILL NAME2]
FROM [CROSS TAB1]
GROUP BY [CROSS TAB1].[FULL NAME], [CROSS TAB1].[SKILL NAME2]
PIVOT ([start date])-([Forms]![Main Page]![Text301]) In (-7,-6,-5,-4,-3,-2,-1);


The seperate components of a "Booking" are combined in the first query to form the "value" at the matrix intersection for each day for any given employee... the problem arises when for a single day there are 2 or more values at that intersection.. I would rather not see the employees name twice on the row to account for the second value.

Any help really would be appreciated..

 

[Jade3942]

Apologies I ommitted the SQL for the concatenate that I have in testing...
The test version of the concatenate SQL I am using is as follows...the data sheet view shows the bookings being combined, but sans any consideration to the start date..I think the WHERE clause approach should work however the info still has to be fed into the cross-tab query above


SELECT [Copy Of CROSS TAB1 pre concat].[TEMP ID], Concatenate("SELECT [start date] & ' #' & [booking] & '#' & chr(13) & chr(10) FROM [copy of cross tab1 pre concat] WHERE [temp id] =" & [temp id]) AS Bookings, [Copy Of CROSS TAB1 pre concat].*
FROM [Copy Of CROSS TAB1 pre concat];

 

[dhookom]

Your concatenate function needs to include the filter for start date. I'm not sure which queries and tables contain this fields but try something like:

Concatenate("SELECT [start date] & ' #' & [booking] & '#' & chr(13) & chr(10) FROM [copy of cross tab1 pre concat] WHERE [temp id] =" & [temp id] & " AND [Start Date]=#" & [Start Date] & "#" ) AS Bookings

Duane
Hook'D on Access
MS Access MVP

 

[Jade3942]

All the fields are available from the query [Copy of CROSS TAB1 pre concat]

The added code you suggest throws and "INVALID SYNTAX" error.. it doesn't like the "AS Bookings" part ?

Stripping that part off the statement allows the query to run, but most of the data is then lost for some reason...though oddly it does concatenate *some* of the data,cannot see quite how it picked that particular data though.

Sorry I'm not an expert on SQL syntax or coding, am I misunderstanding the code modifcation you posted?

I've thought of an alternate method to achieve almost the same result... how about a cross-tab that returns the "LAST" value at the intersection.. combine that with some code to remove the duplicate "FIRST" values and then I could base a report off the combination of the two cross-tab queries.. would that work? (though it would limit intersection values to a maximum of 2..).

Appreciate your efforts thus far, any further light you could shed would be helpful...

 

[dhookom]

I'm not sure why you got the invalid syntax unless possibly there is a null Start Date. It would help to see the resulting syntax from implementing my suggestion.

Does the Start Date contain any time element?

Duane
Hook'D on Access
MS Access MVP

 

[Jade3942]

Ignore the Syntax Error mentioned above.. I copied the code you provided and placed it into the design mode column... (DOH!!)

Ok I added the code to the SQL view and it executes but it definitely is not doing what we would expect.. It seems only interested in pulling records from the last 2 days of any range given though I cannot see the pattern for them and it does not get all of them either. The others it leaves blank.

The code is as follows:

SELECT [Copy Of CROSS TAB1 pre concat].[FULL NAME], [Copy Of CROSS TAB1 pre concat].[TEMP ID], Concatenate("SELECT [start date] & ' #' & [booking] & '#' & chr(13) & chr(10) FROM [copy of cross tab1 pre concat] WHERE [temp id] =" & [temp id] & " AND [Start Date]=#" & [Start Date] & "#") AS bookings
FROM [Copy Of CROSS TAB1 pre concat];


The start date has no time element, only a date formatted as a "short date"
Instead of a WHERE clause is this not a more GROUP BY situation?

Would a copy of the relevant components help to diagnose the problem?

 

[dhookom]

This is a bit difficult to trouble-shoot without seeing your results. You can kick your own debugging efforts up a bit using the debug window. Press Ctrl+G to open the window. Then enter something like:

? Concatenate("SELECT [start date] & ' #' & [booking] & '#' & chr(13) & chr(10) FROM [copy of cross tab1 pre concat]  WHERE [temp id] =999 AND [Start Date]=#1/1/2008#")

The above should all be on one line replacing 999 and 1/1/2008 with appropriate values from your data.

Duane
Hook'D on Access
MS Access MVP

 

[Jade3942]

Okay, I've had a go at seeing if there is some common factor in the data it does concatenate, which it does correctly when it does it. Thats the good news!!..so at least I can see it's a theoretically viable approach.

The vast majority out of a sample of 4875 records show missing concatenations from the 01-12th of every month Jan to April, it does concatenate a few of them but leaves out 80% for the 01-12th of them resulting in 1603 lost values. The date format I have in use is UK standard DD/MM/YY, [start date] is actually stored as a Medium Date, so 01/01/2008 is 01-Jan-08

I am thinking there is something in the concatenate vba code or the data set of those particular records itself that is causing it to move to the next record without performing the concatenation.

Is it possible the problem is the delimiter used in the code below?
Sadly my coding skills would be best described as a work in progress.. I can grasp algorithms but code syntax is difficult.

The concatenation code I am using is as follows:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
'adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

The current SQL for the query itself is still:

SELECT [Copy Of CROSS TAB1 pre concat].[START DATE], [Copy Of CROSS TAB1 pre concat].[FULL NAME], [Copy Of CROSS TAB1 pre concat].[TEMP ID], [Copy Of CROSS TAB1 pre concat].[RINGAROUND ID], Concatenate("SELECT [start date] & ' #' & [booking] & '#' & chr(13) & chr(10) FROM [copy of cross tab1 pre concat] WHERE [temp id] =" & [temp id] & " AND [Start Date]=#" & [Start Date] & "#") AS bookings
FROM [Copy Of CROSS TAB1 pre concat];

 

[dhookom]

It would have helped a long while back if you would have mentioned you are from UK. The date format used in MS Access queries is the m/d/y format. You might want to try something like:

SELECT [Copy Of CROSS TAB1 pre concat].[START DATE], [Copy Of CROSS TAB1 pre concat].[FULL NAME], [Copy Of CROSS TAB1 pre concat].[TEMP ID], [Copy Of CROSS TAB1 pre concat].[RINGAROUND ID], Concatenate("SELECT [start date] & ' #' & [booking] & '#' & chr(13) & chr(10) FROM [copy of cross tab1 pre concat]  WHERE [temp id] =" & [temp id] & " AND [Start Date]=#" & Format([Start Date],"dd-mmm-yyyy") & "#") AS bookings
FROM [Copy Of CROSS TAB1 pre concat];

Duane
Hook'D on Access
MS Access MVP

 

[Jade3942]

Perfect!!!

Thank you for all you help!... lesson learned on date formats and queries.

The report is fine now... cheers!.