Selecting only 1 record from a group of 6

[Guest]

I have query in which I produce the following type of records:

ID TDate LOC ST_DATE END_DATE Support
820 12/7/2007 1 1/25/2008 12/31/2099 no
820 12/7/2007 2 2/22/2008 12/31/2099 no
820 12/7/2007 3 3/21/2008 12/31/2099 no
820 12/7/2007 4 11/30/2007 12/31/2099 Yes
820 12/7/2007 5 3/7/2008 12/31/2099 no
820 12/7/2007 6 12/7/2007 12/31/2099 Yes

ID = customer ID number
TDate = Training Date required
LOC = There is a location assigned to each one of these that coordinates
with the customer's preferred training location.
ST_Date = Available classroom date
Support = The results of an If statement that tells me if we have the
classroom support for the customer's required training date.

The information in the table above tells me that we do not have support for
their first three as well as their fifth preferred training locations but we
do have the classroom support for their fourth and sixth preferred locations.
I'm trying to write a query that will return the first available preferred
training location going in order of 1-6. So in this instance for this
particular training date the fourth location would be returned.

I'd really appreciate if someone could help me work through a statement that
returns this value. My preferred method would be to look at the group of
each customer (6 records for each) and return only the record that is
appropriate.

Another thing I need to consider is that if we do not have any classrooms
available for their TDate than I need to push their TDate to the next
available classroom St_date.

Thanks in advance for anything you can offer.

 

[Jeff Boyce]

Tina

Just because you've numbered the locations 1-6 doesn't mean that #4 is this
person's first choice, does it? Why is #4 more important than #6?

If you are comfortable with your answer, then you can easily do this.

Create a new query, select the LOC and Support. Sort by LOC, use "Yes" as a
selection criterion for Support. Now change the Top property of the query
to return only the first record.

Another way to do this would be to create a new query, change it to a Totals
query, select ID (GroupBy) and LOC (Min), and use "Yes" as a (Where)
criterion for Support.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Try totals query with the following fields ---
ID ST_DATE Support
Set the Totals row like this --
Group By Min Where
Enter criteria for Support as "Yes" if it is a text field or as -1 if it is
a Yes/No field.