Access SQL: Pull Related Information From Another Table Alongside Group By -

-

this question involves following code:

select a.`fsr name` [last name],      min(a.`actual tep in`) [date in],      max(b.site) site,      max(b.position) position,     max(b.comments) comments deployments inner join deployments b on a.id = b.id a.`actual tep in` > now() group a.`fsr name`;

the point of code query first date beyond today each person, , pull 1 record. example there can many smiths dates before , after today, , want show record smith after today. idea show each person's next date on cascading list. @ least part, code works.

the problem having information needs come alongside it. group forces aggregates keep pulling wrong site/position/comments fields when min or max them. i'm not sure should doing @ point seems i'm bit on head. appreciated!

note: i'm doing in access 2013.

edit: adding rough table

fsr name    date in     site    position    comments bob         7/3/2014      op          notes joe         10/11/2013  b   op   joe         8/4/2014    c   op          sea joe         9/1/2014    d   op          dep buckley     11/1/2012     op          transfer buckley     10/7/2012   b   op   jones       6/10/2012     op   jones       4/27/2013     op          3 notes jones       6/21/2011   b   op   jones       3/26/2012   b   op          5 notes jones       6/10/2012   c   op          1 notes jones       3/23/2014   c   op          2 notes jones       7/5/2014    d   op          dep calvillo    2/21/2014     op   calvillo    10/3/2014   b   op          com calvillo    11/10/2014    op          dep casey       5/2/2014    c   op   

select d1.[fsr name], first(d2.[date in]), ... deployments d1       left join (select * deployments d3 d3.[date in] > now() order d3.[date in]) d2          on d1.[fsr name] = d2.[fsr name] group d1.[fsr name];

solution description: joining ordered list , first() return values same row. first() aggregate function exists in access, other database engines have similar ones or can deal without it.


Comments

Post a Comment

Popular posts from this blog

double exclamation marks in haskell -

-
i have code: ghci>let listoffuns = map (*) [0..] ghci>(listoffuns !! 4) 5 20 what !! mean? i saw example double exclamation this: ghci> [1,2,3,4]!!1 ghci> 2 but seems don't apply question example. how understand function. need explanations. !! indexes lists. takes list , index, , returns item @ index. if index out of bounds, returns ⊥.
Read more

qml - Is it possible to implement SystemTrayIcon functionality in Qt Quick application -

-
i writing qtquick desktop application. use both c++ (for functionality) , qml (for ui) in it. use qquickview show interface written in qml. i want application reside in system tray when minimised. i mean functionality similar example. http://qt-project.org/doc/qt-4.8/desktop-systray.html . trying implement feature not find way in qt quick application. here main.cpp code: #include <qguiapplication> #include <qqmlengine> #include <qqmlcontext> #include <qqmlfileselector> #include <qquickview> #include "myapp.h" int main(int argc, char* argv[]) { qguiapplication app(argc,argv); app.setapplicationname(qfileinfo(app.applicationfilepath()).basename()); qdir::setcurrent(qapp->applicationdirpath()); myapp myappobject; qquickview view; view.connect(view.engine(), signal(quit()), &app, slot(quit())); view.rootcontext()->setcontextproperty("myappobject", &myappobject); new qqmlfilese...
Read more

Qt Creator - Searching files with Locator including folder -

-
one quick question qtcreator : know possible use locator open files using "ctrl-k" shortcut. however, possible search files including locations? for example, let's want locate file named "main.cpp" in folder named "myfolder" , have lots of other main.cpp files. what should type locator in order open ? just type myfolder/main.cpp in locator
Read more