A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Limiting Records in an Update Query



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2010, 08:03 PM posted to microsoft.public.access.queries
Amy E. Baggott
external usenet poster
 
Posts: 79
Default Limiting Records in an Update Query

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
  #2  
Old May 14th, 2010, 08:51 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default Limiting Records in an Update Query

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

  #3  
Old May 14th, 2010, 09:07 PM posted to microsoft.public.access.queries
Amy E. Baggott
external usenet poster
 
Posts: 79
Default Limiting Records in an Update Query

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

  #4  
Old May 14th, 2010, 09:29 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default Limiting Records in an Update Query

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

  #5  
Old May 14th, 2010, 09:37 PM posted to microsoft.public.access.queries
Amy E. Baggott
external usenet poster
 
Posts: 79
Default Limiting Records in an Update Query

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

  #6  
Old May 14th, 2010, 09:51 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default Limiting Records in an Update Query

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.