Query to Filter Last Month or Current Month Totals - DateDiff or Dlookup expression

[Jgk]

SELECT [Billing Hours All].Project, Sum(CCur([BillableHours])*([InvoiceRate])) AS [Total Invoiced], Sum(CCur([BillableHours])*([BillingRate])) AS [Total Contractor Pay], Sum([Billing Hours All].BillableHours) AS [Total Hours Billed], Sum(CCur([BillableHours])*([BillingRate])) AS [Current Contractor Pay], Sum(CCur([BillableHours])*([InvoiceRate])) AS [Current Month Invoice], Sum([Billing Hours All].BILLABLEHOURS) AS [Current Month Hours]
FROM [Billing Hours All]-
GROUP BY [Billing Hours All].Project;

The bold part of sql is what I need help with want it to filter data in table for last month data for each category- Contractor Pay, Month Invoice, Current Month Hours by Project

I have the first part for Totals working- Total Invoiced, Total Contractor Pay, Total Hours Billed- by Project

Thanks,

[June7]

Apply filter criteria to the query.

WHERE Format([date field],"yyyymm") = Format(DateAdd("m",-1,Date()),"yyyymm")

[Jgk]

WHERE Format([date worked],"yyyymm") = Format(DateAdd("m",-1,Date()),"yyyymm") I changed date field to my field name which is date worked and could not get it to filter data gave total billing for all projects. What am I doing wrong, Thanks for your help

[burrina]

Is your qry a Select qry, a Crosstab, are you using Totals?

[June7]

Don't know why it is doing that. You have the query grouping by project then it should show totals for each project, limited to the period of the WHERE clause.

Post the entire revised SQL statement.

[Jgk]

SELECT Projects.*, [Project Hours].ID, [Project Hours].*, [Contractors Extended].[Contractor Name], [Contractors Extended].BillingRate, nz([BillableHours])*nz([BillingRate]) AS Amount, Customers.InvoiceRate, CCur([BillableHours])*nz([InvoiceRate]) AS Invoice
FROM (Customers RIGHT JOIN Projects ON Customers.ID = Projects.Customer) INNER JOIN ([Project Hours] INNER JOIN [Contractors Extended] ON [Project Hours].Contractor = [Contractors Extended].ID) ON Projects.ID = [Project Hours].Project
ORDER BY [Project Hours].ID;

This is query I edited :

SELECT [Billing Hours All].Project, Sum(CCur([BillableHours])*([InvoiceRate])) AS [Total Invoiced], Sum(CCur([BillableHours])*([BillingRate])) AS [Total Contractor Pay], Sum([Billing Hours All].BillableHours) AS [Total Hours Billed], Sum(CCur([BillableHours])*([BillingRate])) AS [Current Contractor Pay], Sum(CCur([BillableHours])*([InvoiceRate])) AS [Current Month Invoice], Sum([Billing Hours All].BILLABLEHOURS) AS [Current Month Hours]
FROM [Billing Hours All]
WHERE Format([date worked],"yyyymm") = Format(DateAdd("m",-1,Date()),"yyyymm")
GROUP BY [Billing Hours All].Project;

Project	Total Contractor Pay	Total Invoiced	Total Hours Billed	Current Contractor Pay	Current Month Invoice	Current Month Hours
1009	$1,194.50	$0.00	29.25	$1,194.50	$0.00	29.25
1014	$5,940.00	$0.00	148.5	$5,940.00	$0.00	148.5
1250	$972.00	$3,225.00	43	$972.00	$3,225.00	43
1254	$120.00	$195.00	3	$120.00	$195.00	3
1256	$140.00	$227.50	3.5	$140.00	$227.50	3.5

In order for query to give results, I enter April 2014 and in my test company get this, which is totals. Thanks

[June7]

What do you mean by 'enter April 2014' - enter where?


If you want to filter to a single month then the query can only show data summarized for that month. If Total Invoiced and Total Hours Billed are supposed to be for more than the current month (YTD?), can't do that all in the same GROUP BY.

Perhaps you should build a report and do summary calcs in report and group header/footer sections.