CODEWITHSUNDEEP
sqlms-accessms-access-2016
user2377141
user2377141

Reputation: 132

MS Access subquery from identical table

I am currently working on an access database where we collect customers feedback.

I have one table with the following structure and data :

table data and structure

And I want to display the following result :

result

Indeed, what I want is a MS Access Request that displays, for every date value in my table, the amount of records that matches the same date on the column "date_import" (2nd column of the result) and the amount of records that matches this criteria on the column "date_answered" (3rd column of the result).

I have no idea how to do this since all the subqueries should be aware of each other.

Has anyone ever faced this issue and might be able to help me ?

Thanks in advance,

P.S. : I'm using the 2016 version of MS Access but I'm pretty sure what I'm trying to do is also achievable in previous versions of Access, this is what I added several tags.

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Hmmm . . . I think this will work:

select dte, sum(is_contact), sum(is_answer)
from (select date_import as dte, 1 as is_contact, 0 as is_answer
      from t
      union all
      select date_answers, 0 as is_contact, 1 as is_answer
      from t
     ) t
group by dte;

Not all versions of MS Access allow union all in the FROM clause. If that is a problem, you can create a view and then select from the view.

Upvotes: 2

Related Questions