Same query, multiple fields with results depending on different criteria

[Spurious]

Hi all,

I got a database with Date, Message and Author.
I want a query to give me the number of messages the author has sent in 1 day, 1 week and 1 month.
Basically 3 output fields:
1. count where Date >Date(1 day ago)
2. count where Date >Date(1 week ago)
3. count where Date >Date(1 month ago)

Is it possible to express this in a single query?

 

[Joe4]

Sure. Use IF statements to see if the Message Date field falls in your Date window (returning a one if True and a zero if False), and then make it an Aggregate Query to sum up those counts, i.e.

SELECT 
    MessageTable.Author, 
    Sum(IIf([MessageTable]![MessageDate] Between (Date()-1) And Date(),1,0)) AS 1DayAgo, 
    Sum(IIf([MessageTable]![MessageDate] Between (Date()-7) And Date(),1,0)) AS 1WeekAgo, 
    Sum(IIf([MessageTable]![MessageDate] Between (Date()-30) And Date(),1,0)) AS 1MonthAgo
FROM 
    MessageTable
GROUP BY 
    MessageTable.Author;

 

[Spurious]

Thank you! Works perfectly.

Do you know by any chance, if this works in a MySQL database as well or if I have to find different ways there?

 

[Joe4]

I have worked with Access and SQL, but not MySQL. The concepts are all pretty much the same, though sometimes the syntax is slightly different. For example, DATE() in Access returns the current date, but SQL uses GETDATE(), which also returns a time component.

But Aggregate and Nested Queries do exist in all three, so the structure should be the same.

 

[Spurious]

Thanks, I will try to figure it out, shouldnt be too complicated - I hope.

 

[Norie]

One thing is that MySQL doesn't have IIf, the equivalent of that is CASE.