Return Start & Stop Times by Date

[smc1155]

I have a table containing log data about file transfers. Each arrival creates a unique record with file name, date, and time. Each delivery creates unique record with file name, date, and time. A sample is below with the IN and OUT pair. The underscore "_" are to keep spacing in the post only and are not in the date.

Source_File____Dest_File________Event_Date__Event_ Time
==============_================_===========_====== =====
ORGFile1.txt___DLIVFile1.txt____1/1/2016____22:09:03
DLIVFile1.txt__TGTFile1.txt_____1/1/2016____22:12:45

ORGFile1.txt___DLIVFile1.txt____1/3/2016____23:41:14
DLIVFile1.txt__TGTFile1.txt_____1/3/2016____23:43:54

ORGFile2.txt___DLIVFile2.txt____1/1/2016____22:11:33
DLIVFile2.txt__TGTFile2.txt_____1/1/2016____22:13:02

ORGFile2.txt___DLIVFile2.txt____1/2/2016____22:08:44
DLIVFile2.txt__TGTFile2.txt_____1/2/2016____22:10:12

ORGFile2.txt___DLIVFile2.txt____1/3/2016____22:22:01
DLIVFile2.txt__TGTFile2.txt_____1/3/2016____22:25:15




I need to present the file name with arrived time and delivery time for each given date in an Excel spreadsheet similar to below.

_____________________1/1/2016____________1/2/2016____________1/3/2016
DLIVFile1.txt___22:09:03_22:12:45_______________________23:41:14 23:43:54
DLIVFile2.txt___22:11:33_22:13:02___22:08:44 22:10:12___22:22:01 22:25:15


I'm looking for a way to produce an output similar to the Excel spreadsheet to export the results or cut/paste the results.

In short the problem statement is this:
I need to concatenate related records and report the IN and OUT times by date in way which can be easily imported or cut/pasted into a single linear Excel record.

Any help is appreciated.

[June7]

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

[smc1155]

I think this would work if I were gathering 1 key and concatenating values from a single field. In this case I have 1 key (FileName) and I need to retrieve values from multiple fields...Date, Arrive_Time, Send_Time. This would be an easy problem to solve if it were possible to create a Crosstab Query with 2 values.

FileName = Rows
Dates = Columns
Arrive = Value
Send = Value

The resulting table would be perfect.

[June7]

Allen's code can be adapted to concatenated values from multiple fields.

The real complication in your situation is the 'header' row of dates you want to generate.

As for CROSSTAB, another Allen Browne reference http://allenbrowne.com/ser-67.html#MultipleValues

Another approach would be VBA writing records to a 'temp' table.