Query with memo fields

[Mario Krsnic]

Hello everybody,
I have a query which gives good risults if I do not have strings longer then
255 in memo fields. In such case I get wrong risults. Can I improve this
query in some way?
Thanks
Mario

TRANSFORM sum(YesNoField)
SELECT WhatIsToDo.Tasks, Times.morning, Times.afternoon, Times.night
FROM Times INNER JOIN WhatIsToDo ON WhatIsToDo.id = Times.id
GROUP BY WhatIsToDo.Tasks, Times.morning, Times.afternoon, Times.night
ORDER BY Times.morning, Times.afternoon, Times.night, WhatIsToDo.Tasks
PIVOT day(TodayField)
in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,2
8,29,30,31);

 

[John Spencer]

You may be able to solve your problem by using FIRST on your memo field(s)
instead of Group By. Group By automatically truncates items to 255
characters so it can do the necessary comparison to do the grouping. The
bad part is that if you have multiple memo fields for the same group of data
could potentially lose some information. Try the following. I've removed
WhatIsToDo.Tasks from the Order By clause also.

Assumption:
WhatIsToDo.Tasks is your memo field

TRANSFORM sum(YesNoField)
SELECT First(WhatIsToDo.Tasks) as TheTask, Times.morning, Times.afternoon,
Times.night
FROM Times INNER JOIN WhatIsToDo ON WhatIsToDo.id = Times.id
GROUP BY Times.morning, Times.afternoon, Times.night
ORDER BY Times.morning, Times.afternoon, Times.night
PIVOT day(TodayField)
in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,2
8,29,30,31);

 

[Mario Krsnic]

Thank you, John!

You may be able to solve your problem by using FIRST on your memo field(s)
instead of Group By. ... The
bad part is that if you have multiple memo fields for the same group of data
could potentially lose some information.

After removing "GROUP BY WhatIsToDo.Tasks" I loose 80% of data. It seems to
be neccessary.
The query is the basis for a review of fullfilled tasks in a month.

Cheers
Mario