Convert sql query
- by nisha
I have used this query in sql,pls convert this query to use for access database.
Table structure is UserID,Username,LogDate,LogTime
WITH
[TableWithRowId] as
(SELECT ROW_NUMBER() OVER(ORDER BY UserId,LogDate,LogTime) RowId, * FROM @YourTable),
[OddRows] as
(SELECT * FROM [TableWithRowId] WHERE rowid % 2 = 1),
[EvenRows] as
(SELECT *, RowId-1 As OddRowId FROM [TableWithRowId] WHERE rowid % 2 = 0)
SELECT
[OddRows].UserId,
[OddRows].UserName,
[OddRows].LogDate,
[OddRows].LogTime,
[EvenRows].LogDate,
[EvenRows].LogTime
FROM
[OddRows] LEFT JOIN [EvenRows]
ON [OddRows].RowId = [EvenRows].OddRowId
