combining dates in query

[WimDC]

Hi,



I have a table (or query) with the following fields

 
USER_ID
CODE        (can be "A" or "B")
DATUM      (dutch for "date")
REF_DATE
REF_EXPLICATION

Each record HAS data for the first 3 fields; the other fields CAN have data. (of course, each user and each code is linked to a separate table with name, etc...)

Now, I want to build a query that shows me the records (with user_id, code_id, ref_explication)...
1) where CODE = "A" and there's no record where the CODE = "B AND where DATE is equal to REF_DATE of the record with code = “A"
or
2) where CODE = "A" and there's no REF_DATE
or
3) where CODE = "B" and there's no REF_DATE

Giving the sample database included, I would like to have this result :

 
USER_ID  CODE_ID - DATE        - REF_EXPLICATION_1    -   REF_DATE  -    REF_EXPLICATION_2
   1          A        07/08/2013       01/01/2014          phonecall               (*)
   1          A        08/08/2013       (**)
   1          B        10/08/2013       (***)

(*) because there's no matching record with code=B AND date=ref_date
(**) because code=A and there's no ref_date
(**) because code=B and there's no ref_date

Can someone help me or give me some suggestions?
I also split the table in records with code= "A" and in records with code = "B". (see queries) Maybe this can help ?

[June7]

First line in post shows 07/08/2013 but record in table shows 08/07/2013.
Third line in post shows 10/08/2013 but record in table shows 8/10/2013.
You should review http://allenbrowne.com/ser-36.html

Don't really understand these rules.

Why return record with ref_date 1/12014 and not 8/23/2013?
Why not return the one record for user_id 2?

If your rules require each record to consider values of other records for criteria, that is not easy to do.

[WimDC]

Hi June7,

thanks for your reply; the dates in my "output"-code are in Dutch (dd-mm-yyyy), but I think you opened my database in an English version.

In the meanwhile, I found an answer for my problem : http://www.access-programmers.co.uk/...29#post1288129

Wim