using a "for" loop in a query to reference data from another table

[Guest]

Suppose I have a table of data (one record per business day) that I wish to
run queries against. I wish to use the data from ANOTHER table to provide
input to the query Example: I have a table (call it "revenue") that contains
daily sales revenue. Each record in the table has the business date and
daily revenue for that date. I want to create a simple table of dates (call
it "searchdates") that will used as INPUT to queries against the revenue
database. (For the sake of this topic/question, I am simplifying the actual
set of data that I'm working with.)

What I'm trying to understand is this: Can I create a query such that I can
use the dates within the "searchdates" table as INPUT to a query agains the
"revenue" table ? Is it possible to use the concept of "For....next"
statements within a query ?

For example, I currently use a query to select the 20 days of revenue prior
to a specified date the user enters (usually an end of quarter date), and
allow the user to enter up to 4 dates, as follows:

SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 1]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 2]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 3]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 4]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 5]))
ORDER BY revenue.Date DESC

What I WANT to do is have this automated such that the query references a
table that has these "user entry" dates. Thus, I had hoped to use some
mechanism to cycle through each record in the "searchdates" table, use THAT
date as the [date X] inputs used above in my original query.....then cycle
through the "searchdates" file until no more records are in the "searchdates"
table.

I had seem some examples of using "for...next" loops within Access, but
wasn't sure whether I could use them here. OR, any other solution to allow
me to generate a query using another table as input......

Please help!!

 

[Tom Ellison]

Dear New2:

Adapting your thinking to 4GL programming from sequential thinking can be
difficult. I sympathize. But that won't really help you.

The logic of your query and the descriptive text do not match. I do not see
where your code implements the "20 days of revenue" feature.

Let's try to get just one portion of the UNION working correctly first.

SELECT TOP 25 *
FROM revenue
WHERE Date <= [date 1]

I suggest that the 20 days feature would look like:

WHERE Date BETWEEN DATEADD("d", -20, [date 1]) AND [date 1]

Please let me know if this helped, and whether I can be of further
assistance.

Tom Ellison

Suppose I have a table of data (one record per business day) that I wish to
run queries against. I wish to use the data from ANOTHER table to provide
input to the query Example: I have a table (call it "revenue") that contains
daily sales revenue. Each record in the table has the business date and
daily revenue for that date. I want to create a simple table of dates (call
it "searchdates") that will used as INPUT to queries against the revenue
database. (For the sake of this topic/question, I am simplifying the actual
set of data that I'm working with.)

What I'm trying to understand is this: Can I create a query such that I can
use the dates within the "searchdates" table as INPUT to a query agains the
"revenue" table ? Is it possible to use the concept of "For....next"
statements within a query ?

For example, I currently use a query to select the 20 days of revenue prior
to a specified date the user enters (usually an end of quarter date), and
allow the user to enter up to 4 dates, as follows:

SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 1]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 2]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 3]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 4]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 5]))
ORDER BY revenue.Date DESC

What I WANT to do is have this automated such that the query references a
table that has these "user entry" dates. Thus, I had hoped to use some
mechanism to cycle through each record in the "searchdates" table, use THAT
date as the [date X] inputs used above in my original query.....then cycle
through the "searchdates" file until no more records are in the "searchdates"
table.

I had seem some examples of using "for...next" loops within Access, but
wasn't sure whether I could use them here. OR, any other solution to allow
me to generate a query using another table as input......

Please help!!

 

[Guest]

Tom

Thanks for your response. My subqueries to search/select for the 25 records
prior to the entered date work just fine. (I meant to say 25 records, not 20
as suggested in my initial post of this topic.) That's not what I'm trying
to figure out how to do....however, I like your suggestion. What I'm trying
to do is ELIMINATE THE USER ENTRY portion of the query. I tried to simplify
my example for sake of simplicity in asking the question. The real query
involves up to taking 28 different dates. Each date the user enters
represents a request for the 25 business day sales numbers (records) prior to
each date the user enters....for a total of 28 x 25 = 700 total records being
generated.

However, what I've found is that when the user (me, for example!) enters the
28 dates, HUMAN ERROR occurs. So, I want to eliminate human error from this,
and simply have another table (or excel file) provide the input to the query.

So, I want to find out how to use an "external" table (external to my PRICE
table, that is) that holds up to 28 dates to provide the INPUT to the query
that I describe below.

Ideas????

Dear New2:

Adapting your thinking to 4GL programming from sequential thinking can be
difficult. I sympathize. But that won't really help you.

The logic of your query and the descriptive text do not match. I do not see
where your code implements the "20 days of revenue" feature.

Let's try to get just one portion of the UNION working correctly first.

SELECT TOP 25 *
FROM revenue
WHERE Date <= [date 1]

I suggest that the 20 days feature would look like:

WHERE Date BETWEEN DATEADD("d", -20, [date 1]) AND [date 1]

Please let me know if this helped, and whether I can be of further
assistance.

Tom Ellison

Suppose I have a table of data (one record per business day) that I wish to
run queries against. I wish to use the data from ANOTHER table to provide
input to the query Example: I have a table (call it "revenue") that contains
daily sales revenue. Each record in the table has the business date and
daily revenue for that date. I want to create a simple table of dates (call
it "searchdates") that will used as INPUT to queries against the revenue
database. (For the sake of this topic/question, I am simplifying the actual
set of data that I'm working with.)

What I'm trying to understand is this: Can I create a query such that I can
use the dates within the "searchdates" table as INPUT to a query agains the
"revenue" table ? Is it possible to use the concept of "For....next"
statements within a query ?

For example, I currently use a query to select the 20 days of revenue prior
to a specified date the user enters (usually an end of quarter date), and
allow the user to enter up to 4 dates, as follows:

SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 1]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 2]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 3]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 4]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 5]))
ORDER BY revenue.Date DESC

What I WANT to do is have this automated such that the query references a
table that has these "user entry" dates. Thus, I had hoped to use some
mechanism to cycle through each record in the "searchdates" table, use THAT
date as the [date X] inputs used above in my original query.....then cycle
through the "searchdates" file until no more records are in the "searchdates"
table.

I had seem some examples of using "for...next" loops within Access, but
wasn't sure whether I could use them here. OR, any other solution to allow
me to generate a query using another table as input......

Please help!!

 

[Randy Harris]

You're not going to be able to do this as a saved query. The query will
need to be built in code.

Tom

Thanks for your response. My subqueries to search/select for the 25 records
prior to the entered date work just fine. (I meant to say 25 records, not 20
as suggested in my initial post of this topic.) That's not what I'm trying
to figure out how to do....however, I like your suggestion. What I'm trying
to do is ELIMINATE THE USER ENTRY portion of the query. I tried to simplify
my example for sake of simplicity in asking the question. The real query
involves up to taking 28 different dates. Each date the user enters
represents a request for the 25 business day sales numbers (records) prior to
each date the user enters....for a total of 28 x 25 = 700 total records being
generated.

However, what I've found is that when the user (me, for example!) enters the
28 dates, HUMAN ERROR occurs. So, I want to eliminate human error from this,
and simply have another table (or excel file) provide the input to the query.

So, I want to find out how to use an "external" table (external to my PRICE
table, that is) that holds up to 28 dates to provide the INPUT to the query
that I describe below.

Ideas????

Dear New2:

Adapting your thinking to 4GL programming from sequential thinking can be
difficult. I sympathize. But that won't really help you.

The logic of your query and the descriptive text do not match. I do not see
where your code implements the "20 days of revenue" feature.

Let's try to get just one portion of the UNION working correctly first.

SELECT TOP 25 *
FROM revenue
WHERE Date <= [date 1]

I suggest that the 20 days feature would look like:

WHERE Date BETWEEN DATEADD("d", -20, [date 1]) AND [date 1]

Please let me know if this helped, and whether I can be of further
assistance.

Tom Ellison

Suppose I have a table of data (one record per business day) that I

wish
to

run queries against. I wish to use the data from ANOTHER table to provide
input to the query Example: I have a table (call it "revenue") that contains
daily sales revenue. Each record in the table has the business date and
daily revenue for that date. I want to create a simple table of

dates
(call

it "searchdates") that will used as INPUT to queries against the revenue
database. (For the sake of this topic/question, I am simplifying the actual
set of data that I'm working with.)

What I'm trying to understand is this: Can I create a query such that

I
can

use the dates within the "searchdates" table as INPUT to a query

agains
the

"revenue" table ? Is it possible to use the concept of "For....next"
statements within a query ?

For example, I currently use a query to select the 20 days of revenue prior
to a specified date the user enters (usually an end of quarter date), and
allow the user to enter up to 4 dates, as follows:

SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 1]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 2]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 3]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 4]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 5]))
ORDER BY revenue.Date DESC

What I WANT to do is have this automated such that the query references a
table that has these "user entry" dates. Thus, I had hoped to use some
mechanism to cycle through each record in the "searchdates" table, use THAT
date as the [date X] inputs used above in my original query.....then cycle
through the "searchdates" file until no more records are in the "searchdates"
table.

I had seem some examples of using "for...next" loops within Access, but
wasn't sure whether I could use them here. OR, any other solution to allow
me to generate a query using another table as input......

Please help!!

 

[Tom Ellison]

Dear New2:

You can put 28 dates in a table and use them to do this. What I don't get
is this: do these 28 days ever change? If so, how do you intend to create
new sets of dates and how do you want to associate the correct set with each
sale? Some indication of a design in this respect is critical to me being
able to make any recommendation.

Tom Ellison

Tom

Thanks for your response. My subqueries to search/select for the 25 records
prior to the entered date work just fine. (I meant to say 25 records, not 20
as suggested in my initial post of this topic.) That's not what I'm trying
to figure out how to do....however, I like your suggestion. What I'm trying
to do is ELIMINATE THE USER ENTRY portion of the query. I tried to simplify
my example for sake of simplicity in asking the question. The real query
involves up to taking 28 different dates. Each date the user enters
represents a request for the 25 business day sales numbers (records) prior to
each date the user enters....for a total of 28 x 25 = 700 total records being
generated.

However, what I've found is that when the user (me, for example!) enters the
28 dates, HUMAN ERROR occurs. So, I want to eliminate human error from this,
and simply have another table (or excel file) provide the input to the query.

So, I want to find out how to use an "external" table (external to my PRICE
table, that is) that holds up to 28 dates to provide the INPUT to the query
that I describe below.

Ideas????

Dear New2:

Adapting your thinking to 4GL programming from sequential thinking can be
difficult. I sympathize. But that won't really help you.

The logic of your query and the descriptive text do not match. I do not see
where your code implements the "20 days of revenue" feature.

Let's try to get just one portion of the UNION working correctly first.

SELECT TOP 25 *
FROM revenue
WHERE Date <= [date 1]

I suggest that the 20 days feature would look like:

WHERE Date BETWEEN DATEADD("d", -20, [date 1]) AND [date 1]

Please let me know if this helped, and whether I can be of further
assistance.

Tom Ellison

Suppose I have a table of data (one record per business day) that I

wish
to

run queries against. I wish to use the data from ANOTHER table to provide
input to the query Example: I have a table (call it "revenue") that contains
daily sales revenue. Each record in the table has the business date and
daily revenue for that date. I want to create a simple table of

dates
(call

it "searchdates") that will used as INPUT to queries against the revenue
database. (For the sake of this topic/question, I am simplifying the actual
set of data that I'm working with.)

What I'm trying to understand is this: Can I create a query such that

I
can

use the dates within the "searchdates" table as INPUT to a query

agains
the

"revenue" table ? Is it possible to use the concept of "For....next"
statements within a query ?

For example, I currently use a query to select the 20 days of revenue prior
to a specified date the user enters (usually an end of quarter date), and
allow the user to enter up to 4 dates, as follows:

SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 1]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 2]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 3]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 4]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 5]))
ORDER BY revenue.Date DESC

What I WANT to do is have this automated such that the query references a
table that has these "user entry" dates. Thus, I had hoped to use some
mechanism to cycle through each record in the "searchdates" table, use THAT
date as the [date X] inputs used above in my original query.....then cycle
through the "searchdates" file until no more records are in the "searchdates"
table.

I had seem some examples of using "for...next" loops within Access, but
wasn't sure whether I could use them here. OR, any other solution to allow
me to generate a query using another table as input......

Please help!!

 

[Guest]

Tom,

Thanks for your reply. Yes, the 28 days will change, depending on the dates
which the user chooses. Each user will be required to submit a text file or
excel file containing the dates they wish to get data on. The data goes back
more than 25 years, and the users can pick any business date within the past
25 years, and (for each date they request) will receive the 25 business days
of sales PRIOR TO each date requested.

Once they submit the list of requested dates, I would simply import this
into access and create a new table....or whatever I need/should do to
accomplish the goal of (a) not having users directly enter the data and (b)
ME not having to input this data manually.

Sorry for the confusing goal, but I'm new to access and desperate to get
this project moving....

Thanks for your help!!

Dear New2:

You can put 28 dates in a table and use them to do this. What I don't get
is this: do these 28 days ever change? If so, how do you intend to create
new sets of dates and how do you want to associate the correct set with each
sale? Some indication of a design in this respect is critical to me being
able to make any recommendation.

Tom Ellison

Tom

Thanks for your response. My subqueries to search/select for the 25 records
prior to the entered date work just fine. (I meant to say 25 records, not 20
as suggested in my initial post of this topic.) That's not what I'm trying
to figure out how to do....however, I like your suggestion. What I'm trying
to do is ELIMINATE THE USER ENTRY portion of the query. I tried to simplify
my example for sake of simplicity in asking the question. The real query
involves up to taking 28 different dates. Each date the user enters
represents a request for the 25 business day sales numbers (records) prior to
each date the user enters....for a total of 28 x 25 = 700 total records being
generated.

However, what I've found is that when the user (me, for example!) enters the
28 dates, HUMAN ERROR occurs. So, I want to eliminate human error from this,
and simply have another table (or excel file) provide the input to the query.

So, I want to find out how to use an "external" table (external to my PRICE
table, that is) that holds up to 28 dates to provide the INPUT to the query
that I describe below.

Ideas????

Dear New2:

Adapting your thinking to 4GL programming from sequential thinking can be
difficult. I sympathize. But that won't really help you.

The logic of your query and the descriptive text do not match. I do not see
where your code implements the "20 days of revenue" feature.

Let's try to get just one portion of the UNION working correctly first.

SELECT TOP 25 *
FROM revenue
WHERE Date <= [date 1]

I suggest that the 20 days feature would look like:

WHERE Date BETWEEN DATEADD("d", -20, [date 1]) AND [date 1]

Please let me know if this helped, and whether I can be of further
assistance.

Tom Ellison


Suppose I have a table of data (one record per business day) that I wish
to
run queries against. I wish to use the data from ANOTHER table to provide
input to the query Example: I have a table (call it "revenue") that
contains
daily sales revenue. Each record in the table has the business date and
daily revenue for that date. I want to create a simple table of dates
(call
it "searchdates") that will used as INPUT to queries against the revenue
database. (For the sake of this topic/question, I am simplifying the
actual
set of data that I'm working with.)

What I'm trying to understand is this: Can I create a query such that I
can
use the dates within the "searchdates" table as INPUT to a query agains
the
"revenue" table ? Is it possible to use the concept of "For....next"
statements within a query ?

For example, I currently use a query to select the 20 days of revenue
prior
to a specified date the user enters (usually an end of quarter date), and
allow the user to enter up to 4 dates, as follows:

SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 1]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 2]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 3]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 4]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 5]))
ORDER BY revenue.Date DESC

What I WANT to do is have this automated such that the query references a
table that has these "user entry" dates. Thus, I had hoped to use some
mechanism to cycle through each record in the "searchdates" table, use
THAT
date as the [date X] inputs used above in my original query.....then cycle
through the "searchdates" file until no more records are in the
"searchdates"
table.

I had seem some examples of using "for...next" loops within Access, but
wasn't sure whether I could use them here. OR, any other solution to
allow
me to generate a query using another table as input......

Please help!!

 

[Tom Ellison]

Drst New2:

I suggest each set of (up to?) 28 days should be labelled, that is, uniquely
named. The user could then choose one of these unique names and thereby
select the set of dates. Does that sound at all appealing? Wouldn't you
need to be able to keep several such sets of dates simultaneously available.

Is there any rhyme or reason to this set of dates? Is it possible there
might be one or more sets of intervals, then generating the sets of dates
from one date plus a selected set of intervals? Just a suspicion I have
that this might be something that could be systematic. Anything that is
systematic should usually be represented by software that expoits that
systematic nature. Often, this can avoid errors and save time.

Just a thought.

Tom Ellison

Tom,

Thanks for your reply. Yes, the 28 days will change, depending on the dates
which the user chooses. Each user will be required to submit a text file or
excel file containing the dates they wish to get data on. The data goes back
more than 25 years, and the users can pick any business date within the past
25 years, and (for each date they request) will receive the 25 business days
of sales PRIOR TO each date requested.

Once they submit the list of requested dates, I would simply import this
into access and create a new table....or whatever I need/should do to
accomplish the goal of (a) not having users directly enter the data and (b)
ME not having to input this data manually.

Sorry for the confusing goal, but I'm new to access and desperate to get
this project moving....

Thanks for your help!!

Dear New2:

You can put 28 dates in a table and use them to do this. What I don't get
is this: do these 28 days ever change? If so, how do you intend to create
new sets of dates and how do you want to associate the correct set with each
sale? Some indication of a design in this respect is critical to me being
able to make any recommendation.

Tom Ellison

Tom

Thanks for your response. My subqueries to search/select for the 25 records
prior to the entered date work just fine. (I meant to say 25 records,

not
20

as suggested in my initial post of this topic.) That's not what I'm trying
to figure out how to do....however, I like your suggestion. What I'm trying
to do is ELIMINATE THE USER ENTRY portion of the query. I tried to simplify
my example for sake of simplicity in asking the question. The real query
involves up to taking 28 different dates. Each date the user enters
represents a request for the 25 business day sales numbers (records)

prior
to

each date the user enters....for a total of 28 x 25 = 700 total

records
being

generated.

However, what I've found is that when the user (me, for example!)

enters
the

28 dates, HUMAN ERROR occurs. So, I want to eliminate human error

from
this,

and simply have another table (or excel file) provide the input to the query.

So, I want to find out how to use an "external" table (external to my PRICE
table, that is) that holds up to 28 dates to provide the INPUT to the query
that I describe below.

Ideas????

:

Dear New2:

Adapting your thinking to 4GL programming from sequential thinking

can
be

difficult. I sympathize. But that won't really help you.

The logic of your query and the descriptive text do not match. I do

not
see

where your code implements the "20 days of revenue" feature.

Let's try to get just one portion of the UNION working correctly first.

SELECT TOP 25 *
FROM revenue
WHERE Date <= [date 1]

I suggest that the 20 days feature would look like:

WHERE Date BETWEEN DATEADD("d", -20, [date 1]) AND [date 1]

Please let me know if this helped, and whether I can be of further
assistance.

Tom Ellison


Suppose I have a table of data (one record per business day) that

I
wish

to
run queries against. I wish to use the data from ANOTHER table to provide
input to the query Example: I have a table (call it "revenue") that
contains
daily sales revenue. Each record in the table has the business

date
and

daily revenue for that date. I want to create a simple table of dates
(call
it "searchdates") that will used as INPUT to queries against the revenue
database. (For the sake of this topic/question, I am simplifying the
actual
set of data that I'm working with.)

What I'm trying to understand is this: Can I create a query such

that
I

can
use the dates within the "searchdates" table as INPUT to a query agains
the
"revenue" table ? Is it possible to use the concept of "For....next"
statements within a query ?

For example, I currently use a query to select the 20 days of revenue
prior
to a specified date the user enters (usually an end of quarter

date),
and

allow the user to enter up to 4 dates, as follows:

SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 1]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 2]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 3]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 4]))
ORDER BY revenue.Date DESC
UNION SELECT TOP 25 revenue.*
FROM revenue
WHERE (((revenue.Date)<=[date 5]))
ORDER BY revenue.Date DESC

What I WANT to do is have this automated such that the query references a
table that has these "user entry" dates. Thus, I had hoped to use some
mechanism to cycle through each record in the "searchdates" table, use
THAT
date as the [date X] inputs used above in my original

query.....then
cycle

through the "searchdates" file until no more records are in the
"searchdates"
table.

I had seem some examples of using "for...next" loops within

Access,
but

wasn't sure whether I could use them here. OR, any other solution to
allow
me to generate a query using another table as input......

Please help!!

 

[Vincent Johns]

Tom,

Thanks for your reply. Yes, the 28 days will change, depending on the dates
which the user chooses. Each user will be required to submit a text file or
excel file containing the dates they wish to get data on. The data goes back
more than 25 years, and the users can pick any business date within the past
25 years, and (for each date they request) will receive the 25 business days
of sales PRIOR TO each date requested.

This was not my original impression of what you wanted; in your first
SQL, you'd asked for TOP 25, instead of for a 25-day date range. Date
range is easier. (I came up with a way to do TOP 25, but it was clumsy,
and I concluded, with Randy Harris, that VBA code would be cleaner &
easier. But it appears that that's immaterial here.)

Incidentally, TOP 25 doesn't guarantee 25 records. There might be fewer
than that in the dataset, or there might be a tie for 25th place, in
which case you'd get all the matching ones, perhaps including the entire
dataset.

Once they submit the list of requested dates, I would simply import this
into access and create a new table....or whatever I need/should do to
accomplish the goal of (a) not having users directly enter the data and (b)
ME not having to input this data manually.

I think I have a way to do what you (now) say you want. This assumes
that you will import a list of dates into a Table called [RevenueDate]
and name that field [DateEnded].

Sorry for the confusing goal, but I'm new to access and desperate to get
this project moving....

Thanks for your help!!

Let's assume that your [Revenue] Table contains these records (possibly
with other fields besides the two shown here):

Date Amount
--------- ---------
8/25/2004 $68.50
9/12/2004 $172.00
2/3/2005 $1,228.74
9/25/2004 $666.76
9/10/2005 $853.53

Let's assume that you import into Table [RevenueDate] the dates ended of
the 25-day periods that interest you, for example:

DateEnded
---------
3/31/2005
9/15/2004
9/30/2004
6/30/2004
9/30/2005

Then the following Query:

SELECT DISTINCT revenue.*
FROM Revenue, RevenueDate
WHERE ((([Revenue].[Date])<[RevenueDate]![DateEnded]
And ([Revenue].[Date])>=[RevenueDate]![DateEnded]-25))
ORDER BY [Revenue].[Date] DESC;

will produce the following output. Note that not all of the [Revenue]
data, such as 2/3/2005, appear, since they are not all within the
specified date ranges; and that some, such as 9/12/2004, which appear in
two or more overlapping ranges are listed here only once. Any other
fields in [Revenue] would also be included here; to omit some, you'd
need to specify those you wanted individually instead of using "Revenue.*" :

Date Amount
--------- -------
9/10/2005 $853.53
9/25/2004 $666.76
9/12/2004 $172.00
8/25/2004 $68.50

Since you said "PRIOR TO", records matching the specified date will be
omitted (unless they're covered by some other range). I didn't include
that in my example here, but you can play around with the Query to see
if it works as you'd like it to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Vincent,

THANKS for your response! Again, my current queries/subqueries work just
fine, but I do appreciate the comments. My goal is to automate the entry
process for the "search dates" so that human error is reduced/eliminated.
Anyway, I'll try out your suggestions below in a couple of hours -- gotta get
my kids going for school now!! THANKS AGAIN!!!

Tom,

Thanks for your reply. Yes, the 28 days will change, depending on the dates
which the user chooses. Each user will be required to submit a text file or
excel file containing the dates they wish to get data on. The data goes back
more than 25 years, and the users can pick any business date within the past
25 years, and (for each date they request) will receive the 25 business days
of sales PRIOR TO each date requested.

This was not my original impression of what you wanted; in your first
SQL, you'd asked for TOP 25, instead of for a 25-day date range. Date
range is easier. (I came up with a way to do TOP 25, but it was clumsy,
and I concluded, with Randy Harris, that VBA code would be cleaner &
easier. But it appears that that's immaterial here.)

Incidentally, TOP 25 doesn't guarantee 25 records. There might be fewer
than that in the dataset, or there might be a tie for 25th place, in
which case you'd get all the matching ones, perhaps including the entire
dataset.

Once they submit the list of requested dates, I would simply import this
into access and create a new table....or whatever I need/should do to
accomplish the goal of (a) not having users directly enter the data and (b)
ME not having to input this data manually.

I think I have a way to do what you (now) say you want. This assumes
that you will import a list of dates into a Table called [RevenueDate]
and name that field [DateEnded].

Sorry for the confusing goal, but I'm new to access and desperate to get
this project moving....

Thanks for your help!!

Let's assume that your [Revenue] Table contains these records (possibly
with other fields besides the two shown here):

Date Amount
--------- ---------
8/25/2004 $68.50
9/12/2004 $172.00
2/3/2005 $1,228.74
9/25/2004 $666.76
9/10/2005 $853.53

Let's assume that you import into Table [RevenueDate] the dates ended of
the 25-day periods that interest you, for example:

DateEnded
---------
3/31/2005
9/15/2004
9/30/2004
6/30/2004
9/30/2005

Then the following Query:

SELECT DISTINCT revenue.*
FROM Revenue, RevenueDate
WHERE ((([Revenue].[Date])<[RevenueDate]![DateEnded]
And ([Revenue].[Date])>=[RevenueDate]![DateEnded]-25))
ORDER BY [Revenue].[Date] DESC;

will produce the following output. Note that not all of the [Revenue]
data, such as 2/3/2005, appear, since they are not all within the
specified date ranges; and that some, such as 9/12/2004, which appear in
two or more overlapping ranges are listed here only once. Any other
fields in [Revenue] would also be included here; to omit some, you'd
need to specify those you wanted individually instead of using "Revenue.*" :

Date Amount
--------- -------
9/10/2005 $853.53
9/25/2004 $666.76
9/12/2004 $172.00
8/25/2004 $68.50

Since you said "PRIOR TO", records matching the specified date will be
omitted (unless they're covered by some other range). I didn't include
that in my example here, but you can play around with the Query to see
if it works as you'd like it to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

So, that worked GREAT, except for the fact that I need to produce an exact X
number of records prior to the entered date. Using the
[RevenueDate]![DateEnded]-25 gives me a VARIABLE number of records, which is
why I previously used the TOP predicate. The reason I want an exact amount
of records generated is that the query generates an excel file which a macro
is run on....and the macro relies on the fact that each of the "subqueries"
generates an exact X records.

So, within the contruct of what you provided do you know how I can only
select X records from the revenue table for EACH date being used from the
RevenueDate table ?

Essentially, what I'm trying to do is this:

1. Take the FIRST record in the RevenueDate table.
2. Use the DATE in this record as the INPUT to a QUERY of the Revenue table.
3. Use a QUERY to search for all records with dates that are PRIOR to the
DATE uses from the RevenueDate table. (The result of this query would
essentially be a LARGE number of records in general. However, step 4 takes
care of this.)
4. Use the same QUERY (via the TOP predicate) to take ONLY the TOP 25
records that match the query.
5. REPEAT Steps 1-4 until the LAST record in the RevnueDate table is found.

I had hoped that I could incorporate some function into my existing code --
the code that uses the TOP predicate -- to explicitly "hard code" the actual
field that I would use from the RevenueDate. Take a look below. The ****
are statements that I had hoped to somehow EXPLICITLY reference a value from
the revenuedate table...

SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date <=REVENUEDATE.date RECORD 1 ****
ORDER BY revenue.Date DESC
UNION
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<=REVENUEDATE.date RECORD 2 ****
ORDER BY revenue.Date DESC
UNION
(continue doing for UP TO 28 dates within the "revenuedate" table)
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<= REVENUEDATE.date RECORD 28
ORDER BY revenue.Date DESC

Does this make sense and is this possible ? Is there any function that I
can use to pull a value explicitly from the revenuedate table to use for
comparison within the query ?

Tom,

Thanks for your reply. Yes, the 28 days will change, depending on the dates
which the user chooses. Each user will be required to submit a text file or
excel file containing the dates they wish to get data on. The data goes back
more than 25 years, and the users can pick any business date within the past
25 years, and (for each date they request) will receive the 25 business days
of sales PRIOR TO each date requested.

This was not my original impression of what you wanted; in your first
SQL, you'd asked for TOP 25, instead of for a 25-day date range. Date
range is easier. (I came up with a way to do TOP 25, but it was clumsy,
and I concluded, with Randy Harris, that VBA code would be cleaner &
easier. But it appears that that's immaterial here.)

Incidentally, TOP 25 doesn't guarantee 25 records. There might be fewer
than that in the dataset, or there might be a tie for 25th place, in
which case you'd get all the matching ones, perhaps including the entire
dataset.

Once they submit the list of requested dates, I would simply import this
into access and create a new table....or whatever I need/should do to
accomplish the goal of (a) not having users directly enter the data and (b)
ME not having to input this data manually.

I think I have a way to do what you (now) say you want. This assumes
that you will import a list of dates into a Table called [RevenueDate]
and name that field [DateEnded].

Sorry for the confusing goal, but I'm new to access and desperate to get
this project moving....

Thanks for your help!!

Let's assume that your [Revenue] Table contains these records (possibly
with other fields besides the two shown here):

Date Amount
--------- ---------
8/25/2004 $68.50
9/12/2004 $172.00
2/3/2005 $1,228.74
9/25/2004 $666.76
9/10/2005 $853.53

Let's assume that you import into Table [RevenueDate] the dates ended of
the 25-day periods that interest you, for example:

DateEnded
---------
3/31/2005
9/15/2004
9/30/2004
6/30/2004
9/30/2005

Then the following Query:

SELECT DISTINCT revenue.*
FROM Revenue, RevenueDate
WHERE ((([Revenue].[Date])<[RevenueDate]![DateEnded]
And ([Revenue].[Date])>=[RevenueDate]![DateEnded]-25))
ORDER BY [Revenue].[Date] DESC;

will produce the following output. Note that not all of the [Revenue]
data, such as 2/3/2005, appear, since they are not all within the
specified date ranges; and that some, such as 9/12/2004, which appear in
two or more overlapping ranges are listed here only once. Any other
fields in [Revenue] would also be included here; to omit some, you'd
need to specify those you wanted individually instead of using "Revenue.*" :

Date Amount
--------- -------
9/10/2005 $853.53
9/25/2004 $666.76
9/12/2004 $172.00
8/25/2004 $68.50

Since you said "PRIOR TO", records matching the specified date will be
omitted (unless they're covered by some other range). I didn't include
that in my example here, but you can play around with the Query to see
if it works as you'd like it to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Randy Harris]

Vincent has a very good solution. This can be done with a cartesian product.
Such as:

SELECT
Revenue.*
FROM
Revenue, RevenueDate
WHERE
Revenue.RevDate IN (
SELECT
TOP 25 Revenue.RevDate
FROM
Revenue
WHERE
Revenue.RevDate <= RevenueDate.RevDate
ORDER BY
Revenue.RevDate DESC
)
ORDER BY Revenue.RevDate

You should end up with (25 times however many dates there are in
RevenueDate) records.

So, that worked GREAT, except for the fact that I need to produce an exact X
number of records prior to the entered date. Using the
[RevenueDate]![DateEnded]-25 gives me a VARIABLE number of records, which is
why I previously used the TOP predicate. The reason I want an exact amount
of records generated is that the query generates an excel file which a macro
is run on....and the macro relies on the fact that each of the "subqueries"
generates an exact X records.

So, within the contruct of what you provided do you know how I can only
select X records from the revenue table for EACH date being used from the
RevenueDate table ?

Essentially, what I'm trying to do is this:

1. Take the FIRST record in the RevenueDate table.
2. Use the DATE in this record as the INPUT to a QUERY of the Revenue table.
3. Use a QUERY to search for all records with dates that are PRIOR to the
DATE uses from the RevenueDate table. (The result of this query would
essentially be a LARGE number of records in general. However, step 4 takes
care of this.)
4. Use the same QUERY (via the TOP predicate) to take ONLY the TOP 25
records that match the query.
5. REPEAT Steps 1-4 until the LAST record in the RevnueDate table is found.

I had hoped that I could incorporate some function into my existing code --
the code that uses the TOP predicate -- to explicitly "hard code" the actual
field that I would use from the RevenueDate. Take a look below. The ****
are statements that I had hoped to somehow EXPLICITLY reference a value from
the revenuedate table...

SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date <=REVENUEDATE.date RECORD 1 ****
ORDER BY revenue.Date DESC
UNION
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<=REVENUEDATE.date RECORD 2 ****
ORDER BY revenue.Date DESC
UNION
(continue doing for UP TO 28 dates within the "revenuedate" table)
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<= REVENUEDATE.date RECORD 28
ORDER BY revenue.Date DESC

Does this make sense and is this possible ? Is there any function that I
can use to pull a value explicitly from the revenuedate table to use for
comparison within the query ?

Tom,

Thanks for your reply. Yes, the 28 days will change, depending on the dates
which the user chooses. Each user will be required to submit a text file or
excel file containing the dates they wish to get data on. The data goes back
more than 25 years, and the users can pick any business date within the past
25 years, and (for each date they request) will receive the 25 business days
of sales PRIOR TO each date requested.

This was not my original impression of what you wanted; in your first
SQL, you'd asked for TOP 25, instead of for a 25-day date range. Date
range is easier. (I came up with a way to do TOP 25, but it was clumsy,
and I concluded, with Randy Harris, that VBA code would be cleaner &
easier. But it appears that that's immaterial here.)

Incidentally, TOP 25 doesn't guarantee 25 records. There might be fewer
than that in the dataset, or there might be a tie for 25th place, in
which case you'd get all the matching ones, perhaps including the entire
dataset.

Once they submit the list of requested dates, I would simply import this
into access and create a new table....or whatever I need/should do to
accomplish the goal of (a) not having users directly enter the data and (b)
ME not having to input this data manually.

I think I have a way to do what you (now) say you want. This assumes
that you will import a list of dates into a Table called [RevenueDate]
and name that field [DateEnded].

Sorry for the confusing goal, but I'm new to access and desperate to get
this project moving....

Thanks for your help!!

Let's assume that your [Revenue] Table contains these records (possibly
with other fields besides the two shown here):

Date Amount
--------- ---------
8/25/2004 $68.50
9/12/2004 $172.00
2/3/2005 $1,228.74
9/25/2004 $666.76
9/10/2005 $853.53

Let's assume that you import into Table [RevenueDate] the dates ended of
the 25-day periods that interest you, for example:

DateEnded
---------
3/31/2005
9/15/2004
9/30/2004
6/30/2004
9/30/2005

Then the following Query:

SELECT DISTINCT revenue.*
FROM Revenue, RevenueDate
WHERE ((([Revenue].[Date])<[RevenueDate]![DateEnded]
And ([Revenue].[Date])>=[RevenueDate]![DateEnded]-25))
ORDER BY [Revenue].[Date] DESC;

will produce the following output. Note that not all of the [Revenue]
data, such as 2/3/2005, appear, since they are not all within the
specified date ranges; and that some, such as 9/12/2004, which appear in
two or more overlapping ranges are listed here only once. Any other
fields in [Revenue] would also be included here; to omit some, you'd
need to specify those you wanted individually instead of using "Revenue.*" :

Date Amount
--------- -------
9/10/2005 $853.53
9/25/2004 $666.76
9/12/2004 $172.00
8/25/2004 $68.50

Since you said "PRIOR TO", records matching the specified date will be
omitted (unless they're covered by some other range). I didn't include
that in my example here, but you can play around with the Query to see
if it works as you'd like it to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Thanks yet again for your help. I've tried the code below numerous times.
When I reun the query, Access just hangs, and my CPU spikes to 99%. No idea
what to do.

Vincent has a very good solution. This can be done with a cartesian product.
Such as:

SELECT
Revenue.*
FROM
Revenue, RevenueDate
WHERE
Revenue.RevDate IN (
SELECT
TOP 25 Revenue.RevDate
FROM
Revenue
WHERE
Revenue.RevDate <= RevenueDate.RevDate
ORDER BY
Revenue.RevDate DESC
)
ORDER BY Revenue.RevDate

You should end up with (25 times however many dates there are in
RevenueDate) records.

So, that worked GREAT, except for the fact that I need to produce an exact X
number of records prior to the entered date. Using the
[RevenueDate]![DateEnded]-25 gives me a VARIABLE number of records, which is
why I previously used the TOP predicate. The reason I want an exact amount
of records generated is that the query generates an excel file which a macro
is run on....and the macro relies on the fact that each of the "subqueries"
generates an exact X records.

So, within the contruct of what you provided do you know how I can only
select X records from the revenue table for EACH date being used from the
RevenueDate table ?

Essentially, what I'm trying to do is this:

1. Take the FIRST record in the RevenueDate table.
2. Use the DATE in this record as the INPUT to a QUERY of the Revenue table.
3. Use a QUERY to search for all records with dates that are PRIOR to the
DATE uses from the RevenueDate table. (The result of this query would
essentially be a LARGE number of records in general. However, step 4 takes
care of this.)
4. Use the same QUERY (via the TOP predicate) to take ONLY the TOP 25
records that match the query.
5. REPEAT Steps 1-4 until the LAST record in the RevnueDate table is found.

I had hoped that I could incorporate some function into my existing code --
the code that uses the TOP predicate -- to explicitly "hard code" the actual
field that I would use from the RevenueDate. Take a look below. The ****
are statements that I had hoped to somehow EXPLICITLY reference a value from
the revenuedate table...

SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date <=REVENUEDATE.date RECORD 1 ****
ORDER BY revenue.Date DESC
UNION
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<=REVENUEDATE.date RECORD 2 ****
ORDER BY revenue.Date DESC
UNION
(continue doing for UP TO 28 dates within the "revenuedate" table)
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<= REVENUEDATE.date RECORD 28
ORDER BY revenue.Date DESC

Does this make sense and is this possible ? Is there any function that I
can use to pull a value explicitly from the revenuedate table to use for
comparison within the query ?

new2access wrote:

Tom,

Thanks for your reply. Yes, the 28 days will change, depending on the dates
which the user chooses. Each user will be required to submit a text file or
excel file containing the dates they wish to get data on. The data goes back
more than 25 years, and the users can pick any business date within the past
25 years, and (for each date they request) will receive the 25 business days
of sales PRIOR TO each date requested.

This was not my original impression of what you wanted; in your first
SQL, you'd asked for TOP 25, instead of for a 25-day date range. Date
range is easier. (I came up with a way to do TOP 25, but it was clumsy,
and I concluded, with Randy Harris, that VBA code would be cleaner &
easier. But it appears that that's immaterial here.)

Incidentally, TOP 25 doesn't guarantee 25 records. There might be fewer
than that in the dataset, or there might be a tie for 25th place, in
which case you'd get all the matching ones, perhaps including the entire
dataset.

Once they submit the list of requested dates, I would simply import this
into access and create a new table....or whatever I need/should do to
accomplish the goal of (a) not having users directly enter the data and (b)
ME not having to input this data manually.

I think I have a way to do what you (now) say you want. This assumes
that you will import a list of dates into a Table called [RevenueDate]
and name that field [DateEnded].

Sorry for the confusing goal, but I'm new to access and desperate to get
this project moving....

Thanks for your help!!



Let's assume that your [Revenue] Table contains these records (possibly
with other fields besides the two shown here):

Date Amount
--------- ---------
8/25/2004 $68.50
9/12/2004 $172.00
2/3/2005 $1,228.74
9/25/2004 $666.76
9/10/2005 $853.53

Let's assume that you import into Table [RevenueDate] the dates ended of
the 25-day periods that interest you, for example:

DateEnded
---------
3/31/2005
9/15/2004
9/30/2004
6/30/2004
9/30/2005

Then the following Query:

SELECT DISTINCT revenue.*
FROM Revenue, RevenueDate
WHERE ((([Revenue].[Date])<[RevenueDate]![DateEnded]
And ([Revenue].[Date])>=[RevenueDate]![DateEnded]-25))
ORDER BY [Revenue].[Date] DESC;

will produce the following output. Note that not all of the [Revenue]
data, such as 2/3/2005, appear, since they are not all within the
specified date ranges; and that some, such as 9/12/2004, which appear in
two or more overlapping ranges are listed here only once. Any other
fields in [Revenue] would also be included here; to omit some, you'd
need to specify those you wanted individually instead of using "Revenue.*" :

Date Amount
--------- -------
9/10/2005 $853.53
9/25/2004 $666.76
9/12/2004 $172.00
8/25/2004 $68.50

Since you said "PRIOR TO", records matching the specified date will be
omitted (unless they're covered by some other range). I didn't include
that in my example here, but you can play around with the Query to see
if it works as you'd like it to.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Randy Harris]

OK, I created a table called Revenue and another called RevenueDate. They
each have a field called RDate (you might recall I warned you against using
a field called Date). The query below does exactly what you want. It
selects the Top 25 records from Revenue before each RDate in RevenueDate. I
copied it directly from Access.


SELECT Revenue.*
FROM Revenue, RevenueDate
WHERE Revenue.RDate in (select top 25 Rdate from Revenue where Revenue.Rdate
<= RevenueDate.Rdate order by Rdate desc)
ORDER BY Revenue.RDate;

Randy

 

[Guest]

Randy, et al: THANKS FOR ALL OF YOUR HELP!! I was basically given this
project two weeks ago, and I've never used Access before, so thank you so
much for your help and patience!!!!

 

[Vincent Johns]

So, that worked GREAT, except for the fact that I need to produce an exact X
number of records prior to the entered date. Using the
[RevenueDate]![DateEnded]-25 gives me a VARIABLE number of records, which is
why I previously used the TOP predicate. The reason I want an exact amount
of records generated is that the query generates an excel file which a macro
is run on....and the macro relies on the fact that each of the "subqueries"
generates an exact X records.

Sadly, TOP 25 will sometimes return 25 records, but sometimes fewer and
sometimes more. Fewer if the dataset contains only 7 records to begin
with. More if there's a tie for 25th place. (If the keys are unique
this isn't a problem.)

So, within the contruct of what you provided do you know how I can only
select X records from the revenue table for EACH date being used from the
RevenueDate table ?

Essentially, what I'm trying to do is this:

1. Take the FIRST record in the RevenueDate table.
2. Use the DATE in this record as the INPUT to a QUERY of the Revenue table.
3. Use a QUERY to search for all records with dates that are PRIOR to the
DATE uses from the RevenueDate table. (The result of this query would
essentially be a LARGE number of records in general. However, step 4 takes
care of this.)
4. Use the same QUERY (via the TOP predicate) to take ONLY the TOP 25
records that match the query.

Which means that you can omit step 3. But you might not get exactly 25
records.

5. REPEAT Steps 1-4 until the LAST record in the RevnueDate table is found.

I had hoped that I could incorporate some function into my existing code --
the code that uses the TOP predicate -- to explicitly "hard code" the actual
field that I would use from the RevenueDate. Take a look below. The ****
are statements that I had hoped to somehow EXPLICITLY reference a value from
the revenuedate table...

SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date <=REVENUEDATE.date RECORD 1 ****
ORDER BY revenue.Date DESC
UNION
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<=REVENUEDATE.date RECORD 2 ****
ORDER BY revenue.Date DESC
UNION
(continue doing for UP TO 28 dates within the "revenuedate" table)
SELECT TOP 25 revenue.*
FROM revenue, revenuedate
**** WHERE revenue.Date)<= REVENUEDATE.date RECORD 28
ORDER BY revenue.Date DESC

Does this make sense and is this possible ? Is there any function that I
can use to pull a value explicitly from the revenuedate table to use for
comparison within the query ?

Since you need EXACTLY 25 records from each step in the Query, it's
possible that you'd be ahead biting the bullet and writing a VBA
procedure to do what you want, using DAOs to apply the SQL that you'd
compute based on the user's input. You woudn't even have to specify 28
values; you could just use however many the user supplied. But if you
want to go to that kind of trouble, perhaps you could also replace the
Excel macro with something that would be easier to maintain (just a
suggestion).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.