Finding the last date

[guitardave78]

Hi guys, i have a qurey that is counting the number of logins and is trying to show the most recent dat.
I use count on the hits and i use last on the date and it shows me totally the wrong date, max doesnt work either.
Any ideas?

 

[PHV]

Any chance you could post your table structure, some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[guitardave78]

tbl_user
tbl_date
tbl_page

example data
tbl_user tbl_date tbl_page
stu123 01/03/05 10:23:11 1
stu123 02/03/05 10:11:12 2
stu123 03/03/05 12:16:05 1
stu123 02/03/05 10:15:12 3
stu123 05/03/05 03:12:10 1

expected output

qry_stu qry_last_date qry_hit_count
stu123 005/03/05 03:12:10 5

 

[traingamer]

if this is one table:

Select tbl_user, max(tbl_date), count(tbl_user)
from tbl
group by tbl_user;

If more than one table, please post significant parts of structures and links.




traingamer

 

[PHV]

SELECT tbl_user AS qry_stu, Max(tbl_date) AS qry_last_date, Count(*) AS qry_hit_count
FROM yourTableName
GROUP BY tbl_user

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[guitardave78]

Cheers guys turns out it was cause i was linking 2 tables.

 

[sprintwater]

Sorry and excuse as I am new to Access.

I really need to solve this problem as well, so I will start my stupid question.

As the guitardave78, I need a outcome of the last day and according to this last day, it will append this record to another table (only the record with the last day). How am I doing this?

I tried to put max(tbl_date) in the criteria, but my computer automatically change it to max("tbl_date") and said Cannot have aggregate function in WHERE clause ([table name.field name]=max("tbl_date"). Please help me.

 

[PHV]

Something like this ?
INSERT INTO AnotherTable (SomeFieldList)
SELECT CompatibleFieldList
FROM yourTable
WHERE tbl_date = (SELECT Max(tbl_date) FROM yourTable);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sprintwater]

Thank you PHV

Here is My Data

modified date
costomer ID
equipment

example data in query from Table CS_rel

Modified Date Constomer ID Equipment
11/01/1998 18243 Screws
10/09/1997 19828 Hammer
01/02/2004 76384 Pressing Die
12/15/2001 57685 housing die
10/23/1999 89834 Band Die

Expected outcome to Table History
Modifid Date Customer ID Equipment
Append record: 01/02/2004 76384 Pressing Die
To Table named as History

Here is what I put in my criteria, but the outcome doesn't do as I want, please help

Where modified date = (SELECT max(modified date) FROM CS_rel)

Please help me, thank you

 

[PHV]

The generated SQL should be something like this:
INSERT INTO History ([Modifid Date], [Customer ID], Equipment)
SELECT [Modified Date], [Customer ID], Equipment
FROM CS_rel
WHERE [Modified Date] = (SELECT Max([Modified Date]) FROM CS_rel);

Note: pay attention to the brackets enclosing field names with embedded space ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[guitardave78]

is the info a query coming from 2 tables? if it is try splitting the query into 2 queries, one get the last date one gets the rest plus the new last date

also use last instead of max