Re: using a "for" loop in a query to reference data from another t
- From: "new2access" <new2access@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 12 Oct 2005 20:18:03 -0700
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????
"Tom Ellison" wrote:
> 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
>
>
> "new2access" <new2access@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:2D183D49-D9D4-4FAE-B02B-28F5938D2942@xxxxxxxxxxxxxxxx
> > 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!!
> >
>
>
>
.
- Follow-Ups:
- Re: using a "for" loop in a query to reference data from another t
- From: Randy Harris
- Re: using a "for" loop in a query to reference data from another t
- Prev by Date: Re: Combining data fields into one?
- Next by Date: Re: How Do I compare differences between the data in two tables?
- Previous by thread: Re: How do I see Null Values in a field that is text?
- Next by thread: Re: using a "for" loop in a query to reference data from another t
- Index(es):
Relevant Pages
|
