Limiting Records in an Update Query
in [Queries]
|
From: Amy E. Baggott on 14 May 2010 15:03 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
From: golfinray on 14 May 2010 15:51 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
From: Amy E. Baggott on 14 May 2010 16:07 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
From: golfinray on 14 May 2010 16:29 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
From: Amy E. Baggott on 14 May 2010 16:37
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 |