Isaac, thanks man, that works too and looks better than my case statements. Shame on you for thinking I'd even have Access installed on my machine though! It's MySQL.
--Ferg S. Isaac Dealey wrote: >Hey Ken, > >I've had those days... try this: > >SELECT A.ID , B.ENTITY_NAME, C.ENTITY_NAME AS PARENT_NAME >FROM ENTITY A >INNER JOIN ENTITY_NAME B ON > (b.entity_id = a.id and now() > b.start_date and > (b.end_date is null or b.end_date > now())) >LEFT JOIN ENTITY_NAME C ON > (c.entity_id = a.parent_id and now() > c.start_date and > (c.end_date is null or c.end_date > now())) > >Though... this is Access isn't it? ... you may have some difficulty >getting access to accept the parenthesis in that order, since I think >the inner and left join statements need to be nested... With this one >I might start with the visual query builder in Access first to get the >left join syntax and then add the date filters. > > > >s. isaac dealey 954.522.6080 >new epoch : isn't it time for a change? > >add features without fixtures with >the onTap open source framework > >http://www.fusiontap.com >http://coldfusion.sys-con.com/author/4806Dealey.htm > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:210221 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
