Re: using a "for" loop in a query to reference data from another t
- From: "Randy Harris" <randy@xxxxxxxxxx>
- Date: Wed, 12 Oct 2005 23:36:21 -0400
You're not going to be able to do this as a saved query. The query will
need to be built in code.
"new2access" <new2access@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4E7FF246-00E9-4247-B5F1-D4E753CB6988@xxxxxxxxxxxxxxxx
> 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!!
> > >
> >
> >
> >
.
- References:
- Re: using a "for" loop in a query to reference data from another t
- From: new2access
- Re: using a "for" loop in a query to reference data from another t
- Prev by Date: Re: Split a field in two new fields
- Next by Date: Re: Integrating the code for a max.date function with a check box controlling it and
- Previous by thread: Re: using a "for" loop in a query to reference data from another t
- Next by thread: Re: using a "for" loop in a query to reference data from another t
- Index(es):
Relevant Pages
|
