I initially created 2 separate queries (shown below) then UINION them to get the "most recent date" records and the records that have the dates right before the most recent date records.
SELECT DISTINCT a.companyid, a.entrydate, a.lastupdate
FROM tblReview AS a
WHERE a.entrydate=(select max(b.entrydate) from tblReview as b Where b.companyid = a.companyid)
ORDER BY a.companyid, a.entrydate DESC;
UNION
SELECT DISTINCT a.companyid, a.entrydate, a.lastupdate
FROM tblReview AS a
WHERE (((a.entrydate)=(select max(b.entrydate) from tblReview as b where b.companyid =a.companyid and b.entrydate < (select max(c.entrydate) from tblReview as c where c.companyid = a.companyid))));
With help from Mr. Viescas, I was able to build a better "one step" query (as shown below) to get the same result.
SELECT DISTINCT a.companyid, CDate(Int(a.entrydate)) AS DateEntry, a.lastupdate
FROM tblReview AS a
WHERE (((a.entrydate) Is Not Null And (a.entrydate)=(select max(entrydate) from tblReview as b where b.companyid =a.companyid))) OR (((a.entrydate)=(select max(entrydate) from tblReview as c where c.companyid = a.companyid and c.entrydate < (select max(entrydate) from tblReview as d where d.companyid = a.companyid))))
ORDER BY a.companyid, CDate(Int(a.entrydate)) DESC;
Now, the users want the most recent records stand side-by-side, next to the 2nd most recent records (the records which right before the most recent record).
e.g. Company_ID, MostRecent_date, MostRecentUpdate_date, 2ndRecent_date, 2ndUpdate_date
I was able to do it, by joinning the 2 separate queries together instead of UNION them. I have been wondering if that can be done by using the 'one step' query, instead of writing 2 separate queries then join them. Is it possible? I've tried, but did not figure out how to do it.
Thank you
Phucon
Posted by: saigonf7q5@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar