adding values within query

[sluster]

I have a Union Query that is tied directly to a report and one field needs to be modified. The "CountOfAttended" results in a number that needs to have the "Programs.walk-ins" field added to it. This seems like it ought to be a simple thing but every work around I've tried has brought up a variety of errors. The SQL code looks like this (CountOfAttended is highlighted):

SELECT Programs.Name, Programs.[Date Start], Programs.[Date End], Count(Programs__Registered_Participants.Attended) AS CountOfAttended, MonthName(DatePart("m",[Date End])) AS ProgMonth, DatePart("yyyy",[Date End]) AS ProgYear
FROM Programs INNER JOIN (Participants INNER JOIN Programs__Registered_Participants ON Participants.[Participant ID] = Programs__Registered_Participants.Participant_ID) ON Programs.[Program ID] = Programs__Registered_Participants.Program_ID
WHERE (((Programs__Registered_Participants.Attended)=Yes ))
GROUP BY Programs.Name, Programs.[Date Start], Programs.[Date End], DatePart("m",[Date End]), DatePart("yyyy",[Date End])
ORDER BY Programs.Name
UNION SELECT Programs__Tickered_Attendance_ONLY.Name, Programs__Tickered_Attendance_ONLY.Date, Programs__Tickered_Attendance_ONLY.Date, Programs__Tickered_Attendance_ONLY.Attendees, MonthName(DatePart("m",[Date])) AS ProgMonth, DatePart("yyyy",[Date]) AS ProgYear
FROM Programs__Tickered_Attendance_ONLY;

I'm new to SQL of any kind and was handed this somewhat large Access project when a staff member left so I'm taking something over in midstream and a bit lost on how to deal with this problem. Thanks again for any suggestions

[ADezii]

I have a Union Query that is tied directly to a report and one field needs to be modified. The "CountOfAttended" results in a number that needs to have the "Programs.walk-ins" field added to it. This seems like it ought to be a simple thing but every work around I've tried has brought up a variety of errors. The SQL code looks like this (CountOfAttended is highlighted):

SELECT Programs.Name, Programs.[Date Start], Programs.[Date End], Count(Programs__Registered_Participants.Attended) AS CountOfAttended, MonthName(DatePart("m",[Date End])) AS ProgMonth, DatePart("yyyy",[Date End]) AS ProgYear
FROM Programs INNER JOIN (Participants INNER JOIN Programs__Registered_Participants ON Participants.[Participant ID] = Programs__Registered_Participants.Participant_ID) ON Programs.[Program ID] = Programs__Registered_Participants.Program_ID
WHERE (((Programs__Registered_Participants.Attended)=Yes ))
GROUP BY Programs.Name, Programs.[Date Start], Programs.[Date End], DatePart("m",[Date End]), DatePart("yyyy",[Date End])
ORDER BY Programs.Name
UNION SELECT Programs__Tickered_Attendance_ONLY.Name, Programs__Tickered_Attendance_ONLY.Date, Programs__Tickered_Attendance_ONLY.Date, Programs__Tickered_Attendance_ONLY.Attendees, MonthName(DatePart("m",[Date])) AS ProgMonth, DatePart("yyyy",[Date]) AS ProgYear
FROM Programs__Tickered_Attendance_ONLY;

I'm new to SQL of any kind and was handed this somewhat large Access project when a staff member left so I'm taking something over in midstream and a bit lost on how to deal with this problem. Thanks again for any suggestions

I think that your Summary Statistic (Programs__Registered_Participants.Attended) AS CountOfAttended may have to be referenced in the initial GROUP BY Clause. Hope this helps.
ADezii

[sluster]

I think that your Summary Statistic (Programs__Registered_Participants.Attended) AS CountOfAttended may have to be referenced in the initial GROUP BY Clause. Hope this helps.
ADezii

I explained poorly, the query I posted was working properly, the problem comes in when I try to perform a SUM. For example, it seems to me that this:

Sum(Count(Programs__Registered_Participants.Attend ed), Programs.walk-ins) AS CountOfAttended

In place of this:

Count(Programs__Registered_Participants.Attended) AS CountOfAttended

but when I do that it tells me that subqueries can't be used in this fashion. So that's what I'm trying to figure out, how to add one field to another within this query.

thanks again