By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,641 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Quarterly attendance query help

P: 3
Hi,

I have a table where I record attendance. I created a query to show how many times someone has attended in each quarter. I am wanting to be able to specify which year as atm it is showing all years. The SQL is below. From this I have a report where it displays the data and have conditional formatted it so that if they have not attended more than 4 times in one quarter the "cell" turns red - the only problem is is that some of the cells have no data in it. How can I get the query to display 0 if there is no data?

QUERY SQL:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Abs([ATTENDANCE 2011].DRILL)) AS SumOfDRILL
  2. SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
  3. FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID = [ATTENDANCE 2011].ATTENDED.Value
  4. GROUP BY [MEMBERS LIST].[NUMBER/NAME]
  5. PIVOT "Qtr " & Format([DATE],"yyyy/q");
Cheers

Dan
Jan 15 '14 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,597
Modify the TRANSFORM Section of the SQL Statement to generate Zeros (0) where no Data exists, as in:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM IIf(Sum(Abs([ATTENDANCE 2011].DRILL)),Sum(Abs([ATTENDANCE 2011].DRILL)),0) AS SumOfDRILL
  2. SELECT [MEMBERS LIST].[NUMBER/NAME], Sum(Abs(Nz([ATTENDANCE 2011].DRILL,0))) AS [Total Of DRILL]
  3. FROM [MEMBERS LIST] INNER JOIN [ATTENDANCE 2011] ON [MEMBERS LIST].ID=[ATTENDANCE 2011].ATTENDED
  4. GROUP BY [MEMBERS LIST].[NUMBER/NAME]
  5. PIVOT "Qtr " & Format([DATE],"yyyy/q");
  6.  
Jan 15 '14 #2

Post your reply

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