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

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

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)

--
Duane Hookom
MS Access MVP


"ab" wrote in message
...
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,

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

"Duane Hookom" wrote:

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)

--
Duane Hookom
MS Access MVP


"ab" wrote in message
...
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 !!

"ab" wrote in message
...
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.

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];

--
Duane Hookom
MS Access MVP


"ab" wrote in message
...
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!!!!

"Duane Hookom" wrote:

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)

--
Duane Hookom
MS Access MVP


"ab" wrote in message
...
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 !!

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" wrote:


"ab" wrote in message
...
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.

"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

"Randy Harris" wrote in message
...

"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

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.

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

"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, 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" 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