Query help needed

[mclovin]

Hey guys,

I'm a complete n00b when it comes to queries and SQL, so please forgive my ignorance. I have trawled Google for anything that might help, but alas.. I'm making no progress!

I'm amending an existing query on a customers event management database, which basically pulls a list of all the establishments from tblEstablishment, then counts InvoiceID in tblEventDelegate to calculate the number of times a particular establishment has used services from the company.

What I'd like to do is also only show establishments that have used services in the last 2 years from when the query is run. The field 'TimeStamp' (in format: DD/MM/YYY HH:MM:SS) in TblEventDelegate could be used to perform this calculation, or there are also fields for invoice date etc.



Here is the current query sql:

SELECT TOP 5000 tblEstablishment.EstablishmentName, tblEstablishment.Address1, tblEstablishment.Address2, tblEstablishment.Address3, tblEstablishment.PostTown, tblEstablishment.County, tblEstablishment.PostalCode, Count(tblEventDelegate.InvoiceID) AS CountOfInvoiceID
FROM tblEstablishment RIGHT JOIN tblEventDelegate ON tblEstablishment.EstablishmentID = tblEventDelegate.EstablishmentID
GROUP BY tblEstablishment.EstablishmentName, tblEstablishment.Address1, tblEstablishment.Address2, tblEstablishment.Address3, tblEstablishment.PostTown, tblEstablishment.County, tblEstablishment.PostalCode
ORDER BY Count(tblEventDelegate.InvoiceID) DESC;

What would be the best way of me filtering the resulting data? To be honest even if I was able to pull the 'TimeStamp' field into the data that would be useful.

Any help appreciated

[jzwp11]

Welcome to the forum!

I would create a query that does the date filtering first, and then use that in a new query that does the counting.

The criteria for the date filtering would look something like this

WHERE invoicedate BETWEEN dateadd("yyyy",-2,date()) AND date()

[Robeen]

You might consider using 'DateSerial' in your SQL. Google 'DateSerial' - Microsoft has a good description of how to use it.
Here's a snippet from a query in which I have used 'DateSerial'.

Here, I want all data from the month that is 13 months from the current month.
For example, in April 2012, I want data from March 2011.

WHERE (((TableName.DateField) Between DateSerial(Year(Date()),Month(Date())-13,1) And DateSerial(Year(Date()),Month(Date())-12,0)))

I'm not sure if there is any justificatoin for this, but I am uneasy about you using 'TimeStamp' as the name of a field.
It may NOT be an issue, but sometimes using words like 'Name', 'Date', 'Time' . . . causes problems because those words are 'reserved words' and might be interpreted by Access [or VB, or other programming languages] as an instruction to do something rather than just as a field name.

I hope this helps!

[mclovin]

Excellent! Thank you both for your replies. I ended up doing as jz suggested, and created a separate query to filter by date first.

Thanks again

[jzwp11]

You're welcome; good luck with your project.