Query Syntax

[Matt Greatorex]

I'm trying to get my head round this query and not having much luck.

I have table that contains. amongst others, two fields I need to use - Date and Amount. I already have a query that returns me the number of entries for a given month, along with the total value.

SELECT Count(*) AS [Number of Entries], 
           Sum(NBReferral.[Amount]) AS [Total Amount], 
           NBReferral.Date
FROM NBReferral
GROUP BY NBReferral.Date;

What I want is to also provide a running total (year to date). Using this:

SELECT Count(*) AS [YTD Number of Entries],             
           Sum(NBReferral.[Amtount]) AS [YTD Total Amount]
FROM NBReferral;

Combing the two queries works as long as there is only one month. Unfortunately, I'd like to see each month's YTD figure displayed as shown

Date       Number of Entries  Total Amount  YTD Number of Entries YTD Total Amount
Nov 2006        5                   10000               5                  10000
Dec 2006        3                   5000                 8                  15000
Jan 2007         6                   12000               14                 27000
etc.

What I currently get, as you would expect, are the same YTD totals applied to each month:

Date       Number of Entries  Total Amount  YTD Number of Entries YTD Total Amount
Nov 2006        5                   10000               14                  27000
Dec 2006        3                   5000                 14                  27000
Jan 2007         6                   12000               14                 27000
etc.

Probably a fairly straight-forward query for someone with a bit more experience. Any ideas?

 

[FoFa]

What are you joining these two queries on? I would suspect you need to check the date (maybe <=) in your running sum query to the date in the other query?

 

[Matt Greatorex]

Thanks for the reply.

It was originally a one-off query on one month's data at a time, so I didn't actually 'join' the queries. All the person running it wanted was to be able to call up the most recent month's data and also see the YTD figures.

Now, they want to see a list of all months, along with their respective YTD figures. I assumed the link was something to do with the Date field, so I'll have a play with the <= part.

 

[Matt Greatorex]

Okay, as always someone's suggestion nudged me in the right direction.
For the benefit of anyone who may have the same problem, what I ended up doing was as follows.

I used the same query twice, thereby removing the need for the YTD query.

SELECT 
         Format(qry_Month_Totals.Date,"mmmm yyyy") AS Period, 
         qry_Month_Totals.[Number of Leveraged Loans] AS [Month #], 
         "$" & qry_Month_Totals.[Total Amount]/1000 AS [Month Total (000s)],
         Sum( qry_Month_Totals1.[Number of Leveraged Loans]) AS [YTD #], 
         "$" & Sum(qry_Month_Totals1.[Total Amount])/1000 AS [YTD Total (000s)]
FROM 
         qry_Month_Totals RIGHT JOIN Month_Totals as qry_Month_Totals1
ON 
         qry_Month_Totals1.Date <= qry_Month_Totals.Date
GROUP BY 
         Format(qry_Month_Totals.Date,"mmmm yyyy"), 
         qry_Month_Totals.[Number of Leveraged Loans],
         "$" & qry_Month_Totals.[Total Amount]/1000
ORDER BY 
         "$" & qry_Leveraged_loans.[Total Amount]/1000;