Truncated Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My query shown below is working well with the exception that the ratings
field gets truncated to 255 characters. I have researched in the newgroups
and I cannot find a solution to overcome this. Any help would be sincerely
appreciated.

SELECT tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type], tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, Concatenate("SELECT AllFields FROM
qry_Ratings_Order WHERE SiteID =" & [tbl_Site_Info.SiteID]) AS Ratings
FROM tbl_Site_Info LEFT JOIN qry_Ratings_Order ON tbl_Site_Info.SiteID =
qry_Ratings_Order.SiteID
GROUP BY tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type], tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, Concatenate("SELECT AllFields FROM
qry_Ratings_Order WHERE SiteID =" & [tbl_Site_Info.SiteID]),
tbl_Site_Info.SiteID
ORDER BY tbl_Site_Info.Site;
 
TinleyParkILGal said:
My query shown below is working well with the exception that the ratings
field gets truncated to 255 characters. I have researched in the newgroups
and I cannot find a solution to overcome this. Any help would be sincerely
appreciated.

SELECT tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type], tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, Concatenate("SELECT AllFields FROM
qry_Ratings_Order WHERE SiteID =" & [tbl_Site_Info.SiteID]) AS Ratings
FROM tbl_Site_Info LEFT JOIN qry_Ratings_Order ON tbl_Site_Info.SiteID =
qry_Ratings_Order.SiteID
GROUP BY tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type], tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, Concatenate("SELECT AllFields FROM
qry_Ratings_Order WHERE SiteID =" & [tbl_Site_Info.SiteID]),
tbl_Site_Info.SiteID
ORDER BY tbl_Site_Info.Site;
Click to expand...


That happens when you use a memo field in the GROUP BY
clause.

Why are you using a GROUP BY clause without using an
aggregate function? The only purpose that can serve is to
eliminate the possibility of duplicate records, which is
better dealt with by using the DISTINCT predicate.
 
Any field you Group By is going to get truncated to 255 characters.

Try using First() for the ratings and dropping it from the Group By clause.

SELECT tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type], tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, First(Concatenate("SELECT AllFields FROM
qry_Ratings_Order WHERE SiteID =" & [tbl_Site_Info.SiteID])) AS Ratings
FROM tbl_Site_Info LEFT JOIN qry_Ratings_Order ON tbl_Site_Info.SiteID =
qry_Ratings_Order.SiteID
GROUP BY tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type], tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, tbl_Site_Info.SiteID
ORDER BY tbl_Site_Info.Site;

TinleyParkILGal said:
My query shown below is working well with the exception that the ratings
field gets truncated to 255 characters. I have researched in the newgroups
and I cannot find a solution to overcome this. Any help would be sincerely
appreciated.

SELECT tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type],
tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, Concatenate("SELECT AllFields FROM
qry_Ratings_Order WHERE SiteID =" & [tbl_Site_Info.SiteID]) AS Ratings
FROM tbl_Site_Info LEFT JOIN qry_Ratings_Order ON tbl_Site_Info.SiteID =
qry_Ratings_Order.SiteID
GROUP BY tbl_Site_Info.RegionName, tbl_Site_Info.Site,
tbl_Site_Info.Platinum_Club, tbl_Site_Info.[Club Type],
tbl_Site_Info.Holes,
tbl_Site_Info.Address1, tbl_Site_Info.Address2, tbl_Site_Info.City,
tbl_Site_Info.State, tbl_Site_Info.Zip, tbl_Site_Info.Phone,
tbl_Site_Info.[Pro Shop], tbl_Site_Info.Fax, tbl_Site_Info.[Web Site],
tbl_Site_Info.Location, tbl_Site_Info.OnlinePosting,
tbl_Site_Info.Web_Renewal, tbl_Site_Info.Pro, tbl_Site_Info.Manager,
tbl_Site_Info.Superintendent, tbl_Site_Info.Green_Fee_18_Hole_High,
tbl_Site_Info.Green_Fee_18_Hole_Low, tbl_Site_Info.[Cart Required],
tbl_Site_Info.Cart_Fee_Low, tbl_Site_Info.Cart_Fee_High,
tbl_Site_Info.[Advance Tee Times], tbl_Site_Info.[9-Hole Leagues],
tbl_Site_Info.[Driving Range], tbl_Site_Info.[Locker Rooms],
tbl_Site_Info.[Banquet Space], tbl_Site_Info.RentalClubs,
tbl_Site_Info.ClubRepairs, Concatenate("SELECT AllFields FROM
qry_Ratings_Order WHERE SiteID =" & [tbl_Site_Info.SiteID]),
tbl_Site_Info.SiteID
ORDER BY tbl_Site_Info.Site;
Click to expand...
 
Back
Top