Repost with correct subject: Need help with Query to select oldest date

[John W. Vinson]

I hope I can say this half way intelligently. I have a record set with 2
date fields (One in date and One out date) I have a query to select the
records between a specific date range (no problem) now problem: I need to
also select the earliest date in at the same time. So thus far I have this
code as an expression in empty field: Cohort: IIf([relsdate] Between
[7/1/fy] And [6/30/fy],"1"," ") this codes all who are released between my
date selection with a 1. Often there are more than one record for a person
with different time intervals. Simply, I need to code the earliest record
for each cohort that is within the relsdate range and code it with the 1.
I am pretty new at this coding stuff; got a lot to learn.
Thanks
Pat

You'll need a Subquery with a criterion such as

(SELECT Min([relsdate]) FROM mytable AS X WHERE <some matching criteria>)

If you'll post the actual SQL text of your current query we may be able to
help add this criterion for you.


John W. Vinson [MVP]

 

[John W. Vinson]

The SQL from this query is as follows. I works great for coding all of the
between date files; but there are always more than one file for each person.

SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last, AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC, IIf([relsdate] Between [7/1/fy] And [6/30/fy],"1","
") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings;

Do you want the query to...

display only the earliest date for this ID in the table?
display only the earliest date within the range you specify in the IIF?
something else?

What are some examples of the data in the table, and just what result do you
want to see?

John W. Vinson [MVP]

 

[John W. Vinson]

Sorry, I'm not very good at explaning this. Try to keep it simple.
I need the entire record (only one record each ID) with the earliest date
from the BookDate field and the RelsDate within the IIf, to be coded with a
1, in a seperate field.

SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last, AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC, IIf([relsdate] Between [7/1/fy] And [6/30/fy],"1","
") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings
WHERE [relsdate] = (SELECT Min([relsdate] FROM AllRelsAllBookings AS X
WHERE X.ID = AllRelsAllBookings.ID);

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you so much. Almost there. The coding is working beautiful with the
code you gave me added. The only thing now is; the return is only these
coded records. I don't think I said very clearly if at all, before that I
also need to return all the other records that did not code with the 1. Can
you help me with that?

The query should return one record for each value of ID, the earliest record
for that ID. The coding shouldn't affect which records are retrieved at all.

John W. Vinson [MVP]

 

[John W. Vinson]

There are several records for each ID but all with different dates. I need
only to code the IIf and earliest BookDate not get rid of the others.
Maybe I am complicating this. Maybe I need more than one query.

ahhhh.... I misunderstood. My apologies! You want to see all the records, but
the value of Cohort should be 1 if the *EARLIEST* relsdate for that ID is
within the specified date range, and blank otherwise? If so:


SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last, AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC,
IIf(
DMin("[relsdate]", "[AllRelsAllBookings]", "[ID] = " & AllRelsAllBookings.ID)

= DateSerial([Enter FY:], -6, 1)

AND
DMin("[relsdate]", "[AllRelsAllBookings]", "[ID] = " & AllRelsAllBookings.ID)
< DateSerial([Enter FY:], 7, 1),"1","") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings;


John W. Vinson [MVP]

 

[Dupatt]

I hope I can say this half way intelligently. I have a record set with 2
date fields (One in date and One out date) I have a query to select the
records between a specific date range (no problem) now problem: I need to
also select the earliest date in at the same time. So thus far I have this
code as an expression in empty field: Cohort: IIf([relsdate] Between
[7/1/fy] And [6/30/fy],"1"," ") this codes all who are released between my
date selection with a 1. Often there are more than one record for a person
with different time intervals. Simply, I need to code the earliest record
for each cohort that is within the relsdate range and code it with the 1.
I am pretty new at this coding stuff; got a lot to learn.
Thanks
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

 

[Dupatt]

The SQL from this query is as follows. I works great for coding all of the
between date files; but there are always more than one file for each person.

SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last, AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC, IIf([relsdate] Between [7/1/fy] And [6/30/fy],"1","
") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings;


--
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

I hope I can say this half way intelligently. I have a record set with 2
date fields (One in date and One out date) I have a query to select the
records between a specific date range (no problem) now problem: I need to
also select the earliest date in at the same time. So thus far I have
this
code as an expression in empty field: Cohort: IIf([relsdate] Between
[7/1/fy] And [6/30/fy],"1"," ") this codes all who are released between
my
date selection with a 1. Often there are more than one record for a
person
with different time intervals. Simply, I need to code the earliest record
for each cohort that is within the relsdate range and code it with the 1.
I am pretty new at this coding stuff; got a lot to learn.
Thanks
Pat

You'll need a Subquery with a criterion such as

(SELECT Min([relsdate]) FROM mytable AS X WHERE <some matching criteria>)

If you'll post the actual SQL text of your current query we may be able to
help add this criterion for you.


John W. Vinson [MVP]

 

[Dupatt]

Sorry, I'm not very good at explaning this. Try to keep it simple.
I need the entire record (only one record each ID) with the earliest date
from the BookDate field and the RelsDate within the IIf, to be coded with a
1, in a seperate field.

--
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

The SQL from this query is as follows. I works great for coding all of
the
between date files; but there are always more than one file for each
person.

SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last, AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC, IIf([relsdate] Between [7/1/fy] And
[6/30/fy],"1","
") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings;

Do you want the query to...

display only the earliest date for this ID in the table?
display only the earliest date within the range you specify in the IIF?
something else?

What are some examples of the data in the table, and just what result do
you
want to see?

John W. Vinson [MVP]

 

[Dupatt]

Thank you so much. Almost there. The coding is working beautiful with the
code you gave me added. The only thing now is; the return is only these
coded records. I don't think I said very clearly if at all, before that I
also need to return all the other records that did not code with the 1. Can
you help me with that?

--
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

Sorry, I'm not very good at explaning this. Try to keep it simple.
I need the entire record (only one record each ID) with the earliest date
from the BookDate field and the RelsDate within the IIf, to be coded with
a
1, in a seperate field.

SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last, AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC, IIf([relsdate] Between [7/1/fy] And
[6/30/fy],"1","
") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings
WHERE [relsdate] = (SELECT Min([relsdate] FROM AllRelsAllBookings AS X
WHERE X.ID = AllRelsAllBookings.ID);

John W. Vinson [MVP]

 

[Dupatt]

There are several records for each ID but all with different dates. I need
only to code the IIf and earliest BookDate not get rid of the others.
Maybe I am complicating this. Maybe I need more than one query.

 

[Dupatt]

I think this is confusing. It is still coding all of the ID within the FY.
I am enclosing a small sample of my fields to help explain.......There are
other fields but thise are the ones we are dealing with. Notice that some of
the ID's have more than one book# and date. Hope this will explain clearer.
Thanks for your patience.
ReadyToColorCode2

id
Book#
BookDate
RelsDate
Cohort

6173
32284
11/14/2001
6/16/2003
1

6150
32519
1/15/2002
1/15/2004
1

6150
37452
4/7/2005
4/14/2006
1

6160
28347
6/16/1999
6/16/2000
1

6160
30773
12/1/2000
3/8/2001
1

6167
27241
10/6/1998
11/20/2000
1

6171
27248
10/9/1998
7/28/2000
1

6171
32306
11/20/2001
2/19/2002
1

904
20631
1/27/1993
1/18/2001
1

6173
30059
7/3/2000
9/7/2000
1

6149
27213
10/1/1998
4/21/2001
1

6173
35365
12/23/2003
1/28/2004
1

6174
29790
5/1/2000
7/6/2000
1

6182
27259
10/12/1998
1/24/2001
1

6184
27268
10/13/1998
9/12/2000
1

6202
27300
10/21/1998
8/14/2000
1

6202
30377
9/5/2000
5/24/2001
1

6062
31292
3/26/2001
3/29/2001
1

6171
34427
5/2/2003
10/15/2003
1

6116
27151
9/16/1998
9/22/2000
1

6782
28215
5/18/1999
11/27/2000
1

6066
27063
8/25/1998
8/28/2000
1

6067
27064
8/25/1998
6/26/2001
1

6080
27094
9/3/1998
9/5/2000
1

1051
37681
6/1/2005

1

1051
31764
7/13/2001
3/7/2005
1

1051
20704
2/18/1993
1/2/2001
1

6150
27216
10/2/1998
10/2/2000
1

6100
27114
9/8/1998
12/11/2000
1

6149
37442
4/5/2005
10/5/2006
1

6116
30622
10/27/2000
4/16/2001
1

6120
27380
11/6/1998
4/25/2001
1

6120
32728
3/11/2002
1/31/2003
1

995
30298
8/23/2000
9/15/2000
1

995
22074
6/6/1994
8/23/2000
1

980
10314
6/27/1972
11/11/2000
1

6138
28710
8/31/1999
12/29/2000
1

6209
27309
10/26/1998
12/15/2000
1

6099
27113
9/8/1998
3/26/2001
1

6316
27477
12/2/1998
8/2/2000
1

6303
32196
10/24/2001
7/5/2002
1

6303
36450
8/26/2004
4/27/2005
1


--
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

There are several records for each ID but all with different dates. I
need
only to code the IIf and earliest BookDate not get rid of the others.
Maybe I am complicating this. Maybe I need more than one query.

ahhhh.... I misunderstood. My apologies! You want to see all the records,
but
the value of Cohort should be 1 if the *EARLIEST* relsdate for that ID is
within the specified date range, and blank otherwise? If so:


SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last, AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC,
IIf(
DMin("[relsdate]", "[AllRelsAllBookings]", "[ID] = " &
AllRelsAllBookings.ID)

= DateSerial([Enter FY:], -6, 1)

AND
DMin("[relsdate]", "[AllRelsAllBookings]", "[ID] = " &
AllRelsAllBookings.ID)
< DateSerial([Enter FY:], 7, 1),"1","") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings;


John W. Vinson [MVP]

 

[Dupatt]

That lost it's format in transit. LOL They should all be transposed.

--
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

I think this is confusing. It is still coding all of the ID within the FY.
I am enclosing a small sample of my fields to help explain.......There are
other fields but thise are the ones we are dealing with. Notice that some
of the ID's have more than one book# and date. Hope this will explain
clearer. Thanks for your patience.
ReadyToColorCode2

id
Book#
BookDate
RelsDate
Cohort

6173
32284
11/14/2001
6/16/2003
1

6150
32519
1/15/2002
1/15/2004
1

6150
37452
4/7/2005
4/14/2006
1

6160
28347
6/16/1999
6/16/2000
1

6160
30773
12/1/2000
3/8/2001
1

6167
27241
10/6/1998
11/20/2000
1

6171
27248
10/9/1998
7/28/2000
1

6171
32306
11/20/2001
2/19/2002
1

904
20631
1/27/1993
1/18/2001
1

6173
30059
7/3/2000
9/7/2000
1

6149
27213
10/1/1998
4/21/2001
1

6173
35365
12/23/2003
1/28/2004
1

6174
29790
5/1/2000
7/6/2000
1

6182
27259
10/12/1998
1/24/2001
1

6184
27268
10/13/1998
9/12/2000
1

6202
27300
10/21/1998
8/14/2000
1

6202
30377
9/5/2000
5/24/2001
1

6062
31292
3/26/2001
3/29/2001
1

6171
34427
5/2/2003
10/15/2003
1

6116
27151
9/16/1998
9/22/2000
1

6782
28215
5/18/1999
11/27/2000
1

6066
27063
8/25/1998
8/28/2000
1

6067
27064
8/25/1998
6/26/2001
1

6080
27094
9/3/1998
9/5/2000
1

1051
37681
6/1/2005

1

1051
31764
7/13/2001
3/7/2005
1

1051
20704
2/18/1993
1/2/2001
1

6150
27216
10/2/1998
10/2/2000
1

6100
27114
9/8/1998
12/11/2000
1

6149
37442
4/5/2005
10/5/2006
1

6116
30622
10/27/2000
4/16/2001
1

6120
27380
11/6/1998
4/25/2001
1

6120
32728
3/11/2002
1/31/2003
1

995
30298
8/23/2000
9/15/2000
1

995
22074
6/6/1994
8/23/2000
1

980
10314
6/27/1972
11/11/2000
1

6138
28710
8/31/1999
12/29/2000
1

6209
27309
10/26/1998
12/15/2000
1

6099
27113
9/8/1998
3/26/2001
1

6316
27477
12/2/1998
8/2/2000
1

6303
32196
10/24/2001
7/5/2002
1

6303
36450
8/26/2004
4/27/2005
1


--
Pat

"If you can find a path with no obstacles, it probably doesn't lead
anywhere"

There are several records for each ID but all with different dates. I
need
only to code the IIf and earliest BookDate not get rid of the others.
Maybe I am complicating this. Maybe I need more than one query.

ahhhh.... I misunderstood. My apologies! You want to see all the records,
but
the value of Cohort should be 1 if the *EARLIEST* relsdate for that ID is
within the specified date range, and blank otherwise? If so:


SELECT AllRelsAllBookings.id, AllRelsAllBookings.[Book#],
AllRelsAllBookings.first, AllRelsAllBookings.last,
AllRelsAllBookings.dob,
AllRelsAllBookings.sex, AllRelsAllBookings.BookType,
AllRelsAllBookings.BookDate, AllRelsAllBookings.RelsDate,
AllRelsAllBookings.RelsType, AllRelsAllBookings.RelsTo,
AllRelsAllBookings.NCIC,
IIf(
DMin("[relsdate]", "[AllRelsAllBookings]", "[ID] = " &
AllRelsAllBookings.ID)

= DateSerial([Enter FY:], -6, 1)

AND
DMin("[relsdate]", "[AllRelsAllBookings]", "[ID] = " &
AllRelsAllBookings.ID)
< DateSerial([Enter FY:], 7, 1),"1","") AS Cohort INTO ReadyToColorCode2
FROM AllRelsAllBookings;


John W. Vinson [MVP]