Query on Dates to Collect All Data on Same Day | Newbie | | Join Date: Jul 2007 Location: Los Angeles, CA area
Posts: 3
| | |
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
| | Newbie | | Join Date: Jul 2007
Posts: 4
| | | re: Query on Dates to Collect All Data on Same Day
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 Quote:
Originally Posted by 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 | | Member | | Join Date: Apr 2007 Location: Wales
Posts: 49
| | | re: Query on Dates to Collect All Data on Same Day
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
| | Newbie | | Join Date: Jul 2007 Location: Los Angeles, CA area
Posts: 3
| | | re: Query on Dates to Collect All Data on Same Day
Quote:
Originally Posted by 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 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
| | Newbie | | Join Date: Jul 2007 Location: Los Angeles, CA area
Posts: 3
| | | re: Query on Dates to Collect All Data on Same Day
Quote:
Originally Posted by 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 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
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,353 network members.
|
