combining dates in query

WimDC · Aug 26, 2013

Hi,

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

Code:

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 :

Code:

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

(*) 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 ?

PeterF · Aug 26, 2013

I'm not sure I understand your requirements correct but try this SQL.

Code:

SELECT Tabel1.user_id, Tabel1.code_id, Tabel1.datum, Tabel1_1.ref_date, Tabel1_1.ref_explication, Tabel1_1.ref_date
FROM Tabel1 LEFT JOIN Tabel1 AS Tabel1_1 
ON (Tabel1.datum = Tabel1_1.ref_date) AND (Tabel1.user_id = Tabel1_1.user_id)
WHERE (((Tabel1.code_id)<>[Tabel1_1].[code_id])) OR (((Tabel1_1.ref_date) Is Null));

WimDC · Aug 26, 2013

Hi PiterF,

thanks for your reply; I had a look, and you use the table twice. That's new to me, but seems very interesting...
Your query is not 100% what I have in mind, but it's really close

.
I think I can use your query to get my output. If not, I'll be back in this forum

.

Wim

WimDC said:
Hi,

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

Code:

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 ?

WimDC · Aug 26, 2013

Problem solved, thanks to the PeterF who's query pointed me in the right direction.

combining dates in query

WimDC

Registered User.

Attachments

PeterF

Registered User.

WimDC

Registered User.

WimDC

Registered User.

Similar threads

Users who are viewing this thread