468,727 Members | 1,407 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,727 developers. It's quick & easy.

adding values within query

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
Aug 28 '06 #1
2 2503
ADezii
8,800 Expert 8TB
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
Aug 28 '06 #2
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
Aug 28 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Viorel | last post: by
47 posts views Thread by Pierre Barbier de Reuille | last post: by
1 post views Thread by CARIGAR | last post: by
xarzu
1 post views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.