Query on Dates to Collect All Data on Same Day

[stevetuf]

I have been tinkering with this for a while now, and I just can't get no satisfaction!
I need to create a query which pulls from a table that has three columns: Date, Task and Comment. The final output is to a pane in a month-to-view calendar. Right now, the data might come out a bit like this:

June 1, 2007[ ]Los Angeles, Mexican Eatery[ ] This will be a worthwhile venture
June 2, 2007[ ]Similar task - set a poem on a backdrop of Galicia [ ] Good, too
June 2, 2007[ ]Hermosa Beach - Jazz Party[ ] Set to music?
July 1, 2007[ ]Hidden Hills, CA - Bikers Romp[ ] We'll give this a miss
July 7, 2007[ ]Some other location, something else[ ] Rock Concert

The [ ] are what I have used to illustrate the columns. What is happening now is that for a date where there are two events on the same day, my output is showing two days: i.e.: there are two June 2, 2007's in my calendar. So, consequently, there are going to be two Saturdays.

What I really want to do is to create a query which takes the date, merges the data all on to one line, preserving the order of Tasks and Comments, and delimiting the data with a symbol, any symbol - it doesn't matter, just so long as the event are separate. Both Task and Comment are memo fields. I have so far managed to pipe into the memo field carriage returns and line feeds, so the data actually looks well formatted where it is displayed. However, the major show-stopper (!) is the fact that I cannot reprocess the data so that the stuff that happens on the same day appears one underneath the other in an ordered column. Any help gratefully received...
Best,
stevetuf

[breadhead]

Steve,

What you're describing seems like a task for a do...while loop, not a query. If you're not restricted to queries, I think the right approach might be to create a pair of recordsets - one for the dates and another for the individual records for each date, then loop through the second recordset a row at a time, writing the text to a variable and updating the table at the end of the recordset.

All that being said, I have an open thread where I'm struggling with a recordset, so this advice is worth only what you've paid for it.

Good luck, Mark

I have been tinkering with this for a while now, and I just can't get no satisfaction!
I need to create a query which pulls from a table that has three columns: Date, Task and Comment. The final output is to a pane in a month-to-view calendar. Right now, the data might come out a bit like this:

June 1, 2007[ ]Los Angeles, Mexican Eatery[ ] This will be a worthwhile venture
June 2, 2007[ ]Similar task - set a poem on a backdrop of Galicia [ ] Good, too
June 2, 2007[ ]Hermosa Beach - Jazz Party[ ] Set to music?
July 1, 2007[ ]Hidden Hills, CA - Bikers Romp[ ] We'll give this a miss
July 7, 2007[ ]Some other location, something else[ ] Rock Concert

The [ ] are what I have used to illustrate the columns. What is happening now is that for a date where there are two events on the same day, my output is showing two days: i.e.: there are two June 2, 2007's in my calendar. So, consequently, there are going to be two Saturdays.

What I really want to do is to create a query which takes the date, merges the data all on to one line, preserving the order of Tasks and Comments, and delimiting the data with a symbol, any symbol - it doesn't matter, just so long as the event are separate. Both Task and Comment are memo fields. I have so far managed to pipe into the memo field carriage returns and line feeds, so the data actually looks well formatted where it is displayed. However, the major show-stopper (!) is the fact that I cannot reprocess the data so that the stuff that happens on the same day appears one underneath the other in an ordered column. Any help gratefully received...
Best,
stevetuf

[garethfx]

I understand the query - no problem

Why not output to a report where you can group on date. Makes the job so simple

and ease to do in second

gareth

[stevetuf]

Steve,

What you're describing seems like a task for a do...while loop, not a query. If you're not restricted to queries, I think the right approach might be to create a pair of recordsets - one for the dates and another for the individual records for each date, then loop through the second recordset a row at a time, writing the text to a variable and updating the table at the end of the recordset.

All that being said, I have an open thread where I'm struggling with a recordset, so this advice is worth only what you've paid for it.

Good luck, Mark

Thanks Mark
I will try and use this approach, although, as you can see, it may be as simple as outputting it to a report (suggested by Gareth.) Whether I can fit each day's report into the date panes will be another matter though... I think your approach looks like the more elegant solution. I didn't really understand what you meant by "...this advice is worth only what you've paid for it." I pay a monthly subscription to Experts Exchange and frequently find that the responses to users' questions are not only dumb - they show remarkable lack of focus on the problem...! (Maybe I am not asking the sort of questions that produce the most elegant Experts Exchange answers...! LOL) What are you struggling with? Perhaps I can help?
Best,
~Steve

[stevetuf]

I understand the query - no problem

Why not output to a report where you can group on date. Makes the job so simple

and ease to do in second

gareth

Hi Gareth
Thanks for your answer. I have a feeling that putting each date's values into a separate report might be a tad too complicated... (Since there are no attachments or graphics in a text forum such as this, I can't really illustrate it sufficiently to show you why I don't think this would work...)
But I appreciate your response.
Best,
~Steve