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 |
#11
|
|||
|
|||
finds gaps in sequential numbers?
Dear Pip:
There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's what you desire. Given full details, I could work out a solution on this basis, too. Tom Ellison "Pip''''n" wrote in message ... I too am looking for a way to determine missing numbers in a sequence. Your solution is a wonderful way to determine the ranges missing. Is there a way to list each missing number through this solution? Pip''n "Tom Ellison" wrote: Dear Jennifer: Step 1: find a Number where the next number is missing, but not the greatest number in the table: SELECT Number FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Here, a subquery in the EXISTS clause looks for Number + 1. SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1 Notice the use of Aliases T and T1. This allows the query to look at the whole table using T1 without referenced to the "outer" query based on an instance of the table called T. The WHERE clause relates the two copies of the table. It says, "look for the next sequential value in Number, relative to the value in the outer query which is considering each value of Number that exists." Another subquery: SELECT MAX(Number) FROM YourTable Simply returns the largest value of Number in the table. We want to omit that value from our list of missing ranges. Now we have the values of Number that are just before the range of missing numbers. Add 1 to define the start of each range. Now, what is the upper end of each range? To find that, find the next larger Number that does exist and subtract 1: (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 That's it! Any clearer? I would think it reads well, but you would probably need to practice it. These techniques have a wide range of applications. Read up on the subjects "alias" and "subquery". Tom Ellison "Jennifer@DakCo" wrote in message ... Tom - thanks so much for the info! I spent a lot of time trying to find a solution for this without much success until you responded to my post. And yes, I think it would benefit me if you could break this down and explain how it works. Jennifer "Tom Ellison" wrote: Dear Jennifer: I would do this with a subquery that checks for N + 1 not existing as the start of a sequence of missing numbers, and X - 1 as the end of that sequence of missing numbers, where X is the next larger (the MIN() of those greater than). SELECT Number + 1 AS BeginMissing, (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 AS EndMissing FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Substitute the actual name of YourTable and the column name having the Number in it. This will work much more quickly if there is an index on the column I called Number. Would you benefit from me breaking this down and explaining how it works? Tom Ellison If you post the name of the table and of the column having these numbers, I could create the actual SQL. "Jennifer@DakCo" wrote in message ... How do I create a query, in MS Office Access 2000, that will find gaps in manually typed sequential numbers? |
#12
|
|||
|
|||
finds gaps in sequential numbers?
Tom,
you mentioned assigning the Max(Invoice) to the record using a query. When I try it i get the error that the field InvoiceNo is not being used as part of an aggregate function. Here's the code I tried: UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1 WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#])); Also, how do you test for NewRecord? I've been checking to see if the key field is Null. "Tom Ellison" wrote: Dear Pip: There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's what you desire. Given full details, I could work out a solution on this basis, too. Tom Ellison "Pip''''n" wrote in message ... I too am looking for a way to determine missing numbers in a sequence. Your solution is a wonderful way to determine the ranges missing. Is there a way to list each missing number through this solution? Pip''n "Tom Ellison" wrote: Dear Jennifer: Step 1: find a Number where the next number is missing, but not the greatest number in the table: SELECT Number FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Here, a subquery in the EXISTS clause looks for Number + 1. SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1 Notice the use of Aliases T and T1. This allows the query to look at the whole table using T1 without referenced to the "outer" query based on an instance of the table called T. The WHERE clause relates the two copies of the table. It says, "look for the next sequential value in Number, relative to the value in the outer query which is considering each value of Number that exists." Another subquery: SELECT MAX(Number) FROM YourTable Simply returns the largest value of Number in the table. We want to omit that value from our list of missing ranges. Now we have the values of Number that are just before the range of missing numbers. Add 1 to define the start of each range. Now, what is the upper end of each range? To find that, find the next larger Number that does exist and subtract 1: (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 That's it! Any clearer? I would think it reads well, but you would probably need to practice it. These techniques have a wide range of applications. Read up on the subjects "alias" and "subquery". Tom Ellison "Jennifer@DakCo" wrote in message ... Tom - thanks so much for the info! I spent a lot of time trying to find a solution for this without much success until you responded to my post. And yes, I think it would benefit me if you could break this down and explain how it works. Jennifer "Tom Ellison" wrote: Dear Jennifer: I would do this with a subquery that checks for N + 1 not existing as the start of a sequence of missing numbers, and X - 1 as the end of that sequence of missing numbers, where X is the next larger (the MIN() of those greater than). SELECT Number + 1 AS BeginMissing, (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 AS EndMissing FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Substitute the actual name of YourTable and the column name having the Number in it. This will work much more quickly if there is an index on the column I called Number. Would you benefit from me breaking this down and explaining how it works? Tom Ellison If you post the name of the table and of the column having these numbers, I could create the actual SQL. "Jennifer@DakCo" wrote in message ... How do I create a query, in MS Office Access 2000, that will find |
#13
|
|||
|
|||
finds gaps in sequential numbers?
Dear Pip:
I don't understand using an update query to assign the InvoiceNo. I suggest setting this in the BeforeUpdate event when it is a NewRecord, as I said before. Testing NewRecord in the code of a form is Me.NewRecord. Tom Ellison "Pip''''n" wrote in message ... Tom, you mentioned assigning the Max(Invoice) to the record using a query. When I try it i get the error that the field InvoiceNo is not being used as part of an aggregate function. Here's the code I tried: UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1 WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#])); Also, how do you test for NewRecord? I've been checking to see if the key field is Null. "Tom Ellison" wrote: Dear Pip: There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's what you desire. Given full details, I could work out a solution on this basis, too. Tom Ellison "Pip''''n" wrote in message ... I too am looking for a way to determine missing numbers in a sequence. Your solution is a wonderful way to determine the ranges missing. Is there a way to list each missing number through this solution? Pip''n "Tom Ellison" wrote: Dear Jennifer: Step 1: find a Number where the next number is missing, but not the greatest number in the table: SELECT Number FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Here, a subquery in the EXISTS clause looks for Number + 1. SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1 Notice the use of Aliases T and T1. This allows the query to look at the whole table using T1 without referenced to the "outer" query based on an instance of the table called T. The WHERE clause relates the two copies of the table. It says, "look for the next sequential value in Number, relative to the value in the outer query which is considering each value of Number that exists." Another subquery: SELECT MAX(Number) FROM YourTable Simply returns the largest value of Number in the table. We want to omit that value from our list of missing ranges. Now we have the values of Number that are just before the range of missing numbers. Add 1 to define the start of each range. Now, what is the upper end of each range? To find that, find the next larger Number that does exist and subtract 1: (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 That's it! Any clearer? I would think it reads well, but you would probably need to practice it. These techniques have a wide range of applications. Read up on the subjects "alias" and "subquery". Tom Ellison "Jennifer@DakCo" wrote in message ... Tom - thanks so much for the info! I spent a lot of time trying to find a solution for this without much success until you responded to my post. And yes, I think it would benefit me if you could break this down and explain how it works. Jennifer "Tom Ellison" wrote: Dear Jennifer: I would do this with a subquery that checks for N + 1 not existing as the start of a sequence of missing numbers, and X - 1 as the end of that sequence of missing numbers, where X is the next larger (the MIN() of those greater than). SELECT Number + 1 AS BeginMissing, (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 AS EndMissing FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Substitute the actual name of YourTable and the column name having the Number in it. This will work much more quickly if there is an index on the column I called Number. Would you benefit from me breaking this down and explaining how it works? Tom Ellison If you post the name of the table and of the column having these numbers, I could create the actual SQL. "Jennifer@DakCo" wrote in message ... How do I create a query, in MS Office Access 2000, that will find |
#14
|
|||
|
|||
finds gaps in sequential numbers?
I understand using the BeforeUpdate event, how do I determine the invoice
number to use? Pip''n "Tom Ellison" wrote: Dear Pip: I don't understand using an update query to assign the InvoiceNo. I suggest setting this in the BeforeUpdate event when it is a NewRecord, as I said before. Testing NewRecord in the code of a form is Me.NewRecord. Tom Ellison "Pip''''n" wrote in message ... Tom, you mentioned assigning the Max(Invoice) to the record using a query. When I try it i get the error that the field InvoiceNo is not being used as part of an aggregate function. Here's the code I tried: UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1 WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#])); Also, how do you test for NewRecord? I've been checking to see if the key field is Null. "Tom Ellison" wrote: Dear Pip: There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's what you desire. Given full details, I could work out a solution on this basis, too. Tom Ellison "Pip''''n" wrote in message ... I too am looking for a way to determine missing numbers in a sequence. Your solution is a wonderful way to determine the ranges missing. Is there a way to list each missing number through this solution? Pip''n "Tom Ellison" wrote: Dear Jennifer: Step 1: find a Number where the next number is missing, but not the greatest number in the table: SELECT Number FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Here, a subquery in the EXISTS clause looks for Number + 1. SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1 Notice the use of Aliases T and T1. This allows the query to look at the whole table using T1 without referenced to the "outer" query based on an instance of the table called T. The WHERE clause relates the two copies of the table. It says, "look for the next sequential value in Number, relative to the value in the outer query which is considering each value of Number that exists." Another subquery: SELECT MAX(Number) FROM YourTable Simply returns the largest value of Number in the table. We want to omit that value from our list of missing ranges. Now we have the values of Number that are just before the range of missing numbers. Add 1 to define the start of each range. Now, what is the upper end of each range? To find that, find the next larger Number that does exist and subtract 1: (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 That's it! Any clearer? I would think it reads well, but you would probably need to practice it. These techniques have a wide range of applications. Read up on the subjects "alias" and "subquery". Tom Ellison "Jennifer@DakCo" wrote in message ... Tom - thanks so much for the info! I spent a lot of time trying to find a solution for this without much success until you responded to my post. And yes, I think it would benefit me if you could break this down and |
#15
|
|||
|
|||
finds gaps in sequential numbers?
Dear Pip:
Use a query that returns the value, as we have discussed. Run this query from code in the BeforeUpdate event. Grab the returned value (one row, one column). Tom Ellison "Pip''''n" wrote in message ... I understand using the BeforeUpdate event, how do I determine the invoice number to use? Pip''n "Tom Ellison" wrote: Dear Pip: I don't understand using an update query to assign the InvoiceNo. I suggest setting this in the BeforeUpdate event when it is a NewRecord, as I said before. Testing NewRecord in the code of a form is Me.NewRecord. Tom Ellison "Pip''''n" wrote in message ... Tom, you mentioned assigning the Max(Invoice) to the record using a query. When I try it i get the error that the field InvoiceNo is not being used as part of an aggregate function. Here's the code I tried: UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1 WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#])); Also, how do you test for NewRecord? I've been checking to see if the key field is Null. "Tom Ellison" wrote: Dear Pip: There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's what you desire. Given full details, I could work out a solution on this basis, too. Tom Ellison "Pip''''n" wrote in message ... I too am looking for a way to determine missing numbers in a sequence. Your solution is a wonderful way to determine the ranges missing. Is there a way to list each missing number through this solution? Pip''n "Tom Ellison" wrote: Dear Jennifer: Step 1: find a Number where the next number is missing, but not the greatest number in the table: SELECT Number FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Here, a subquery in the EXISTS clause looks for Number + 1. SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1 Notice the use of Aliases T and T1. This allows the query to look at the whole table using T1 without referenced to the "outer" query based on an instance of the table called T. The WHERE clause relates the two copies of the table. It says, "look for the next sequential value in Number, relative to the value in the outer query which is considering each value of Number that exists." Another subquery: SELECT MAX(Number) FROM YourTable Simply returns the largest value of Number in the table. We want to omit that value from our list of missing ranges. Now we have the values of Number that are just before the range of missing numbers. Add 1 to define the start of each range. Now, what is the upper end of each range? To find that, find the next larger Number that does exist and subtract 1: (SELECT MIN(Number) FROM YourTable T1 WHERE T1.Number T.Number) - 1 That's it! Any clearer? I would think it reads well, but you would probably need to practice it. These techniques have a wide range of applications. Read up on the subjects "alias" and "subquery". Tom Ellison "Jennifer@DakCo" wrote in message ... Tom - thanks so much for the info! I spent a lot of time trying to find a solution for this without much success until you responded to my post. And yes, I think it would benefit me if you could break this down and |
#16
|
|||
|
|||
finds gaps in sequential numbers?
Sorry, I asked the wrong question last time.
I know how to create the query to show the max(invoiceNo). I also know how to run the query using the DoCmd.OpenQuery. But how do I grab the single value in order to assign the value to the table. If i'm not to use the DoCmd.OpenQuery what do I use? I understand as you mentioned that you can't use an update query in the BeforeUpdate event because you havn't updated the table with the new record. Pip''n "Tom Ellison" wrote: Dear Pip: Use a query that returns the value, as we have discussed. Run this query from code in the BeforeUpdate event. Grab the returned value (one row, one column). Tom Ellison "Pip''''n" wrote in message ... I understand using the BeforeUpdate event, how do I determine the invoice number to use? Pip''n "Tom Ellison" wrote: Dear Pip: I don't understand using an update query to assign the InvoiceNo. I suggest setting this in the BeforeUpdate event when it is a NewRecord, as I said before. Testing NewRecord in the code of a form is Me.NewRecord. Tom Ellison "Pip''''n" wrote in message ... Tom, you mentioned assigning the Max(Invoice) to the record using a query. When I try it i get the error that the field InvoiceNo is not being used as part of an aggregate function. Here's the code I tried: UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1 WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#])); Also, how do you test for NewRecord? I've been checking to see if the key field is Null. "Tom Ellison" wrote: Dear Pip: There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's what you desire. Given full details, I could work out a solution on this basis, too. Tom Ellison "Pip''''n" wrote in message ... I too am looking for a way to determine missing numbers in a sequence. Your solution is a wonderful way to determine the ranges missing. Is there a way to list each missing number through this solution? Pip''n "Tom Ellison" wrote: Dear Jennifer: Step 1: find a Number where the next number is missing, but not the greatest number in the table: SELECT Number FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Here, a subquery in the EXISTS clause looks for Number + 1. SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1 Notice the use of Aliases T and T1. This allows the query to look at the whole table using T1 without referenced to the "outer" query based on an instance of the table called T. The WHERE clause relates the two copies of the table. It says, "look for the next sequential value in Number, relative to the value in the outer query which is considering each value of Number that exists." Another subquery: |
#17
|
|||
|
|||
finds gaps in sequential numbers?
Dear Pip:
Open the query as a recordset, read the record, and reference the value: Public Function testme() As String Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("SELECT MAX(invoiceNo) AS MaxInvNo FROM YourTable") rst.MoveFirst Debug.Print rst!MaxInvNo End Function Replace YourTable with the actual name of your table. You don't need the Debug.Print line, it just shows that the value you need is now available as rst!MaxInvNo. Tom Ellison "Pip''''n" wrote in message ... Sorry, I asked the wrong question last time. I know how to create the query to show the max(invoiceNo). I also know how to run the query using the DoCmd.OpenQuery. But how do I grab the single value in order to assign the value to the table. If i'm not to use the DoCmd.OpenQuery what do I use? I understand as you mentioned that you can't use an update query in the BeforeUpdate event because you havn't updated the table with the new record. Pip''n "Tom Ellison" wrote: Dear Pip: Use a query that returns the value, as we have discussed. Run this query from code in the BeforeUpdate event. Grab the returned value (one row, one column). Tom Ellison "Pip''''n" wrote in message ... I understand using the BeforeUpdate event, how do I determine the invoice number to use? Pip''n "Tom Ellison" wrote: Dear Pip: I don't understand using an update query to assign the InvoiceNo. I suggest setting this in the BeforeUpdate event when it is a NewRecord, as I said before. Testing NewRecord in the code of a form is Me.NewRecord. Tom Ellison "Pip''''n" wrote in message ... Tom, you mentioned assigning the Max(Invoice) to the record using a query. When I try it i get the error that the field InvoiceNo is not being used as part of an aggregate function. Here's the code I tried: UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1 WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#])); Also, how do you test for NewRecord? I've been checking to see if the key field is Null. "Tom Ellison" wrote: Dear Pip: There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's what you desire. Given full details, I could work out a solution on this basis, too. Tom Ellison "Pip''''n" wrote in message ... I too am looking for a way to determine missing numbers in a sequence. Your solution is a wonderful way to determine the ranges missing. Is there a way to list each missing number through this solution? Pip''n "Tom Ellison" wrote: Dear Jennifer: Step 1: find a Number where the next number is missing, but not the greatest number in the table: SELECT Number FROM YourTable T WHERE NOT EXISTS ( SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1) AND Number (SELECT MAX(Number) FROM YourTable) Here, a subquery in the EXISTS clause looks for Number + 1. SELECT * FROM YourTable T1 WHERE T1.Number = T.Number + 1 Notice the use of Aliases T and T1. This allows the query to look at the whole table using T1 without referenced to the "outer" query based on an instance of the table called T. The WHERE clause relates the two copies of the table. It says, "look for the next sequential value in Number, relative to the value in the outer query which is considering each value of Number that exists." Another subquery: |
#18
|
|||
|
|||
finds gaps in sequential numbers?
Dear Tom,
Thanks for all your help. Your solution worked after I changed the variable rst to an Object instead of a RecordSet. Thanks for the Debug.Print action too. All this time i've been using msg boxes. Thanks Again, Pip''n "Tom Ellison" wrote: Dear Pip: Open the query as a recordset, read the record, and reference the value: Public Function testme() As String Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("SELECT MAX(invoiceNo) AS MaxInvNo FROM YourTable") rst.MoveFirst Debug.Print rst!MaxInvNo End Function Replace YourTable with the actual name of your table. You don't need the Debug.Print line, it just shows that the value you need is now available as rst!MaxInvNo. Tom Ellison "Pip''''n" wrote in message ... Sorry, I asked the wrong question last time. I know how to create the query to show the max(invoiceNo). I also know how to run the query using the DoCmd.OpenQuery. But how do I grab the single value in order to assign the value to the table. If i'm not to use the DoCmd.OpenQuery what do I use? I understand as you mentioned that you can't use an update query in the BeforeUpdate event because you havn't updated the table with the new record. Pip''n "Tom Ellison" wrote: Dear Pip: Use a query that returns the value, as we have discussed. Run this query from code in the BeforeUpdate event. Grab the returned value (one row, one column). Tom Ellison "Pip''''n" wrote in message ... I understand using the BeforeUpdate event, how do I determine the invoice number to use? Pip''n "Tom Ellison" wrote: Dear Pip: I don't understand using an update query to assign the InvoiceNo. I suggest setting this in the BeforeUpdate event when it is a NewRecord, as I said before. Testing NewRecord in the code of a form is Me.NewRecord. Tom Ellison "Pip''''n" wrote in message ... Tom, you mentioned assigning the Max(Invoice) to the record using a query. When I try it i get the error that the field InvoiceNo is not being used as part of an aggregate function. Here's the code I tried: UPDATE UnitSales SET UnitSales.InvoiceNo = Max([InvoiceNo])+1 WHERE (((UnitSales.[STK#])=[Forms]![UnitSales]![STK#])); Also, how do you test for NewRecord? I've been checking to see if the key field is Null. "Tom Ellison" wrote: Dear Pip: There are usually other ways a record can be caused to be saved other than your "save" button. You need an event that covers all cases. I'm accustomed to using the BeforeUpdate event and testing for NewRecord. You could use the Max(InvoiceNo), obtained from a query, to assign this, or you could have a control table with a row for Next Invoice Number from which you get this. In multi-user situations it is usually recommended to use locking in either case while you do this. In extensive tests I've run, I could never get it to foul up, but it's theoretically possible. In a singlue user system, you have no worries with locking. Unless, of course, it get's installed later multi-user. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: No, Work orders are never deleted. They are recorded manually from printouts so the work order number is manually entered. I just need a listing of the missing work orders to enter any that may have been missed through the manual entry system. This system will only ever have one user for each location. ******************************* This second part is for a different program. Is there an easy way to test for a new record? These records aren't deleted after they are completed. However there are times that the invoice is started and then canceled for some reason to be entered later. Once the form is finished it can not be deleted. I'll probably use the BeforeUpdate event like you mentioned. However, how do I assign in code the maximum(InvoiceNo) to the field. Just use an update query called from the event? I could also call it from the 'save' button or is that not suggested? Cheers and thanks, Pip''n "Tom Ellison" wrote: Dear Pip: Are Work Orders ever deleted? If they are, do you want to assign the number of the deleted Work Order to the next one that is saved? Perhaps it would be good to wait to assign the new Work Order numbers until the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order Number at that time. Use the MAX() of the current work orders. In a busy multi-user system, it might also need some locking. I'm really thinking you don't want to fill in the gaps created by deletions. Perhaps you would consider not deleting such work orders, but just marking them. Depends on may factors of the design. Tom Ellison "Pip''''n" wrote in message ... Dear Tom: Currently I have a listing of [Work Order]s in a table called [Work Orders] i know the naming isn't the best but it was before I learned what I know now. This listing is used to create a bill each month. To prevent the possibility of missing work orders I need to list all missing work orders before I create the bill. Missing orders are then entered into the system. Currently I have a form to perform this task. I began by storing the min work order for the location. and Run a count+1 on the work order numbers. Opening a form to the desired record each time. If the record can't be found the number is concatenated to a string that is returned once the maximum work order is reached. Each time the code is run, the first missing work order - 1 is stored as the minimum work order for that location. The values stored a MinROMissed : starting value MaxRO : last entered work order [Current Invoice] : stores current invoice being checked [Location ID] : each store has a different range of work orders (values 1,2,3) It's a crude way of doing it, but it works. I'd just like to make it better. ********************************* In a separate system, I need to assign an InvoiceNo to a record upon completion of a sale. Currently I assign the maximum number + 1 but i know this could create problems if a number is assigned when opening this form, a second is opened and the first is cancelled. I would end up missing an invoiceNo in my sequence. Your assistance is greatly appreciated, Thanks in advance, Pip'n "Tom Ellison" wrote: Dear Pip: Showing all the missing numbers would be quite a different problem. You would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS can be made made against this list, limited by the largest value, if that's |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
is it possible to make gift certificates with sequential numbers? | GaryB | General Discussions | 1 | March 15th, 2006 02:30 AM |
General solution for missing sequence numbers | Peter Danes | Running & Setting Up Queries | 33 | December 8th, 2005 03:14 PM |
How do i identify missing numbers in a sequential list | Chet-a-roo | General Discussion | 4 | August 5th, 2005 07:25 PM |
sequential cell numbers?? | Gordon J. Rattray | Setting up and Configuration | 1 | May 27th, 2004 08:47 PM |
sequential cell numbers?? | Gordon J. Rattray | Charts and Charting | 1 | May 27th, 2004 08:47 PM |