Complex query: Find clients with unanswered questions

[svesjo]

I think this got a little bit too complex for me. My problem, very simplified:
I have the following tables:

Clients:
- ID
- Name

Events:
- ID


- ClientID
- Type (number, 1 or 2. 1 means question, 2 means answer)
- Date

The tables are related by ID - ClientID.

I want a query that lists all clients that have unanswered questions. I want to do it this way:
Compare the date of the last event type 1 (question) with the date of the last event type 2 (answer) for each client.
If the last answer date is earlier than the last question date, it means there are unanswered questions, and I want the client output in the query.
The query must also work if there are no events of one type or the other.

Anyone have suggestions, using query designer or sql?

- svesjo

[burrina]

2 part solution. First get the DateDiff between Answer and Question
Then add that qry to a new query as a Select Totals Query.

1st qry:

AQ: DateDiff("d",[AnswerDate],[QuestionDate])

HTH

[pbaldy]

It looks like questions and answers are different records. I feel like this is in the right direction:

http://www.baldyweb.com/LastValue.htm

The ones where the last record is a 1 are your unanswered questions. Regarding "The query must also work if there are no events of one type or the other", it would seem irrelevant. If there are no records then there are no unanswered questions.

[burrina]

svesjo , Good Luck With Your Project!