 <p>I don't have access handy, but I'll give it a shot "free hand" and see how it goes :)<br> Since Access does not (afaik) have <code>CASE</code>, the trick would seem to be to use <code>IIF</code>;</p> <pre class="lang-sql prettyprint-override"><code>SELECT SUM(Total) as RoomRevenue, SUM(ExtraCharges) AS Extra, SUM(Discount) AS DiscountGiven, SUM(Tax) AS TaxCollection, SUM(GrandTotal) AS TotalCollection, SUM(IIF(ExtraChargesType = 'Tickets', ExtraCharges, 0)) AS TheatreTickets, SUM(IIF([CheckInDate])&lt;=[regDate] AND [CheckOutDate])&gt;=[regDate], 1, 0)) AS OcuppiedRooms regDate FROM tblRegistration GROUP BY regDate; </code></pre>