Hello, Message Board:
I have a table with invoice details. I'm trying to create a query that will summarize the total $ of invoices by month. I created a field that will give me the month end date based off the invoice date field using the dateserial function (ie and invoice with a date of 2/15/2014 will give me a month end date of 2/28/2014).
I created a query that includes the Month End Date, and Invoice_Amt. I grouped the Month End Date and Summed the Invoice Amount. That part works fine. When I try to add a subquery to give me a running total that's where I run into problems
Here's the sql I used: [YTD_INV_AMT: (SELECT SUM([INV_AMT]) FROM [InvoiceDetail] AS I1 WHERE [I1]![INVOICE_DT]<=[MonthEndDate])]. This gives me the sum of the whole dataset.
I'm not sure what it is that I'm missing.
I have a table with invoice details. I'm trying to create a query that will summarize the total $ of invoices by month. I created a field that will give me the month end date based off the invoice date field using the dateserial function (ie and invoice with a date of 2/15/2014 will give me a month end date of 2/28/2014).
I created a query that includes the Month End Date, and Invoice_Amt. I grouped the Month End Date and Summed the Invoice Amount. That part works fine. When I try to add a subquery to give me a running total that's where I run into problems
Here's the sql I used: [YTD_INV_AMT: (SELECT SUM([INV_AMT]) FROM [InvoiceDetail] AS I1 WHERE [I1]![INVOICE_DT]<=[MonthEndDate])]. This gives me the sum of the whole dataset.
I'm not sure what it is that I'm missing.
