TRUNCATION OF MEMO FIELD

G

Guest

Using the following SQL query below truncates the field
Dataset.AvailabilityGapNotes to 255 characters in the output. This field
originates in memo format which I obviously want to keep.

This statement was generated using query by example window in MS access 2003.

Any reworking of the statement below to keep Dataset.AvailabilityGapNotes as
memo would be appreciated.

Thanks.


SELECT Data_Requirement.DataRequirementGroup,
Data_Requirement.DataRequirement, Data_Requirement.DataPlanType,
Data_Requirement.CoverageRequired, Data_Requirement.SpatialScaleRequired,
Dataset.CoverageExisting, Dataset.DatasetName, Dataset.ScaleRangeExisting,
Dataset.AvailabilityStatus, Dataset.LicenseStatus,
Dataset.AccessRelationship, Dataset.Province, Dataset.AvailabilityGapNotes,
Min(AppsPriorities21Oct04.Phase) AS MinOfPhase,
Min(AppsPriorities21Oct04.PhasedRank) AS MinOfPhasedRank


FROM (Data_Requirement LEFT JOIN (Data_To_Application LEFT JOIN
AppsPriorities21Oct04 ON Data_To_Application.AppTitle =
AppsPriorities21Oct04.AppTitle) ON Data_Requirement.DataRequirement =
Data_To_Application.Data) INNER JOIN (Data_Requirement_To_Dataset INNER JOIN
Dataset ON Data_Requirement_To_Dataset.DatasetName = Dataset.DatasetName) ON
Data_Requirement.DataRequirement = Data_Requirement_To_Dataset.DataRequirement


GROUP BY Data_Requirement.DataRequirementGroup,
Data_Requirement.DataRequirement, Data_Requirement.DataPlanType,
Data_Requirement.CoverageRequired, Data_Requirement.SpatialScaleRequired,
Dataset.CoverageExisting, Dataset.DatasetName, Dataset.ScaleRangeExisting,
Dataset.AvailabilityStatus, Dataset.LicenseStatus,
Dataset.AccessRelationship, Dataset.Province, Dataset.AvailabilityGapNotes


HAVING (((Data_Requirement.DataPlanType)="Land Use") AND
((Min(AppsPriorities21Oct04.Phase)) Is Not Null))



ORDER BY Data_Requirement.DataRequirementGroup,
Data_Requirement.DataRequirement, Data_Requirement.DataPlanType,
Data_Requirement.SpatialScaleRequired, Dataset.CoverageExisting DESC;
 
D

Douglas J. Steele

As soon as you introduce a Group By, the truncation will happen. I can't
think of an alternate query that would give you the same information without
the truncation.
 
M

Marshall Barton

gawilkes said:
Using the following SQL query below truncates the field
Dataset.AvailabilityGapNotes to 255 characters in the output. This field
originates in memo format which I obviously want to keep.

This statement was generated using query by example window in MS access 2003.

Any reworking of the statement below to keep Dataset.AvailabilityGapNotes as
memo would be appreciated.

Thanks.


SELECT Data_Requirement.DataRequirementGroup,
Data_Requirement.DataRequirement, Data_Requirement.DataPlanType,
Data_Requirement.CoverageRequired, Data_Requirement.SpatialScaleRequired,
Dataset.CoverageExisting, Dataset.DatasetName, Dataset.ScaleRangeExisting,
Dataset.AvailabilityStatus, Dataset.LicenseStatus,
Dataset.AccessRelationship, Dataset.Province, Dataset.AvailabilityGapNotes,
Min(AppsPriorities21Oct04.Phase) AS MinOfPhase,
Min(AppsPriorities21Oct04.PhasedRank) AS MinOfPhasedRank


FROM (Data_Requirement LEFT JOIN (Data_To_Application LEFT JOIN
AppsPriorities21Oct04 ON Data_To_Application.AppTitle =
AppsPriorities21Oct04.AppTitle) ON Data_Requirement.DataRequirement =
Data_To_Application.Data) INNER JOIN (Data_Requirement_To_Dataset INNER JOIN
Dataset ON Data_Requirement_To_Dataset.DatasetName = Dataset.DatasetName) ON
Data_Requirement.DataRequirement = Data_Requirement_To_Dataset.DataRequirement


GROUP BY Data_Requirement.DataRequirementGroup,
Data_Requirement.DataRequirement, Data_Requirement.DataPlanType,
Data_Requirement.CoverageRequired, Data_Requirement.SpatialScaleRequired,
Dataset.CoverageExisting, Dataset.DatasetName, Dataset.ScaleRangeExisting,
Dataset.AvailabilityStatus, Dataset.LicenseStatus,
Dataset.AccessRelationship, Dataset.Province, Dataset.AvailabilityGapNotes


HAVING (((Data_Requirement.DataPlanType)="Land Use") AND
((Min(AppsPriorities21Oct04.Phase)) Is Not Null))



ORDER BY Data_Requirement.DataRequirementGroup,
Data_Requirement.DataRequirement, Data_Requirement.DataPlanType,
Data_Requirement.SpatialScaleRequired, Dataset.CoverageExisting DESC;
Click to expand...


To include the memo field, you need to strip it (and any
other non-core fields) out of yout query. Just keep the PK
field (DatasetName?) of the memo field's table, the
essential Goup By fields and the aggregate function in the
query. Also, leave out most(?) of the Joins and the Order
By clause. (I think(?) you should be using a Where clause
instead of a Having clause.)

Once you have that working without all the related data
fields but the right grouping, key fields and the calculated
Min fields, then create another query to Join the above
modified query to the Dataset ( and other?) table to pick up
the rest of the fields including the memo field. The Order
By clause would be part of this new query.
 
Top