top 5 days for each user

S

Song

I have a query like this. I want to modify the query to show top (recent) 5
days for EACH user.

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Result is:

User Date Day Edited
elac_iso 8/1/2008 Fri 61
elac_iso 7/31/2008 Thu 129
elac_iso 7/30/2008 Wed 79
elac_iso 7/29/2008 Tue 23
elac_iso 7/28/2008 Mon 9
elac_iso 7/25/2008 Fri 37
elac_iso 7/24/2008 Thu 82
elac_iso 7/23/2008 Wed 100
wongnc 7/31/2008 Thu 4
wongnc 7/30/2008 Wed 8
wongnc 7/24/2008 Thu 1
wongnc 7/23/2008 Wed 5
wongnc 7/22/2008 Tue 4
wongnc 7/21/2008 Mon 1
wongnc 7/17/2008 Thu 2
wongnc 7/16/2008 Wed 1
wongnc 7/12/2008 Sat 1
wongnc 7/5/2008 Sat 1
wongnc 6/24/2008 Tue 1
wongnc 6/18/2008 Wed 1
 
S

Song

Thanks for quick reply. I followed the example, modified my query. However,
the result only 1 record for first user and 2 records from second user
(total 3 records shown). Where did I do wrong?

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
WHERE DateValue([DateEdited]) IN
(SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateEdited DESC)
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Allen Browne said:
See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
I have a query like this. I want to modify the query to show top (recent)
5 days for EACH user.

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Result is:

User Date Day Edited
elac_iso 8/1/2008 Fri 61
elac_iso 7/31/2008 Thu 129
elac_iso 7/30/2008 Wed 79
elac_iso 7/29/2008 Tue 23
elac_iso 7/28/2008 Mon 9
elac_iso 7/25/2008 Fri 37
elac_iso 7/24/2008 Thu 82
elac_iso 7/23/2008 Wed 100
wongnc 7/31/2008 Thu 4
wongnc 7/30/2008 Wed 8
wongnc 7/24/2008 Thu 1
wongnc 7/23/2008 Wed 5
wongnc 7/22/2008 Tue 4
wongnc 7/21/2008 Mon 1
wongnc 7/17/2008 Thu 2
wongnc 7/16/2008 Wed 1
wongnc 7/12/2008 Sat 1
wongnc 7/5/2008 Sat 1
wongnc 6/24/2008 Tue 1
wongnc 6/18/2008 Wed 1
Click to expand...
Click to expand...
 
J

John Spencer

As a guess, the records being returned by the subquery include duplicates.
Something like

11/1/2008
11/1/2008
11/1/2008
11/13/2008
11/13/2008

And then the group by clause is reducing that down to show two records.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks for quick reply. I followed the example, modified my query.
However, the result only 1 record for first user and 2 records from
second user (total 3 records shown). Where did I do wrong?

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
WHERE DateValue([DateEdited]) IN
(SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateEdited DESC)
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Allen Browne said:
See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
I have a query like this. I want to modify the query to show top
(recent) 5 days for EACH user.

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS
[Date], Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS
Edited
FROM tblMaster
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Result is:

User Date Day Edited
elac_iso 8/1/2008 Fri 61
elac_iso 7/31/2008 Thu 129
elac_iso 7/30/2008 Wed 79
elac_iso 7/29/2008 Tue 23
elac_iso 7/28/2008 Mon 9
elac_iso 7/25/2008 Fri 37
elac_iso 7/24/2008 Thu 82
elac_iso 7/23/2008 Wed 100
wongnc 7/31/2008 Thu 4
wongnc 7/30/2008 Wed 8
wongnc 7/24/2008 Thu 1
wongnc 7/23/2008 Wed 5
wongnc 7/22/2008 Tue 4
wongnc 7/21/2008 Mon 1
wongnc 7/17/2008 Thu 2
wongnc 7/16/2008 Wed 1
wongnc 7/12/2008 Sat 1
wongnc 7/5/2008 Sat 1
wongnc 6/24/2008 Tue 1
wongnc 6/18/2008 Wed 1
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
WHERE DateValue([DateEdited]) IN
(SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)
GROUP BY [EditedBy],
DateValue([DateEdited]),
ORDER BY [EditedBy],
DateValue([DateEdited]) DESC;

JET is not date sensitive, so LCase() won't make a difference.

Presumably DateEdited is a Date/Time field, and you are using DateValue() to
lose the time component. If so, placing the DateValue() in the ORDER BY of
the subquery may make a difference.

I've also tried to remove fields whose names are reserved words (such as
Date and User), as these can confuse Access.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
Thanks for quick reply. I followed the example, modified my query.
However, the result only 1 record for first user and 2 records from second
user (total 3 records shown). Where did I do wrong?

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
WHERE DateValue([DateEdited]) IN
(SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateEdited DESC)
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Allen Browne said:
See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
I have a query like this. I want to modify the query to show top (recent)
5 days for EACH user.

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Result is:

User Date Day Edited
elac_iso 8/1/2008 Fri 61
elac_iso 7/31/2008 Thu 129
elac_iso 7/30/2008 Wed 79
elac_iso 7/29/2008 Tue 23
elac_iso 7/28/2008 Mon 9
elac_iso 7/25/2008 Fri 37
elac_iso 7/24/2008 Thu 82
elac_iso 7/23/2008 Wed 100
wongnc 7/31/2008 Thu 4
wongnc 7/30/2008 Wed 8
wongnc 7/24/2008 Thu 1
wongnc 7/23/2008 Wed 5
wongnc 7/22/2008 Tue 4
wongnc 7/21/2008 Mon 1
wongnc 7/17/2008 Thu 2
wongnc 7/16/2008 Wed 1
wongnc 7/12/2008 Sat 1
wongnc 7/5/2008 Sat 1
wongnc 6/24/2008 Tue 1
wongnc 6/18/2008 Wed 1
Click to expand...
Click to expand...
Click to expand...
 
S

Song

Yes, DateEdited is date/time so I use DateValue to extract date only. I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

Allen Browne said:
Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
WHERE DateValue([DateEdited]) IN
(SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)
GROUP BY [EditedBy],
DateValue([DateEdited]),
ORDER BY [EditedBy],
DateValue([DateEdited]) DESC;

JET is not date sensitive, so LCase() won't make a difference.

Presumably DateEdited is a Date/Time field, and you are using DateValue()
to lose the time component. If so, placing the DateValue() in the ORDER BY
of the subquery may make a difference.

I've also tried to remove fields whose names are reserved words (such as
Date and User), as these can confuse Access.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
Thanks for quick reply. I followed the example, modified my query.
However, the result only 1 record for first user and 2 records from
second user (total 3 records shown). Where did I do wrong?

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
WHERE DateValue([DateEdited]) IN
(SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateEdited DESC)
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Allen Browne said:
See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Song" <songsu at yahoo dot com> wrote in message
I have a query like this. I want to modify the query to show top
(recent) 5 days for EACH user.

SELECT LCase([EditedBy]) AS [User], DateValue([DateEdited]) AS [Date],
Format([dateedited],"ddd") AS [Day], Count(tblMaster.ID) AS Edited
FROM tblMaster
GROUP BY LCase([EditedBy]), DateValue([DateEdited]),
Format([dateedited],"ddd")
ORDER BY LCase([EditedBy]), DateValue([DateEdited]) DESC;

Result is:

User Date Day Edited
elac_iso 8/1/2008 Fri 61
elac_iso 7/31/2008 Thu 129
elac_iso 7/30/2008 Wed 79
elac_iso 7/29/2008 Tue 23
elac_iso 7/28/2008 Mon 9
elac_iso 7/25/2008 Fri 37
elac_iso 7/24/2008 Thu 82
elac_iso 7/23/2008 Wed 100
wongnc 7/31/2008 Thu 4
wongnc 7/30/2008 Wed 8
wongnc 7/24/2008 Thu 1
wongnc 7/23/2008 Wed 5
wongnc 7/22/2008 Tue 4
wongnc 7/21/2008 Mon 1
wongnc 7/17/2008 Thu 2
wongnc 7/16/2008 Wed 1
wongnc 7/12/2008 Sat 1
wongnc 7/5/2008 Sat 1
wongnc 6/24/2008 Tue 1
wongnc 6/18/2008 Wed 1
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Sorry: lose the comma after TheDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
Yes, DateEdited is date/time so I use DateValue to extract date only. I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

Allen Browne said:
Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
Click to expand...
Click to expand...
 
S

Song

I modified accordingly as follows and it only shows 3 records. See my first
post, I have a lot more records for each user.

SELECT tblMaster.[EditedBy], DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE (((DateValue([DateEdited])) In (SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)))
GROUP BY tblMaster.[EditedBy], DateValue([DateEdited])
ORDER BY tblMaster.[EditedBy], DateValue([DateEdited]) DESC;


result:

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Allen Browne said:
Sorry: lose the comma after TheDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
Yes, DateEdited is date/time so I use DateValue to extract date only. I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

Allen Browne said:
Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Have you nulls for some DateEdited values? if so, try:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
GROUP BY tblMaster.[EditedBy], DateValue(DateEdited)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;




Vanderghast, Access MVP


Song said:
I modified accordingly as follows and it only shows 3 records. See my first
post, I have a lot more records for each user.

SELECT tblMaster.[EditedBy], DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE (((DateValue([DateEdited])) In (SELECT TOP 5 DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)))
GROUP BY tblMaster.[EditedBy], DateValue([DateEdited])
ORDER BY tblMaster.[EditedBy], DateValue([DateEdited]) DESC;


result:

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Allen Browne said:
Sorry: lose the comma after TheDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Song said:
Yes, DateEdited is date/time so I use DateValue to extract date only. I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name that
is misspelled or missing, or the punctuation is incorrect.

Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
Click to expand...
Click to expand...
Click to expand...
 
S

Song

I copied your code, same result, only 3 records. Let me attach rawdata.txt.
I have 2 users. I want to see for the past 5 days which users edited how
many records.

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Michel Walsh said:
Have you nulls for some DateEdited values? if so, try:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
GROUP BY tblMaster.[EditedBy], DateValue(DateEdited)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;




Vanderghast, Access MVP


Song said:
I modified accordingly as follows and it only shows 3 records. See my
first post, I have a lot more records for each user.

SELECT tblMaster.[EditedBy], DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE (((DateValue([DateEdited])) In (SELECT TOP 5
DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)))
GROUP BY tblMaster.[EditedBy], DateValue([DateEdited])
ORDER BY tblMaster.[EditedBy], DateValue([DateEdited]) DESC;


result:

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Allen Browne said:
Sorry: lose the comma after TheDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Song" <songsu at yahoo dot com> wrote in message
Yes, DateEdited is date/time so I use DateValue to extract date only. I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name that
is misspelled or missing, or the punctuation is incorrect.

Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Remove the GROUP BY and the COUNT:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;







To get back the 'COUNT', use a sub-query:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
( SELECT COUNT(*) FROM tblMaster AS a WHERE a.EditedBy =
tblMaster.EditedBy )
FROM ...




Vanderghast, Access MVP


Song said:
I copied your code, same result, only 3 records. Let me attach rawdata.txt.
I have 2 users. I want to see for the past 5 days which users edited how
many records.

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Michel Walsh said:
Have you nulls for some DateEdited values? if so, try:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
GROUP BY tblMaster.[EditedBy], DateValue(DateEdited)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;




Vanderghast, Access MVP


Song said:
I modified accordingly as follows and it only shows 3 records. See my
first post, I have a lot more records for each user.

SELECT tblMaster.[EditedBy], DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE (((DateValue([DateEdited])) In (SELECT TOP 5
DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)))
GROUP BY tblMaster.[EditedBy], DateValue([DateEdited])
ORDER BY tblMaster.[EditedBy], DateValue([DateEdited]) DESC;


result:

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Sorry: lose the comma after TheDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Song" <songsu at yahoo dot com> wrote in message
Yes, DateEdited is date/time so I use DateValue to extract date only.
I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name that
is misspelled or missing, or the punctuation is incorrect.

Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
Click to expand...
Click to expand...
Click to expand...
 
S

Song

I'd like to try your method. I don't know how to "To get back the 'COUNT',
use a sub-query" and put it all together. Please advice

Michel Walsh said:
Remove the GROUP BY and the COUNT:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;







To get back the 'COUNT', use a sub-query:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
( SELECT COUNT(*) FROM tblMaster AS a WHERE a.EditedBy =
tblMaster.EditedBy )
FROM ...




Vanderghast, Access MVP


Song said:
I copied your code, same result, only 3 records. Let me attach
rawdata.txt.
I have 2 users. I want to see for the past 5 days which users edited how
many records.

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Michel Walsh said:
Have you nulls for some DateEdited values? if so, try:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
GROUP BY tblMaster.[EditedBy], DateValue(DateEdited)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;




Vanderghast, Access MVP


"Song" <songsu at yahoo dot com> wrote in message
I modified accordingly as follows and it only shows 3 records. See my
first post, I have a lot more records for each user.

SELECT tblMaster.[EditedBy], DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE (((DateValue([DateEdited])) In (SELECT TOP 5
DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)))
GROUP BY tblMaster.[EditedBy], DateValue([DateEdited])
ORDER BY tblMaster.[EditedBy], DateValue([DateEdited]) DESC;


result:

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Sorry: lose the comma after TheDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Song" <songsu at yahoo dot com> wrote in message
Yes, DateEdited is date/time so I use DateValue to extract date only.
I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name
that
is misspelled or missing, or the punctuation is incorrect.

Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
Click to expand...
Click to expand...
Click to expand...
 
S

Song

Ok, since I could not figure out how to put subquery with main query, I gave
up. With all the hints I got, I come up with something that dirty and quick
single query. Even though I could not get 'top 5' as planed, I just adjust
number of 'tops' based on how many users. Here is my code. Here I must thank
all of you trying to help me.

SELECT TOP 15 First(DateValue([DateEdited])) AS Dates,
Format([DateEdited],"ddd") AS Days, LCase([EditedBy]) AS Users,
Count(tblMaster.ID) AS [Records Edited]
FROM tblMaster
GROUP BY Format([DateEdited],"ddd"), LCase([EditedBy]),
DateValue([dateedited]) & LCase([EditedBy])
ORDER BY DateValue([dateedited]) & LCase([EditedBy]) DESC ,
LCase([EditedBy]);

Michel Walsh said:
Remove the GROUP BY and the COUNT:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;







To get back the 'COUNT', use a sub-query:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
( SELECT COUNT(*) FROM tblMaster AS a WHERE a.EditedBy =
tblMaster.EditedBy )
FROM ...




Vanderghast, Access MVP


Song said:
I copied your code, same result, only 3 records. Let me attach
rawdata.txt.
I have 2 users. I want to see for the past 5 days which users edited how
many records.

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Michel Walsh said:
Have you nulls for some DateEdited values? if so, try:


SELECT tblMaster.[EditedBy],
DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE DateValue(DateEdited) IN
(
SELECT TOP 5 DateValue(Dup.DateEdited)
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
AND ( NOT Dup.DateEdited IS NULL )
ORDER BY DateValue(Dup.DateEdited) DESC
)
GROUP BY tblMaster.[EditedBy], DateValue(DateEdited)
ORDER BY tblMaster.[EditedBy], DateValue(DateEdited) DESC;




Vanderghast, Access MVP


"Song" <songsu at yahoo dot com> wrote in message
I modified accordingly as follows and it only shows 3 records. See my
first post, I have a lot more records for each user.

SELECT tblMaster.[EditedBy], DateValue([DateEdited]) AS TheDate,
Count(tblMaster.ID) AS RecEdited
FROM tblMaster
WHERE (((DateValue([DateEdited])) In (SELECT TOP 5
DateValue([DateEdited])
FROM tblMaster as Dup
WHERE Dup.EditedBy=tblMaster.EditedBy
ORDER BY DateValue([DateEdited]) DESC)))
GROUP BY tblMaster.[EditedBy], DateValue([DateEdited])
ORDER BY tblMaster.[EditedBy], DateValue([DateEdited]) DESC;


result:

EditedBy TheDate RecEdited
elac_iso 8/1/2008 61
wongnc 7/31/2008 4
wongnc 7/30/2008 8

Sorry: lose the comma after TheDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Song" <songsu at yahoo dot com> wrote in message
Yes, DateEdited is date/time so I use DateValue to extract date only.
I
tried (copied) your code. It gave me following error message and it
highlighted the first FROM:

The SELECT statement includes a reserved word or an argument name
that
is misspelled or missing, or the punctuation is incorrect.

Try this simplified version until you get it working.:

SELECT [EditedBy],
DateValue([DateEdited]) AS TheDate,
FROM tblMaster
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query for attendance 1
query to sort data 3
show top values 5
First value below zero in a column 1
The Tueday prior to a holiday... 8
Sorting day names chronologically 5
Another Date ? Continued 1
Fiscal year 2

Top