Access SQL Query: Comparing Date In Select Statement -

i have problem cannot seem figure out. have list of employees different travel dates , want display of them in cascading list format. problem want see employees once, , date closest today.

for example have 'smith' in there multiple times dates before , after today, keep historical records. means can't min, try , display date before today, , max far forward.

the code example below works. problem in select statement. want show minimum date after today, instead gives me 0's , -1's dates should be. there might way together, configuration seems allow other information such site, position, , comments displayed correctly alongside it.

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

i did group name because want see each individual once. if don't add table join gives self reference error. first time posting hope makes sense! appreciated!

not sure db you're on, in general, need return min(date) instead of result of comparison "min(date) > now()" - i'm guessing you're seeing 0's , -1's, since result of comparison, when want minimum date value itself.

also, if wanting people have trip date in future, restrict query clause, group by, , rid of self-join. note example below aligns discrepancies in op you're selecting based on "last name" grouping on "fsr name" - these things must consistent, whichever field you're concerned about.

example:

select a.[fsr name] [fsr name],      min(a.[date in]) [date in],      max(a.site) site,      max(a.position) position,     max(a.comments) comments deployments  a.[date in] > now() group a.[fsr name]; 

edit: if need make sure site,position,comments came same row, have 1 of these options:

if have primary key:

select * deployments a3 a3.pk_value =      (select max(a2.pk_value) deployments a2       a2.[date in] =          (select max([date in]) deployments a.[fsr name] = a2.[fsr name])      , a2.[fsr name] = a3.[fsr name]     ) 

this guarantees 1 row per fsr name, if there multiple rows fsr same "latest" date.

otherwise, can leave out secondary query dealing pk_value, run risk of getting multiple rows fsr has multiple records same "latest" date.

note: when queries complex, running on full-featured database (sql server, oracle, access) allows use more sophistication. example, "windowing functions" give answer without wrangling. not sure if you're stuck access now, consider future, anyway.