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 "CountOfAttende d" results in a number that needs to have the "Programs.w alk-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 (CountOfAttende d is highlighted):

SELECT Programs.Name, Programs.[Date Start], Programs.[Date End], Count(Programs_ _Registered_Par ticipants.Atten ded) AS CountOfAttended, MonthName(DateP art("m",[Date End])) AS ProgMonth, DatePart("yyyy" ,[Date End]) AS ProgYear
FROM Programs INNER JOIN (Participants INNER JOIN Programs__Regis tered_Participa nts ON Participants.[Participant ID] = Programs__Regis tered_Participa nts.Participant _ID) ON Programs.[Program ID] = Programs__Regis tered_Participa nts.Program_ID
WHERE (((Programs__Re gistered_Partic ipants.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__Ticke red_Attendance_ ONLY.Name, Programs__Ticke red_Attendance_ ONLY.Date, Programs__Ticke red_Attendance_ ONLY.Date, Programs__Ticke red_Attendance_ ONLY.Attendees, MonthName(DateP art("m",[Date])) AS ProgMonth, DatePart("yyyy" ,[Date]) AS ProgYear
FROM Programs__Ticke red_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 "CountOfAttende d" results in a number that needs to have the "Programs.w alk-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 (CountOfAttende d is highlighted):

SELECT Programs.Name, Programs.[Date Start], Programs.[Date End], Count(Programs_ _Registered_Par ticipants.Atten ded) AS CountOfAttended, MonthName(DateP art("m",[Date End])) AS ProgMonth, DatePart("yyyy" ,[Date End]) AS ProgYear
FROM Programs INNER JOIN (Participants INNER JOIN Programs__Regis tered_Participa nts ON Participants.[Participant ID] = Programs__Regis tered_Participa nts.Participant _ID) ON Programs.[Program ID] = Programs__Regis tered_Participa nts.Program_ID
WHERE (((Programs__Re gistered_Partic ipants.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__Ticke red_Attendance_ ONLY.Name, Programs__Ticke red_Attendance_ ONLY.Date, Programs__Ticke red_Attendance_ ONLY.Date, Programs__Ticke red_Attendance_ ONLY.Attendees, MonthName(DateP art("m",[Date])) AS ProgMonth, DatePart("yyyy" ,[Date]) AS ProgYear
FROM Programs__Ticke red_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__Regi stered_Particip ants.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__Regi stered_Particip ants.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(Progr ams__Registered _Participants.A ttended), Programs.walk-ins) AS CountOfAttended

In place of this:

Count(Programs_ _Registered_Par ticipants.Atten ded) 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