0

I'm using Access, and I have a table of forms that people submit. I need to find all of the people who submitted forms in one month but not another. More specifically, I need to find all of the forms that are in a set where they match A in a type field, June in a month field, and August in a processed field which are not in a set where they match B in the type field and August in the month field.

Many people are supposed to submit additional forms in a later month, and I'm trying to find who hasn't done that.

I found this question: SQL: select all records not selected by another query. It seems similar, but I'm not really looking for duplicates.

Community
  • 1
  • 1
Tagg Ridler
  • 87
  • 1
  • 5

1 Answers1

1

I believe that you are looking for something like this:

SELECT PersonID FROM FormsSubmitted
WHERE 
    [type]="A" 
    AND 
    [month]="June"
    AND
    PersonID NOT IN (
        SELECT PersonID FROM FormsSubmitted
        WHERE [type]="B" AND [month]="August"
    )

Notes:

  • This answer makes a few assumptions, but I hope it gives you an idea on how to proceed.

  • One of those assumptions is that the [month] field is stored as text. If so, then that is almost always a Bad Idea™. If that is the case and it is practical for you to change the [month] field to a number, then you should seriously consider doing so.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • That syntax is just what I needed. Thank you. The month field is text, and I wish it was a number, but I don't have the authority to change any types. – Tagg Ridler May 24 '13 at 21:06