ACCESS FEED: Re: [MS_AccessPros] sql queries

Rabu, 28 Oktober 2015

Re: [MS_AccessPros] sql queries

Making you jump through hoops?

Try this:

SELECT DISTINCT a.companyid, CDate(Int(a.entrydate)) AS DateEntry, a.lastupdate, CDate(Int(e.entrydate)) AS DateEntryPrev, e.lastupdate AS LastUpdatePrev
FROM tblReview AS a INNER JOIN tblReview As e ON a.companyid = e.companyid
WHERE (((a.entrydate) Is Not Null And (a.entrydate)=(select max(entrydate) from tblReview as b where b.companyid =a.companyid))) AND (((e.entrydate)=(select max(entrydate) from tblReview as c where c.companyid = d.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;

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 28, 2015, at 3:17 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•