troubleshooting Question

Translate Query from SQL Server to MS Access

Avatar of PagodNaUtak
PagodNaUtakFlag for Philippines asked on
Microsoft AccessMicrosoft SQL ServerSQL
1 Comment1 Solution289 ViewsLast Modified:
I use this code in MS SQLServer database now I want to use the same query in an access database how do you translate the below query so it is compatible to MS ACCESS.

TableName:Timelogs

Fields:
ID - Integer - Primary key.
EmpID - Integer - Foreign Key
Name - Nvarchar
LogDate - DateTime

Any idea is greatly appreciated.

Thanks
select employeeID, [LogDate1],[LogDate2],[LogDate3],[LogDate4],[LogDate5],[LogDate6],[LogDate7],[LogDate8]
from 
( select employeeID, dt, rowid=(rn-1)/8+1, rn='LogDate' + convert(varchar,(rn-1)%8+1), RecordDateTime from
(       select employeeID, RecordDateTime,
                dt=dateadd(d,0,datediff(d,0,RecordDateTime)),
                rn=ROW_NUMBER() over (partition by employeeID, dateadd(d,0,datediff(d,0,RecordDateTime)) order by RecordDateTime)
        from TimeLogs) P
) P2
pivot
(max(RecordDateTime) for rn in ([LogDate1],[LogDate2],[LogDate3],[LogDate4],[LogDate5],[LogDate6],[LogDate7],[LogDate8])) as VT
ORDER BY employeeID, dt, rowid
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros