Same Field repeated with different values.

[Betra]

Well Hello everyone, I'm not too sure what this is called and therefor no search results are even helping me.



What im trying to do is, pull up 3 tables, and pull generic fields...

So Right now this is where im at

SELECT Clients.ClientName, Contract.ClientCodeName, Contract.ClientCodeYear, Contract.SignedDate, Contract.ContractTotalHours, Contract.TermInMonth, Sum(Invoices.UsedHours) AS SumOfUsedHours, Sum(Invoices.UsedHours) AS u2
FROM (Clients INNER JOIN Contract ON Clients.[ID] = Contract.[ClientID]) INNER JOIN Invoices ON Contract.ID = Invoices.ContractID
GROUP BY Clients.ClientName, Contract.ClientCodeName, Contract.ClientCodeYear, Contract.SignedDate, Contract.ContractTotalHours, Contract.TermInMonth, Contract.Active, Clients.Company
HAVING (((Contract.Active)=True) AND ((Clients.Company)=2))
ORDER BY Clients.ClientName;

Problem:
there are three fields i want to add ([Invoices.UsedHours] based on Invoices.InvDate (this month, last month, and month before). this would give me the used hours for a 3 month period.

Some help would be awesome!

[June7]

The 3 fields you refer to are this month, last month, and month before? These aren't fields, this a range criteria. You want to retrieve records that fall within this range and Sum the UsedHours field.

You can determine the first and last dates of the 3 month period and use that as criteria. A complication is having to consider crossing years.

InvDate Between Month(DateAdd("m",-2,Date())) & "/1/" & Year(DateAdd("m",-2,Date())) And Date()

That will use the current date to determine the 3-month range. Question is, do you want the current month? If it is March 1 and you run report do you want Jan, Feb, Mar or Dec, Jan, Feb? If you really want report to cover through the last full month, complicates the calculation even more because the ending date varies by month.

InvDate Between CDate(Month(DateAdd("m",-3,Date())) & "/1/" & Year(DateAdd("m",-3,Date()))) And CDate(Month(Date()) & "/1/" & Year(Date())) - 1