Need to select a certain X records after a query in access

"ab" wrote in message
...
So, NOW I need to select 20 records after each date a userenters. For
example, if a user enters 1/19/05 and 4/20/05, I need the 20 records
(business days) prior to 1/19/05 AND the 20 records prior to 4/20/05.

I'm a bit confused. This seems contradictory. "20 records after", "20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough to provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for only the FIRST
select query, while the SECOND select query simply pulls the MOST RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the records "after"
the entered date, then both parts of the Union need to be sorted ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to enter TWO
dates
and
produce the 15 records prior to each of the dates. For example, if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15 records prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do this ???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed number of days or
months between or something like that) you would probably need to use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc

Hi Randy. Thanks again for your help, as I'm obviously a new access user.
Yes, I'm trying to find the 20 records BEFORE a date, and 20 records after. I
assumed that if I could solve the problem on how to find the 20 records prior
to the DATE(s) the user entered, I could figure out how to find the "AFTER"
records. Obviously, it wasn't so simple. ;-)

Regarding the "ORDER BY dataset.Date DESC", that was a TYPO. I do have both
statements using the ascending argument.

As I said, the query results in generating the CORRECT records for the FIRST
date the user enters, but for the 2nd date produces the most recent
(datewise) 20 records in the table. So, I get 40 records total, 20 of which
are what I expected, and 20 are simply the most recent 20 records in the
table.

Ideas ?

Aaron

"Randy Harris" wrote:


"ab" wrote in message
...
So, NOW I need to select 20 records after each date a userenters. For
example, if a user enters 1/19/05 and 4/20/05, I need the 20 records
(business days) prior to 1/19/05 AND the 20 records prior to 4/20/05.

I'm a bit confused. This seems contradictory. "20 records after", "20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough to provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for only the FIRST
select query, while the SECOND select query simply pulls the MOST RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the records "after"
the entered date, then both parts of the Union need to be sorted ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to enter TWO
dates
and
produce the 15 records prior to each of the dates. For example, if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15 records prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do this ???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed number of days or
months between or something like that) you would probably need to use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc

Let me see if I've got this straight. You want the 20 records before AND
the 20 records after, each of two different dates? A total return of 80
records providing the dates are more than 20 days apart? What about if the
dates are 10 days apart - 50 records? Or still 80 records with some
duplicates?



"ab" wrote in message
...
Hi Randy. Thanks again for your help, as I'm obviously a new access user.
Yes, I'm trying to find the 20 records BEFORE a date, and 20 records
after. I
assumed that if I could solve the problem on how to find the 20 records
prior
to the DATE(s) the user entered, I could figure out how to find the
"AFTER"
records. Obviously, it wasn't so simple. ;-)

Regarding the "ORDER BY dataset.Date DESC", that was a TYPO. I do have
both
statements using the ascending argument.

As I said, the query results in generating the CORRECT records for the
FIRST
date the user enters, but for the 2nd date produces the most recent
(datewise) 20 records in the table. So, I get 40 records total, 20 of
which
are what I expected, and 20 are simply the most recent 20 records in the
table.

Ideas ?

Aaron

"Randy Harris" wrote:


"ab" wrote in message
...
So, NOW I need to select 20 records after each date a userenters. For
example, if a user enters 1/19/05 and 4/20/05, I need the 20 records
(business days) prior to 1/19/05 AND the 20 records prior to 4/20/05.

I'm a bit confused. This seems contradictory. "20 records after", "20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough to
provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for only the
FIRST
select query, while the SECOND select query simply pulls the MOST
RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the records
"after"
the entered date, then both parts of the Union need to be sorted
ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to enter TWO
dates
and
produce the 15 records prior to each of the dates. For example,
if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15 records
prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do this ???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed number of
days or
months between or something like that) you would probably need to
use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc

So, the overall goal/problem is this:
1. I have a table that contains daily sales data for a company, with the
data history going back 10+ years
2. The "user" will enter in a series of dates (the company end of quarter
dates for Q1, Q2, Q3, and Q4 for all years). Hence, since the DATES in which
the user enters will always be around 90 days apart, I don't have to worry
about overlapping records within the query results.
3. The GOAL is to determine the TREND of the sales data JUST BEFORE (20
days specifically before) the end of quarter date and JUST AFTER (20 days
AFTER) the end of quarter date specified by the user.

So, you educated me previously on how to select the X records BEFORE the
date(s) the user entered by using the "TOP" within the query and "UNION" to
combine the numerous queries. Now, I'm trying to find the 20 records (each
record being one business day of data) AFTER each of the END OF QUARTER dates
that the user will enter.

Make sense ? Thanks again for your help and patience!


"Randy Harris" wrote:

Let me see if I've got this straight. You want the 20 records before AND
the 20 records after, each of two different dates? A total return of 80
records providing the dates are more than 20 days apart? What about if the
dates are 10 days apart - 50 records? Or still 80 records with some
duplicates?



"ab" wrote in message
...
Hi Randy. Thanks again for your help, as I'm obviously a new access user.
Yes, I'm trying to find the 20 records BEFORE a date, and 20 records
after. I
assumed that if I could solve the problem on how to find the 20 records
prior
to the DATE(s) the user entered, I could figure out how to find the
"AFTER"
records. Obviously, it wasn't so simple. ;-)

Regarding the "ORDER BY dataset.Date DESC", that was a TYPO. I do have
both
statements using the ascending argument.

As I said, the query results in generating the CORRECT records for the
FIRST
date the user enters, but for the 2nd date produces the most recent
(datewise) 20 records in the table. So, I get 40 records total, 20 of
which
are what I expected, and 20 are simply the most recent 20 records in the
table.

Ideas ?

Aaron

"Randy Harris" wrote:


"ab" wrote in message
...
So, NOW I need to select 20 records after each date a userenters. For
example, if a user enters 1/19/05 and 4/20/05, I need the 20 records
(business days) prior to 1/19/05 AND the 20 records prior to 4/20/05.

I'm a bit confused. This seems contradictory. "20 records after", "20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough to
provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for only the
FIRST
select query, while the SECOND select query simply pulls the MOST
RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the records
"after"
the entered date, then both parts of the Union need to be sorted
ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to enter TWO
dates
and
produce the 15 records prior to each of the dates. For example,
if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15 records
prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do this ???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed number of
days or
months between or something like that) you would probably need to
use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc

You had it pretty much right before. You'll have to do multiple unions. To
get the 20 days before, select top 20 where date = date entered, then order
descending. To get the 20 days after, select top 20 where date = date
entered, then order ascending.


SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate DESC
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDATE = [date 2 start]
ORDER BY Ddate DESC
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 2 start]
ORDER BY DDate


BTW - Date is a reserved word in Access. Using it for a field name is a bad
practice. It can lead to bizarre problems. You should consider renaming
that field to SaleDate, TargetDate or something like that.

Randy


"ab" wrote in message
...
So, the overall goal/problem is this:
1. I have a table that contains daily sales data for a company, with the
data history going back 10+ years
2. The "user" will enter in a series of dates (the company end of quarter
dates for Q1, Q2, Q3, and Q4 for all years). Hence, since the DATES in
which
the user enters will always be around 90 days apart, I don't have to worry
about overlapping records within the query results.
3. The GOAL is to determine the TREND of the sales data JUST BEFORE (20
days specifically before) the end of quarter date and JUST AFTER (20 days
AFTER) the end of quarter date specified by the user.

So, you educated me previously on how to select the X records BEFORE the
date(s) the user entered by using the "TOP" within the query and "UNION"
to
combine the numerous queries. Now, I'm trying to find the 20 records
(each
record being one business day of data) AFTER each of the END OF QUARTER
dates
that the user will enter.

Make sense ? Thanks again for your help and patience!


"Randy Harris" wrote:

Let me see if I've got this straight. You want the 20 records before
AND
the 20 records after, each of two different dates? A total return of 80
records providing the dates are more than 20 days apart? What about if
the
dates are 10 days apart - 50 records? Or still 80 records with some
duplicates?



"ab" wrote in message
...
Hi Randy. Thanks again for your help, as I'm obviously a new access
user.
Yes, I'm trying to find the 20 records BEFORE a date, and 20 records
after. I
assumed that if I could solve the problem on how to find the 20
records
prior
to the DATE(s) the user entered, I could figure out how to find the
"AFTER"
records. Obviously, it wasn't so simple. ;-)

Regarding the "ORDER BY dataset.Date DESC", that was a TYPO. I do
have
both
statements using the ascending argument.

As I said, the query results in generating the CORRECT records for the
FIRST
date the user enters, but for the 2nd date produces the most recent
(datewise) 20 records in the table. So, I get 40 records total, 20 of
which
are what I expected, and 20 are simply the most recent 20 records in
the
table.

Ideas ?

Aaron

"Randy Harris" wrote:


"ab" wrote in message
...
So, NOW I need to select 20 records after each date a userenters.
For
example, if a user enters 1/19/05 and 4/20/05, I need the 20
records
(business days) prior to 1/19/05 AND the 20 records prior to
4/20/05.

I'm a bit confused. This seems contradictory. "20 records after",
"20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough to
provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for only
the
FIRST
select query, while the SECOND select query simply pulls the MOST
RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the records
"after"
the entered date, then both parts of the Union need to be sorted
ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to enter
TWO
dates
and
produce the 15 records prior to each of the dates. For
example,
if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15
records
prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do this
???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed number of
days or
months between or something like that) you would probably need
to
use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc

Still no success on finding the records AFTER the date the user enters. The
query successfully finds the 20 records after the FIRST DATE entered, but all
of the other records for the subsequent dates entered don't make any sense.
I'm losing it.....

"Randy Harris" wrote:

You had it pretty much right before. You'll have to do multiple unions. To
get the 20 days before, select top 20 where date = date entered, then order
descending. To get the 20 days after, select top 20 where date = date
entered, then order ascending.


SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate DESC
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDATE = [date 2 start]
ORDER BY Ddate DESC
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 2 start]
ORDER BY DDate


BTW - Date is a reserved word in Access. Using it for a field name is a bad
practice. It can lead to bizarre problems. You should consider renaming
that field to SaleDate, TargetDate or something like that.

Randy


"ab" wrote in message
...
So, the overall goal/problem is this:
1. I have a table that contains daily sales data for a company, with the
data history going back 10+ years
2. The "user" will enter in a series of dates (the company end of quarter
dates for Q1, Q2, Q3, and Q4 for all years). Hence, since the DATES in
which
the user enters will always be around 90 days apart, I don't have to worry
about overlapping records within the query results.
3. The GOAL is to determine the TREND of the sales data JUST BEFORE (20
days specifically before) the end of quarter date and JUST AFTER (20 days
AFTER) the end of quarter date specified by the user.

So, you educated me previously on how to select the X records BEFORE the
date(s) the user entered by using the "TOP" within the query and "UNION"
to
combine the numerous queries. Now, I'm trying to find the 20 records
(each
record being one business day of data) AFTER each of the END OF QUARTER
dates
that the user will enter.

Make sense ? Thanks again for your help and patience!


"Randy Harris" wrote:

Let me see if I've got this straight. You want the 20 records before
AND
the 20 records after, each of two different dates? A total return of 80
records providing the dates are more than 20 days apart? What about if
the
dates are 10 days apart - 50 records? Or still 80 records with some
duplicates?



"ab" wrote in message
...
Hi Randy. Thanks again for your help, as I'm obviously a new access
user.
Yes, I'm trying to find the 20 records BEFORE a date, and 20 records
after. I
assumed that if I could solve the problem on how to find the 20
records
prior
to the DATE(s) the user entered, I could figure out how to find the
"AFTER"
records. Obviously, it wasn't so simple. ;-)

Regarding the "ORDER BY dataset.Date DESC", that was a TYPO. I do
have
both
statements using the ascending argument.

As I said, the query results in generating the CORRECT records for the
FIRST
date the user enters, but for the 2nd date produces the most recent
(datewise) 20 records in the table. So, I get 40 records total, 20 of
which
are what I expected, and 20 are simply the most recent 20 records in
the
table.

Ideas ?

Aaron

"Randy Harris" wrote:


"ab" wrote in message
...
So, NOW I need to select 20 records after each date a userenters.
For
example, if a user enters 1/19/05 and 4/20/05, I need the 20
records
(business days) prior to 1/19/05 AND the 20 records prior to
4/20/05.

I'm a bit confused. This seems contradictory. "20 records after",
"20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough to
provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for only
the
FIRST
select query, while the SECOND select query simply pulls the MOST
RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the records
"after"
the entered date, then both parts of the Union need to be sorted
ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to enter
TWO
dates
and
produce the 15 records prior to each of the dates. For
example,
if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15
records
prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do this
???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed number of
days or
months between or something like that) you would probably need
to
use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc

You're confusing me again. You say "Still no success on finding the records
AFTER the date the user enters" then "The query successfully finds the 20
records after the FIRST DATE entered"???

Try grouping the subqueries:

(SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate DESC)
UNION
(SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate)
UNION
(SELECT TOP 20 *
FROM dataset
WHERE DDATE = [date 2 start]
ORDER BY Ddate DESC)
UNION
(SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 2 start]
ORDER BY DDate)

If this doesn't work, try running the subqueries individually. You are
saying that some (at least one) is working. Which ones are and which ones
aren't working?

"new2access" wrote in message
...
Still no success on finding the records AFTER the date the user enters.
The
query successfully finds the 20 records after the FIRST DATE entered, but
all
of the other records for the subsequent dates entered don't make any
sense.
I'm losing it.....

"Randy Harris" wrote:

You had it pretty much right before. You'll have to do multiple unions.
To
get the 20 days before, select top 20 where date = date entered, then
order
descending. To get the 20 days after, select top 20 where date = date
entered, then order ascending.


SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate DESC
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDATE = [date 2 start]
ORDER BY Ddate DESC
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 2 start]
ORDER BY DDate


BTW - Date is a reserved word in Access. Using it for a field name is a
bad
practice. It can lead to bizarre problems. You should consider
renaming
that field to SaleDate, TargetDate or something like that.

Randy


"ab" wrote in message
...
So, the overall goal/problem is this:
1. I have a table that contains daily sales data for a company, with
the
data history going back 10+ years
2. The "user" will enter in a series of dates (the company end of
quarter
dates for Q1, Q2, Q3, and Q4 for all years). Hence, since the DATES
in
which
the user enters will always be around 90 days apart, I don't have to
worry
about overlapping records within the query results.
3. The GOAL is to determine the TREND of the sales data JUST BEFORE
(20
days specifically before) the end of quarter date and JUST AFTER (20
days
AFTER) the end of quarter date specified by the user.

So, you educated me previously on how to select the X records BEFORE
the
date(s) the user entered by using the "TOP" within the query and
"UNION"
to
combine the numerous queries. Now, I'm trying to find the 20 records
(each
record being one business day of data) AFTER each of the END OF
QUARTER
dates
that the user will enter.

Make sense ? Thanks again for your help and patience!


"Randy Harris" wrote:

Let me see if I've got this straight. You want the 20 records
before
AND
the 20 records after, each of two different dates? A total return
of 80
records providing the dates are more than 20 days apart? What about
if
the
dates are 10 days apart - 50 records? Or still 80 records with some
duplicates?



"ab" wrote in message
...
Hi Randy. Thanks again for your help, as I'm obviously a new
access
user.
Yes, I'm trying to find the 20 records BEFORE a date, and 20
records
after. I
assumed that if I could solve the problem on how to find the 20
records
prior
to the DATE(s) the user entered, I could figure out how to find
the
"AFTER"
records. Obviously, it wasn't so simple. ;-)

Regarding the "ORDER BY dataset.Date DESC", that was a TYPO. I do
have
both
statements using the ascending argument.

As I said, the query results in generating the CORRECT records for
the
FIRST
date the user enters, but for the 2nd date produces the most
recent
(datewise) 20 records in the table. So, I get 40 records total,
20 of
which
are what I expected, and 20 are simply the most recent 20 records
in
the
table.

Ideas ?

Aaron

"Randy Harris" wrote:


"ab" wrote in message
...
So, NOW I need to select 20 records after each date a
userenters.
For
example, if a user enters 1/19/05 and 4/20/05, I need the 20
records
(business days) prior to 1/19/05 AND the 20 records prior to
4/20/05.

I'm a bit confused. This seems contradictory. "20 records
after",
"20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough
to
provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for
only
the
FIRST
select query, while the SECOND select query simply pulls the
MOST
RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the
records
"after"
the entered date, then both parts of the Union need to be sorted
ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to
enter
TWO
dates
and
produce the 15 records prior to each of the dates. For
example,
if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15
records
prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do
this
???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed
number of
days or
months between or something like that) you would probably
need
to
use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc

That's it!!! Using the ( ) around each of the subqueries solved the problem.
Major lesson learned! THANK YOU SO MUCH FOR YOUR HELP!!!!

"Randy Harris" wrote:

You're confusing me again. You say "Still no success on finding the records
AFTER the date the user enters" then "The query successfully finds the 20
records after the FIRST DATE entered"???

Try grouping the subqueries:

(SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate DESC)
UNION
(SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate)
UNION
(SELECT TOP 20 *
FROM dataset
WHERE DDATE = [date 2 start]
ORDER BY Ddate DESC)
UNION
(SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 2 start]
ORDER BY DDate)

If this doesn't work, try running the subqueries individually. You are
saying that some (at least one) is working. Which ones are and which ones
aren't working?

"new2access" wrote in message
...
Still no success on finding the records AFTER the date the user enters.
The
query successfully finds the 20 records after the FIRST DATE entered, but
all
of the other records for the subsequent dates entered don't make any
sense.
I'm losing it.....

"Randy Harris" wrote:

You had it pretty much right before. You'll have to do multiple unions.
To
get the 20 days before, select top 20 where date = date entered, then
order
descending. To get the 20 days after, select top 20 where date = date
entered, then order ascending.


SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate DESC
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 1 start]
ORDER BY DDate
UNION
SELECT TOP 20 *
FROM dataset
WHERE DDATE = [date 2 start]
ORDER BY Ddate DESC
SELECT TOP 20 *
FROM dataset
WHERE DDate = [date 2 start]
ORDER BY DDate


BTW - Date is a reserved word in Access. Using it for a field name is a
bad
practice. It can lead to bizarre problems. You should consider
renaming
that field to SaleDate, TargetDate or something like that.

Randy


"ab" wrote in message
...
So, the overall goal/problem is this:
1. I have a table that contains daily sales data for a company, with
the
data history going back 10+ years
2. The "user" will enter in a series of dates (the company end of
quarter
dates for Q1, Q2, Q3, and Q4 for all years). Hence, since the DATES
in
which
the user enters will always be around 90 days apart, I don't have to
worry
about overlapping records within the query results.
3. The GOAL is to determine the TREND of the sales data JUST BEFORE
(20
days specifically before) the end of quarter date and JUST AFTER (20
days
AFTER) the end of quarter date specified by the user.

So, you educated me previously on how to select the X records BEFORE
the
date(s) the user entered by using the "TOP" within the query and
"UNION"
to
combine the numerous queries. Now, I'm trying to find the 20 records
(each
record being one business day of data) AFTER each of the END OF
QUARTER
dates
that the user will enter.

Make sense ? Thanks again for your help and patience!


"Randy Harris" wrote:

Let me see if I've got this straight. You want the 20 records
before
AND
the 20 records after, each of two different dates? A total return
of 80
records providing the dates are more than 20 days apart? What about
if
the
dates are 10 days apart - 50 records? Or still 80 records with some
duplicates?



"ab" wrote in message
...
Hi Randy. Thanks again for your help, as I'm obviously a new
access
user.
Yes, I'm trying to find the 20 records BEFORE a date, and 20
records
after. I
assumed that if I could solve the problem on how to find the 20
records
prior
to the DATE(s) the user entered, I could figure out how to find
the
"AFTER"
records. Obviously, it wasn't so simple. ;-)

Regarding the "ORDER BY dataset.Date DESC", that was a TYPO. I do
have
both
statements using the ascending argument.

As I said, the query results in generating the CORRECT records for
the
FIRST
date the user enters, but for the 2nd date produces the most
recent
(datewise) 20 records in the table. So, I get 40 records total,
20 of
which
are what I expected, and 20 are simply the most recent 20 records
in
the
table.

Ideas ?

Aaron

"Randy Harris" wrote:


"ab" wrote in message
...
So, NOW I need to select 20 records after each date a
userenters.
For
example, if a user enters 1/19/05 and 4/20/05, I need the 20
records
(business days) prior to 1/19/05 AND the 20 records prior to
4/20/05.

I'm a bit confused. This seems contradictory. "20 records
after",
"20
records prior"? Do you need both before and after records?

I had assumed that the "opposite" of what you were kind enough
to
provide
below would work, OR essentially changing the following:

(1) change the LESS THAN to GREATER THAN
(2) change order by to ascending

Here's what I did:
SELECT TOP 20 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 1 start]))
ORDER BY dataset.Date ASC
UNION SELECT TOP 25 dataset.*
FROM dataset
WHERE (((dataset.Date)=[date 2 start]))
ORDER BY dataset.Date DESC


However, when I run the query, the results are accurate for
only
the
FIRST
select query, while the SECOND select query simply pulls the
MOST
RECENT
DATE
records. What am I missing here ?

You didn't change the second Order By. If you want only the
records
"after"
the entered date, then both parts of the Union need to be sorted
ascending.


"Randy Harris" wrote:


"ab" wrote in message
...
Worked like a charm!

However, now I can't figure out how to allow the user to
enter
TWO
dates
and
produce the 15 records prior to each of the dates. For
example,
if
the
user
enters 5/31/05 and 1/31/05, I'd want to return the 15
records
prior
to
5/31/05 and the 15 records prior to 1/31/05. Can I do
this
???

Ideas? Thanks again!


If there is no relationship between the dates (a fixed
number of
days or
months between or something like that) you would probably
need
to
use a
union query.

Select TOP 16 * from table
where StartDate = TargetDate1
Order By Start Desc
UNION
Select TOP 16 * from table
where StartDate = TargetDate2
Order By Start Desc