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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|