Group by time interval with Memo field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all

I have a table of data with the following fields
"Date" = Date/Time
"Remarks" = Memo

Sample data =
27/10/2005 10:13:04, "Event stated by A.N.Other"
27/10/2005 10:14:05, "Event ended by A.N.Other"

I have designed a query which will group the records by intervals of 15 mins
(starting at the start time of the 15 min period) using an expression (it's
quite long so i won't quote it here but it does work)

The result of the Query =
10:00, "Event started by A.N.Other"
10:00, "Event ended by A.N.Other"

So what is my issue. Well ideally I would like to concatenate the two (or
more) memo fields so that the query produces only one line in the result,
something like =

10:00, "Event started by A.N.Other & Event ended by A.N.Other"

What have I tried. Well I had limited succes with the expression field
"First([Date]) & " & " & Last([Date]). Two problems here, 1 - the Date field
content is duplicated if there is only one entry in the 15 min since it is
both the first and last, 2 - this does not capture results in between first
and last if there are more than 2 records in the group.

Anyone any ideas. i'd appreciate any helpful suggestions

Regards

Michael Bond
 
Steve

Looks good. Thanks for the prompt reply.

Regards

Michael Bond

[MVP] S.Clark said:
Duane Hookum's concatenate function can help.

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Click on:
Generic Function To Concatenate Child Records (46 KB) Access 2000

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting

mabond said:
Hello all

I have a table of data with the following fields
"Date" = Date/Time
"Remarks" = Memo

Sample data =
27/10/2005 10:13:04, "Event stated by A.N.Other"
27/10/2005 10:14:05, "Event ended by A.N.Other"

I have designed a query which will group the records by intervals of 15
mins
(starting at the start time of the 15 min period) using an expression
(it's
quite long so i won't quote it here but it does work)

The result of the Query =
10:00, "Event started by A.N.Other"
10:00, "Event ended by A.N.Other"

So what is my issue. Well ideally I would like to concatenate the two (or
more) memo fields so that the query produces only one line in the result,
something like =

10:00, "Event started by A.N.Other & Event ended by A.N.Other"

What have I tried. Well I had limited succes with the expression field
"First([Date]) & " & " & Last([Date]). Two problems here, 1 - the Date
field
content is duplicated if there is only one entry in the 15 min since it is
both the first and last, 2 - this does not capture results in between
first
and last if there are more than 2 records in the group.

Anyone any ideas. i'd appreciate any helpful suggestions

Regards

Michael Bond
Click to expand...
Click to expand...
 
Back
Top