Union Query Problems
From: bdehning (bdehning_at_discussions.microsoft.com)
Date: 09/17/04
- Next message: Steve: "Re: Count Consecutive Months"
- Previous message: xian: "express"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 16 Sep 2004 23:25:02 -0700
I have 2 queries which work fine by themselves but having trouble getting
union query to work without getting error and terminating program.
Here is first Query:
SELECT Location.[Assigned Consultant], [Service Calls].[Service Call
Number], [Account Information].[Account Name], [Service Calls].[Scheduled
Service Month], [Account Information].[Policy Number], Location.[Location
Servicing Division], Location.[Location Address], Location.[Location City],
Location.[Location State], [Service Calls].[Date of Survey], [Service
Calls].[Date Written Report Sent], IIf(Len(Trim$([FirstofComments] &
""))=0,"No Comments",[FirstofComments]) AS NonBlankComments, [Service
Calls].[Service Call Type], [Original Service Call Month].[Original Service
Call Date], [Service Calls].[Rescheduled Service Month], [Account
Information].[Expiration Date], [Service Calls].[Cancel Service Call],
[Service Calls].[Waive Service Call], [Service Calls].[Service Frequency],
[Service Calls].Evaluation, [Service Calls].Recommendations, First([Service
Calls].Comments) AS FirstOfComments
FROM ([Account Information] RIGHT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]) INNER JOIN ([Service
Calls] INNER JOIN [Original Service Call Month] ON [Service Calls].[Service
Call Number] = [Original Service Call Month].[Service Call Number]) ON
Location.[Location ID] = [Service Calls].[Location ID]
GROUP BY Location.[Assigned Consultant], [Service Calls].[Service Call
Number], [Account Information].[Account Name], [Service Calls].[Scheduled
Service Month], [Account Information].[Policy Number], Location.[Location
Servicing Division], Location.[Location Address], Location.[Location City],
Location.[Location State], [Service Calls].[Date of Survey], [Service
Calls].[Date Written Report Sent], [Service Calls].[Service Call Type],
[Original Service Call Month].[Original Service Call Date], [Service
Calls].[Rescheduled Service Month], [Account Information].[Expiration Date],
[Service Calls].[Cancel Service Call], [Service Calls].[Waive Service Call],
[Service Calls].[Service Frequency], [Service Calls].Evaluation, [Service
Calls].Recommendations
HAVING ((([Service Calls].[Date Written Report Sent]) Is Not Null))
ORDER BY Location.[Assigned Consultant];
Here is 2nd query
SELECT Remarks.[Remarks #], First(Remarks.Remarks) AS FirstOfRemarks,
Remarks.[Date Remarks Entered], Remarks.[Remark Entered By], [Account
Information].[Account Name], [Account Information].[Policy Number],
IIf(Len(Trim$([FirstofRemarks] & ""))=0,"No Comments",[FirstofRemarks]) AS
NonBlankComments, Remarks.Evaluation, Remarks.Recommendations,
Location.[Location Servicing Division]
FROM ([Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]) INNER JOIN Remarks
ON [Account Information].[Policy Number] = Remarks.[Policy Number]
GROUP BY Remarks.[Remarks #], Remarks.[Date Remarks Entered],
Remarks.[Remark Entered By], [Account Information].[Account Name], [Account
Information].[Policy Number], Remarks.Evaluation, Remarks.Recommendations,
Location.[Location Servicing Division];
The Union query works fine until I try to add the nonblankcomments field
from each. This field is created to get around the 255 character limit with
memo field in quires and reports. I must be doing something wrong with the
SQL.
Here is what I am using:
SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT INFORMATION].[ACCOUNT
NAME],[FIRSTOFCOMMENTS],[DATE WRITTEN REPORT
SENT],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [ACCOUNT SERVICE RECORD FOR REMARKS-COMMENTS]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*")
UNION ALL SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT
INFORMATION].[ACCOUNT NAME],[FIRSTOFREMARKS],[DATE REMARKS
ENTERED],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [REMARKS QUERY]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*");
Can someone help me with what I need to include in both sections of the
union queries to combine the nonblankcomments field? Obviously I need more
code than what I have for thse fields that work great in each individual
query above.
-- Brian
- Next message: Steve: "Re: Count Consecutive Months"
- Previous message: xian: "express"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
