Query with memo fields

  • Thread starter Thread starter Mario Krsnic
  • Start date Start date
M

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);
 
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);
 
Thank you, John!

John Spencer said:
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.
Click to expand...

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
 
Back
Top