Prev: Date Question...
Next: Find Duplicates Query Wizard Question
From: golfinray on 14 May 2010 16:51
Checkout sql help for assigning top values for update queries. There is a way
to do that with your Where statement.
--
Milton Purdy
ACCESS
State of Arkansas


"Amy E. Baggott" wrote:

> The report only selects records that have null print dates, as I only want to
> see new booth assignments. I then need to update those new booth assignments
> to set the print date. I have no problem if I simply run the report and the
> update for "all unprinted", but this can run to 60 or 70 assignments during
> the initial assignment period. My boss has therefore asked me if there is a
> way I can limit it so that it will print in smaller batches. The only way I
> can think of to do this, given that they all meet the same criteria
> (including having the same assignment date; during the initial assignment
> period, those guys are assigning fools!) is to set the Top Values property to
> 15. This works in the select query, but I cannot find a similar property in
> the update query.
>
> --
> Amy E. Baggott
>
> "I''m going crazy and I''m taking all of you with me!" -- Linda Grayson
>
>
> "golfinray" wrote:
>
> > Could you set up a date last_printed field in the query? On your print
> > command button, add in something like Me.last_printed = Now().
> > Then you could use between ____ and ___ as the criteria for your update query.
> > --
> > Milton Purdy
> > ACCESS
> > State of Arkansas
> >
> >
> > "Amy E. Baggott" wrote:
> >
> > > The print date is what I'm trying to set. If I simply use null as my
> > > criterion, it updates ALL of the assignment records with null print dates. I
> > > want it only to update the ones that are selected in the select query that
> > > drives the report. The code is set up to open the report, then run the
> > > update query. The user closes the report after printing it. On the select
> > > query I have it set to show only the first 15 records in alphabetical order.
> > > However, since the query (1) is a SELECT DISTINCT since I want only one page
> > > per exhibitor regardless of how many booths they are assigned (the booth
> > > assignments themselves are listed as a subreport) and (2) uses information
> > > that is derived from a subquery that has summary information, I can't use a
> > > Join to the select query to filter the update query.
> > >
> > > --
> > > Amy E. Baggott
> > >
> > > "I''m going crazy and I''m taking all of you with me!" -- Linda Grayson
> > >
> > >
> > > "golfinray" wrote:
> > >
> > > > My question is, how would you know that they have been printed? There would
> > > > have to be some sort of criteria, like a date of print or a last update date
> > > > or something to let you know those records have been printed. Otherwise, how
> > > > would you know how to put in query criteria to only update those that hadn't
> > > > been? If the records are null, you could use Is Null as your update query
> > > > criteria. If the records are not null, you could use Is Not Null. If the
> > > > records have some sort of verification of print, use that.
> > > > --
> > > > Milton Purdy
> > > > ACCESS
> > > > State of Arkansas
> > > >
> > > >
> > > > "Amy E. Baggott" wrote:
> > > >
> > > > > I have a report that generates assignment sheets for exhibitors who have
> > > > > received booth assignments so that the orders can be updated (for various
> > > > > reasons, we don't want the sales staff updating the orders directly). There
> > > > > is an update query that then flags those assignments as printed so that they
> > > > > are not printed again. During the early part of the show year, when we are
> > > > > assigning the bulk of our exhibitors, these runs can quickly grow to 60
> > > > > sheets or more. What I'd like to do is be able to run them in smaller groups
> > > > > that are easier to manage so that each support person can run a batch, assign
> > > > > them, run the invoices and floor plans and pass them on for proofing, then
> > > > > run another batch to keep the process moving. I know how to limit the number
> > > > > of records in the query that drives the report, but I don't know how to limit
> > > > > the number of records in the update query to update only the records whose
> > > > > assignment sheets have been run. I tried just tying in the query that drives
> > > > > the report, but it is not updatable, so the update query bombs. (I have
> > > > > never understood why Access cares whether a subquery is updatable if you are
> > > > > not trying to update the information in that query, but that's another rant
> > > > > for another time.) In the meantime, I'm trying to figure out how to make
> > > > > sure that only the records that have actually BEEN printed get MARKED as
> > > > > printed because if any fall through the cracks, it can be a major mess.
> > > > >
> > > > > Can anyone help me?
> > > > > --
> > > > > Amy E. Baggott
> > > > >
> > > > > "I''m going crazy and I''m taking all of you with me!" -- Linda Grayson
First  |  Prev  | 
Pages: 1 2
Prev: Date Question...
Next: Find Duplicates Query Wizard Question