Take the memo field out of the GROUP BY clause, and change it in the
SELECT clause to a First([LastStatusReportAll.StatusReport]) instead.
SELECT
LastStatusReportAll.ProjectID,
LastStatusReportAll.ProjectName,
LastStatusReportAll.Status,
tbl_L1_Employees.Alias,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.DeptPriority,
LastStatusReportAll.ProjectBeginDate,
LastStatusReportAll.ProjectSubClass,
LastStatusReportAll.StatusDate,
LastStatusReportAll.ProjectEndDate,
LastStatusReportAll.ActualCompletionDate,
LastStatusReportAll.Last_Status_Date,
LastStatusReportAll.ProjectLeader,
LastStatusReportAll.CapEx,
LastStatusReportAll.EIP,
LastStatusReportAll.[06s],
LastStatusReportAll.Projects.MIP,
LastStatusReportAll.[R&D],
LastStatusReportAll.ProjectTotalCostEstimate,
LastStatusReportAll.EstimatedSavings,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.Benefits,
LastStatusReportAll.CorpPriority,
LastStatusReportAll.ActualCompDate,
First(LastStatusReportAll.StatusReport) AS StatusReport,
LastStatusReportAll.Projects.ProjectType
FROM
(LastStatusReportAll INNER JOIN tbl_Employee_Project_rel ON
LastStatusReportAll.ProjectID =
tbl_Employee_Project_rel.emp_project_rel_ProjectID) INNER JOIN
tbl_L1_Employees ON tbl_Employee_Project_rel.emp_project_rel_EmpID =
tbl_L1_Employees.ClockNums
GROUP BY
LastStatusReportAll.ProjectID,
LastStatusReportAll.ProjectName,
LastStatusReportAll.Status,
tbl_L1_Employees.Alias,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.DeptPriority,
LastStatusReportAll.ProjectBeginDate,
LastStatusReportAll.ProjectSubClass,
LastStatusReportAll.StatusDate,
LastStatusReportAll.ProjectEndDate,
LastStatusReportAll.ActualCompletionDate,
LastStatusReportAll.Last_Status_Date,
LastStatusReportAll.ProjectLeader,
LastStatusReportAll.CapEx,
LastStatusReportAll.EIP,
LastStatusReportAll.[06s],
LastStatusReportAll.Projects.MIP,
LastStatusReportAll.[R&D],
LastStatusReportAll.ProjectTotalCostEstimate,
LastStatusReportAll.EstimatedSavings,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.Benefits,
LastStatusReportAll.CorpPriority,
LastStatusReportAll.ActualCompDate,
LastStatusReportAll.Projects.ProjectType;
--
Ken Snell
<MS ACCESS MVP>
Bill - ESAI said:
yep, GROUP BY
SELECT
LastStatusReportAll.ProjectID,
LastStatusReportAll.ProjectName,
LastStatusReportAll.Status,
tbl_L1_Employees.Alias,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.DeptPriority,
LastStatusReportAll.ProjectBeginDate,
LastStatusReportAll.ProjectSubClass,
LastStatusReportAll.StatusDate,
LastStatusReportAll.ProjectEndDate,
LastStatusReportAll.ActualCompletionDate,
LastStatusReportAll.Last_Status_Date,
LastStatusReportAll.ProjectLeader,
LastStatusReportAll.CapEx,
LastStatusReportAll.EIP,
LastStatusReportAll.[06s],
LastStatusReportAll.Projects.MIP,
LastStatusReportAll.[R&D],
LastStatusReportAll.ProjectTotalCostEstimate,
LastStatusReportAll.EstimatedSavings,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.Benefits,
LastStatusReportAll.CorpPriority,
LastStatusReportAll.ActualCompDate,
LastStatusReportAll.StatusReport,
LastStatusReportAll.Projects.ProjectType
FROM
(LastStatusReportAll INNER JOIN tbl_Employee_Project_rel ON
LastStatusReportAll.ProjectID =
tbl_Employee_Project_rel.emp_project_rel_ProjectID) INNER JOIN
tbl_L1_Employees ON tbl_Employee_Project_rel.emp_project_rel_EmpID =
tbl_L1_Employees.ClockNums
GROUP BY
LastStatusReportAll.ProjectID,
LastStatusReportAll.ProjectName,
LastStatusReportAll.Status,
tbl_L1_Employees.Alias,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.DeptPriority,
LastStatusReportAll.ProjectBeginDate,
LastStatusReportAll.ProjectSubClass,
LastStatusReportAll.StatusDate,
LastStatusReportAll.ProjectEndDate,
LastStatusReportAll.ActualCompletionDate,
LastStatusReportAll.Last_Status_Date,
LastStatusReportAll.ProjectLeader,
LastStatusReportAll.CapEx,
LastStatusReportAll.EIP,
LastStatusReportAll.[06s],
LastStatusReportAll.Projects.MIP,
LastStatusReportAll.[R&D],
LastStatusReportAll.ProjectTotalCostEstimate,
LastStatusReportAll.EstimatedSavings,
LastStatusReportAll.Projects.Department,
LastStatusReportAll.Benefits,
LastStatusReportAll.CorpPriority,
LastStatusReportAll.ActualCompDate,
LastStatusReportAll.StatusReport,
LastStatusReportAll.Projects.ProjectType;
It's the "LastStatusReportAll.StatusReport" that is the cultrit.
Ken Snell (MVP) said:
See Allen Browne's website for detailed information about truncation of
memo
fields:
http://allenbrowne.com/ser-63.html
This URL shows two posts from Jamie Collins where he tested 7 different
scenarios for possible memo trunction:
http://groups.google.com/group/micr...4e2d1b65a60e7/489970072eafe7a3?hl=en&lnk=st&q
Is your query using one of the "truncation" scenarios?
--
Ken Snell
<MS ACCESS MVP>
Hi All
I have a weird one here.
I have a database project that is spit. The presentation layer is using
Linked Tables to the data layer.
In one of my tables I have Memo field that typically holds a rather
large amount of text per record. Then I have a report that is powered
by two queries. A query of a query actually. For some reason when I run
the report as well as the query (if I run the query standalone) this
field in question only returns about 150 or 200 characters then cuts
off the rest.
If I go to the data file and look up that same record then all the data
is there. All of the fields are working fine and I have many others
that are retrieved the same way that are working fine. It's just this
one that is a little troublesome.
Any ideas as to why this is happening would be greatly appreciated.
Thank
Bill
