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

[Guest]

I have a table of data (.CSV file) that I'm trying to use Access 2003 to
analyze. Each record within the table is essentially a business day
(Monday-Friday) plus various data associated with that particular day. What
I'm trying to do is allow the user to enter a START DATE, and use an Access
query to generate all of the records from the START DATE to 15 BUSINESS days
(not 15 calendar days) prior to the START DATE entered by the user.

I know how to prompt the user to enter the START DATE within the query.
What I don't know how to do is get Access to select/find the 15 records
(business days) prior to the entered START DATE.

Thanks for any help on this one !!

 

[Duane Hookom]

Aren't 15 business days the same as 21 calendar days? Try create a form with
a text box (frmYourFrom!txtStartDate) and use:

Between Forms!frmYourForm!txtStartDate and DateAdd("d", 21,
Forms!frmYourForm!txtStartDate)

 

[Guest]

Duane,

THANKS for the quick response. Unfortunately that will not work. Consider
the fact that the date in which the user enters may be ANYWHERE within the
week, so it won't always be 21 calendar days. I want to select ONLY the 15
business days (records) prior to the date the user enters.

So, I wanted to see if there was some mechanism to select the previous 15
records of the results of a query.....or some other creative way to solve
this problem.

Now, you may think this is a rather simplistic problem to solve, and may
wonder why I can't just look at a calendar or have the user do so....but I'm
actually allowing the user to enter NUMEROUS (as in 50) dates, and want to
analyze the TREND of certain data for the 15 business days prior to that
date. (Just trying to expand on the problem definition a bit...)

Ideas? THANKS again for the help!!!!

 

[Randy Harris]

I have a table of data (.CSV file) that I'm trying to use Access 2003 to
analyze. Each record within the table is essentially a business day
(Monday-Friday) plus various data associated with that particular day. What
I'm trying to do is allow the user to enter a START DATE, and use an Access
query to generate all of the records from the START DATE to 15 BUSINESS days
(not 15 calendar days) prior to the START DATE entered by the user.

I know how to prompt the user to enter the START DATE within the query.
What I don't know how to do is get Access to select/find the 15 records
(business days) prior to the entered START DATE.

Thanks for any help on this one !!

If the table contains only the business days, 1 record per day, how about
using the TOP predicate? Something like:

Select TOP 16 * from table
where StartDate <= TargetDate
Order By Start Desc

That should return the Date specified by the user plus the 15 previous days.

 

[Duane Hookom]

Is there only one record per date? If so, you can use a TOP 15 query
SELECT TOP 15 ....
FROM tblSomeTable
WHERE [SomeDate]>=Forms!frmYourForm!txtStartDate
ORDER BY [SomeDate];

 

[Guest]

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!

 

[Randy Harris]

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

 

[Randy Harris]

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

Upon reflection... I've never used two Order By clauses in a query like
this. You might have to group the queries with parentheses. Not sure.

 

[Guest]

That's it!!! Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Guest]

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 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 ?

 

[Randy Harris]

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.

 

[Guest]

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

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]

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?

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

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.

:


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

 

[Guest]

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!

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?

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

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.


:


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

 

[Randy Harris]

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

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!

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?

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

:


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.


:


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

 

[Guest]

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.....

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

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!

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?



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

:


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.


:


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

 

[Randy Harris]

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?

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.....

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

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!


:

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?



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

:


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.


:


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

 

[Guest]

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

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?

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.....

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


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!


:

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?



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

:


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.


:


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