Query Count Question

[zgray]

I have this query. It counts how many records a person has based on a date.

SELECT queryName.UserName, Count(*) AS [Count]
FROM queryName
WHERE (((queryName.Date)=Date()))
GROUP BY queryName.UserName;

It spits out the persons name and how many records for them for the current day. Simple Enough.

I can remake the same query but instead of the WHERE being the current date it looks at the entire month. Which is just a small change.

What I want (if possible) is to put these both in one query. So the first Column is the persons Name, the second is a count of records that user has in the current month and the third column is a count of records that person has on the current day.

Iv tried a few things and (if it doesn't error out) I will get either no data or everything. Some assistance would be greatly appreciated.

 

[BlueIshDan]

I'm a little iffy with SQL, haven't had much pracatice lately. But have a subquery and match the user_ids, and display them as joined records.

SELECT queryName.UserName, Count(*) AS [TodayCount], MonthlyQuery.MonthlyCount
FROM queryName, (
  SELECT queryName.UserName, Count(*) As [MonthlyCount]
  FROM queryName
  WHERE (MONTHLY CONDITION)
  GROUP BY queryName.UserName;
) As MonthlyQuery
WHERE (((queryName.Date)=Date()))
GROUP BY queryName.UserName;

Would this work?

 

[plog]

First, 'Count' and 'Date' are poor choices field names because they are reserved words. Change them to something more descriptive (e.g. 'DailyCount', 'SalesDate')

Then for your issue, you move the criteria from the WHERE clause to the SELECT clause by using an Iif statement inside a SUM function:

SELECT UserName, SUM(Iif(SalesDate = Date(), 1, 0)) AS DailyCount
FROM queryName
GROUP BY UserName;

Using the same method, create a new field for every other total you want to see.

 

[BlueIshDan]

SELECT UserName, SUM(Iif(SalesDate = Date(), 1, 0)) AS DailyCount
FROM queryName
GROUP BY UserName;

Would this produce the required results of both count in this month and count in this current day.

 

[BlueIshDan]

OH I SEE what you're saying.

SELECT UserName, SUM(Iif(SalesDate = Date(), 1, 0)) AS DailyCount, SUM(MONTHLY CONDITION) AS MonthlyCount
FROM queryName
GROUP BY UserName;

Awesome lol

 

[plog]

That's right Dan, from there he could add as many calculated fields as he wanted with condtions like the first one I posted (Weekly, Monthly, Yesterday etc.)

 

[zgray]

OH I SEE what you're saying.

SELECT UserName, SUM(Iif(SalesDate = Date(), 1, 0)) AS DailyCount, SUM(MONTHLY CONDITION) AS MonthlyCount
FROM queryName
GROUP BY UserName;

Awesome lol

This looked like it worked. Thanks for all your guys help.