Limiting Records in an Update Query

[Amy E. Baggott]

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?

 

[golfinray]

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.

 

[Amy E. Baggott]

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.

 

[golfinray]

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.

 

[Amy E. Baggott]

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.

 

[golfinray]

Checkout sql help for assigning top values for update queries. There is a way
to do that with your Where statement.