maximus35
asked on
Sql Query to get Closest Date to Today's Date
Hello People.
Situation:
2 access tables: Tasks, and PCs
Tasks stores info about work orders, PCs stores info about user's computer or computers.
Tasks table has, for example:
TaskID, USERID, TaskDate, TaskType
PCs table has, for example:
PC-ID, PCName, PC-IP, PC-BIOS, PC-HDSize, PC-RAM, PC-SerialNum, PCAuditDate, and of course, USERID
Since many users may change computer due to replacement, malfunction, or even Departmental Changes(person only, not PC), PCs names are repeated or even have a different name in the table, hence, multiple records of the same PC with the same or different specs (PC-RAM, PC-HD, USERID), but the only diffrence that it presents is the PCAuditDate.
So here is what I'm trying to get; an SQL Query that checks the PCs table, and gets the PCName with the newest PCAuditDate or the one Closest to Today's Date.
This is what I have only:
"SELECT USER.EMAILADDR, USER.PHONE, USER.DEPT, " _
& " PC.PCNAME, PC.TCPIP " _
& " FROM USER, PCs WHERE [USER.USERID] = " & Me.USERID & _
" AND PCs.USERID = " & Me.USERID
this works ok, but displays the incorrect PCName.
In advance, thank you for helping.
Situation:
2 access tables: Tasks, and PCs
Tasks stores info about work orders, PCs stores info about user's computer or computers.
Tasks table has, for example:
TaskID, USERID, TaskDate, TaskType
PCs table has, for example:
PC-ID, PCName, PC-IP, PC-BIOS, PC-HDSize, PC-RAM, PC-SerialNum, PCAuditDate, and of course, USERID
Since many users may change computer due to replacement, malfunction, or even Departmental Changes(person only, not PC), PCs names are repeated or even have a different name in the table, hence, multiple records of the same PC with the same or different specs (PC-RAM, PC-HD, USERID), but the only diffrence that it presents is the PCAuditDate.
So here is what I'm trying to get; an SQL Query that checks the PCs table, and gets the PCName with the newest PCAuditDate or the one Closest to Today's Date.
This is what I have only:
"SELECT USER.EMAILADDR, USER.PHONE, USER.DEPT, " _
& " PC.PCNAME, PC.TCPIP " _
& " FROM USER, PCs WHERE [USER.USERID] = " & Me.USERID & _
" AND PCs.USERID = " & Me.USERID
this works ok, but displays the incorrect PCName.
In advance, thank you for helping.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
You only want on result...
select top 1 user.emailaddr, user.phone, user.dept, pc.pcname, pc.tcpip
from pc left join user on pc.userid = user.userid
where pc.userid = "& Me.userid &_ "
oder by pcauditdate desc
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Interesting choice for the accepted answer, as that is the only one that did not return only 1 record, if there are more, which is what you asked for (apart from the fact that you close the question after 1 week of no feedback at all :(
ASKER
angellll:
i found that using DMAX, as other functions, acutally takes a little longer unlike the sql, plus tweaking it a little more, it does display the correct answer.
But thank you, thank you all for your time and effort, I really appreciate that; I've recommneded expterts exchange to other collegues since years before.
Again, thank you all and especilly angellll.
i found that using DMAX, as other functions, acutally takes a little longer unlike the sql, plus tweaking it a little more, it does display the correct answer.
But thank you, thank you all for your time and effort, I really appreciate that; I've recommneded expterts exchange to other collegues since years before.
Again, thank you all and especilly angellll.
Open in new window