Group is Limiting My Field!

[jt463]

I have read several threads that cause me to conclude that Access limits the characters in the field of a query to 255 if my SQL includes a 'SELECT DISTINCT' or a 'GROUP BY'.

This creates problems for me since I need to pull back more than 255 characters for a certain field.

When I remove the 'GROUP BY' criteria, I get multiple records of the same values.

Ultimately, I have a report that is built from this query that I only want one record to show. Unfortunately, the 'Hide Duplicates' property does not solve my problem.

Thanks in advance for any help anyone can provide.

SQL (The specific field with more than 255 characters is 'MonthActivity'):
SELECT ven_calendar_report.building_id_, cal_temp.report_date1, cal_temp.report_date2, cal_temp.report_date3, cal_temp.report_date4, cal_temp.report_date5, ven_calendar_report.project_id_, ven_calendar_report.Development, ven_calendar_report.ship_to_address_, ven_calendar_report.Permit, ven_calendar_report_activity.Activity1, ven_calendar_report_activity.Activity2, ven_calendar_report_activity.Activity3, ven_calendar_report_activity.Activity4, ven_calendar_report_activity.Activity5, ven_thirty_activity.MonthActivity
FROM ((cal_temp INNER JOIN ven_calendar_report ON cal_temp.UserID = ven_calendar_report.userid) INNER JOIN ven_calendar_report_activity ON (ven_calendar_report.project_id_ = ven_calendar_report_activity.project_id_) AND (ven_calendar_report.building_id_ = ven_calendar_report_activity.building_id_)) INNER JOIN ven_thirty_activity ON (ven_calendar_report_activity.project_id_ = ven_thirty_activity.project_id_) AND (ven_calendar_report_activity.building_id_ = ven_thirty_activity.building_id_)
GROUP BY ven_calendar_report.building_id_, cal_temp.report_date1, cal_temp.report_date2, cal_temp.report_date3, cal_temp.report_date4, cal_temp.report_date5, ven_calendar_report.project_id_, ven_calendar_report.Development, ven_calendar_report.ship_to_address_, ven_calendar_report.Permit, ven_calendar_report_activity.Activity1, ven_calendar_report_activity.Activity2, ven_calendar_report_activity.Activity3, ven_calendar_report_activity.Activity4, ven_calendar_report_activity.Activity5, ven_thirty_activity.MonthActivity;

 

[lespaul]

is the field 'MonthActivity' a memo field?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi