Selecting right appointment

[thesuggs76]

Hi All
I'm trying to work a query that will allow me to select the outcome of an appointment that has taken place within a certain timeframe. The outcome would be either Attended or DNA. However the problem I have is that an individual can have more that one appointment within this timeframe, several in fact. They could have 6 appointments, the first 5 were DNA'd then the 6th would be attended. And others will only have one appointment and the outcome could be either Attended or DNA.
I need to count the Attended for the first example i mentioned but not to count the DNA as I only want to count DNA for that individual if they have not attended any appointments.
The table I'm using is called Appointments.

Thanks

 

[SydneyGeek]

Try setting up a calculated expression that returns 1 for Attended and 0 for DNA.
Then you can query, grouping by client and date range and taking the Max of the calculated field. Max=0 will show someone who never attended.

Denis

 

[thesuggs76]

Try setting up a calculated expression that returns 1 for Attended and 0 for DNA.
Then you can query, grouping by client and date range and taking the Max of the calculated field. Max=0 will show someone who never attended.

Denis

Hi, thanks for replying. I've included what the data looks like below. For example I'm looking for all those that attended and did not attend an appointment. As you can see ID 1, although they DNA'd 2 appointments still attended the last. I don't need his DNA to count. If I do as you suggest would it still not count his first DNA

ID Appointment Status
1 01/01/13 DNA
1 02/01/13 DNA
1 03/01/13 Attended
2 04/01/13 Attended
3 04/01/13 DNA

 

[SydneyGeek]

No, that's why I used MAX. 1 is bigger than 0, so DNA won't appear in the grouped total.

Denis

 

[thesuggs76]

No, that's why I used MAX. 1 is bigger than 0, so DNA won't appear in the grouped total.

Denis

Thanks for replying. It's working out those that DNA I'm finding hard to work out. Unlike the Attend, with DNA I need to ignore any individual who has attended an appointment. An individual may have 5 appointments, DNA'd the first 4 but attended the 5th. I don't want to include him I only want those where they have DNA'd every appointment within this time period

 

[SydneyGeek]

If you don't display the date (you can filter by dates to get the desired date range) and then group by the ID and use the MAX of the calculated field I described, you'll get the result: ID, Score (0 or 1).
Save the query and join to a dummy table with 2 fields; Score (values: 0 or 1) and Status (values: DNA or Attended).
Display the ID and the Status.

sometimes, one query doesn't do the job completely...

Denis

 

[thesuggs76]

If you don't display the date (you can filter by dates to get the desired date range) and then group by the ID and use the MAX of the calculated field I described, you'll get the result: ID, Score (0 or 1).
Save the query and join to a dummy table with 2 fields; Score (values: 0 or 1) and Status (values: DNA or Attended).
Display the ID and the Status.

sometimes, one query doesn't do the job completely...

Denis

Thanks Dennis