 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Record Numbering
In my order entry query I would like to have a column numbering each row starting with 1 and continue on through the items until the next order number is reached Order number changes. I need this because this information will be exported and imported in to our accounting program, and that is the format it has to be in to import it. Any ideas? shannaj What you need to do is make a field in the table that autonumbers and make sure in the query that it is part of that query/form. this will authomatically give you a number each time something new is added. Judy > In my order entry query I would like to have a column numbering each row > starting with 1 and continue on through the items until the next order number > is reached Order number changes. I need this because this information will > be exported and imported in to our accounting program, and that is the format > it has to be in to import it. Any ideas? But if I do this, it won't start back at 1 when the order number changes. Right? >shannaj > [quoted text clipped - 8 lines] >> be exported and imported in to our accounting program, and that is the format >> it has to be in to import it. Any ideas? Are your orders and order details in the same table? If so, it will be quite difficult, if it is possible at all, to achieve what you want. A better choice is to have the orders in one table (OrderNumber, OrderDate, and so forth), with the Order line items in a separate OrderDetails table. If you do it that way you can use the DMax function to number each line item starting with 1 for each new order. Unless that is how you are going to approach this I won't go into a lot of details. In any case I don't have much time today to respond. However, in general you could have something like this in the subform's Current event: If Me.NewRecord Then Me.LineNumber = Nz(DMax("LineNumber","tblOrderDetails","OrderID = " & Me.OrderID),0) + 1 End If > But if I do this, it won't start back at 1 when the order number changes. > Right? [quoted text clipped - 14 lines] >>> format >>> it has to be in to import it. Any ideas? It makes no difference to computing the line number whether the data is in one table or two, though the former would not be fully normalized of course . With a single table the combination of order number and line number is a candidate key so the DMax function is called in exactly the same way. However, unless there is a specific need to preserve the original line numbers if an item is deleted from an order computing the values on the fly in a query as Steve describes is a far better solution. The result table of the query can then be exported as the OP wants. Its also worth noting that in a multi-user environment conflicts can arise if the next number is generated by calling the DMax function, should two or more users be adding items to an order simultaneously. Its imperative therefore that a unique index be created on the order number and line number columns. The data error which would arise in the case of a conflict can be handled in the form's Error event procedure, or the error can be avoided by using the method popularised by Ken Getz et al in the ADH of storing the last used number in an external database which can only be opened exclusively by each user. I did produce a little demo of this some years ago. The next number is generated by the following function. The demo numbered males and females separately, hence the name of the function; nothing to do with an orgy enrolment system; Public Function GetNextNumberForSex(strCounterDb As String, strSex As String) As Long ' Accepts: Full path to database containing tblCounter table with ' long integer column NextNumber and text column Sex. ' Sex ('M' or 'F') for which next serial number to be obtained ' Returns next number in sequence for specified sex ' if external database can be opened and number obtained. ' Returns zero if unable to get next number. Const NOCURRENTRECORD As Integer = 3021 Dim dbs As DAO.Database, rst As DAO.Recordset Dim n As Integer, I As Integer, intInterval As Integer Dim strSQL As String strSQL = "SELECT * FROM tblCounter WHERE Sex = """ & strSex & """" ' make 10 attempts to open external database exclusively DoCmd.Hourglass True SysCmd acSysCmdSetStatus, "Attempting to get new number" On Error Resume Next For n = 1 To 10 Err.Clear Set dbs = OpenDatabase(strCounterDb, True) If Err = 0 Then Exit For Else intInterval = Int(Rnd(Time()) * 100) For I = 1 To intInterval DoEvents Next I End If Next n SysCmd acSysCmdClearStatus DoCmd.Hourglass False If Err <> 0 Then GetNextNumberForSex = 0 Exit Function End If Err.Clear Set rst = dbs.OpenRecordset(strSQL) With rst .Edit ' insert new row if no existing record for this sex If Err = NOCURRENTRECORD Then .AddNew !Sex = strSex !NextNumber = 1 .Update GetNextNumberForSex = 1 Else ' update row and get next number in sequence !NextNumber = !NextNumber + 1 .Update GetNextNumberForSex = rst!NextNumber End If .Close End With End Function Ken Sheridan Stafford, England > Are your orders and order details in the same table? If so, it will be > quite difficult, if it is possible at all, to achieve what you want. A [quoted text clipped - 29 lines] > >>> format > >>> it has to be in to import it. Any ideas? I responded to steer the OP away from the use of autonumber. There was only one other response at the time. The OP already understood the limitation of autonumber, so I guess I should have left the question to somebody else. Of course you are correct that DMax would work the same way whether or not the data are more fully normalized. I have gotten around the possiblility of conflicts in a multi-user system by assigning the value provisionally, then confirming it in the form's Before Update event. If there is no need for the user to see the line number, the provisional initial assignment can be skipped. I understand the point about the potential conflicts in a multi-user system, but I think that would apply more to the Order number than to the line numbers, since it seems unlikely several users would be entering line items into the same order at the same time. That being said, I had not considered what would happen if a line is deleted from the order. I expect the original line number would not be preserved. Renumbering is possible (I have done so in a situation where the order matters), but not the best choice if the order of lines is irrelevant. I am not well-acquainted with ranking subqueries, but will do some experimenting based on Steve's example now that I am getting a better handle on SQL. I sort of see what is going on with the function to grab the next number. It is of interest to me in that it may work better than my DMax with error handling system for numbering purchase orders. Do I understand correctly that NextNumber is the only field in tblCounter, which contains one record? One part I'm not getting (assuming I understand so far) is this: intInterval = Int(Rnd(Time()) * 100) For I = 1 To intInterval DoEvents Next I I get that this returns a value that I suppose could be from 0 to 99, but why a random value. Alse, what events are occurring that DoEvents is needed? > It makes no difference to computing the line number whether the data is in > one table or two, though the former would not be fully normalized of [quoted text clipped - 140 lines] >> >>> format >> >>> it has to be in to import it. Any ideas? As regards the use of the Rnd function it randomizes the timing of the loop reducing the, albeit already small, possibility of a conflict at an iteration of the loop. The code essentially behaves in the same way as the function published by Ken Getz et al, though I simplified it. The DoEvents function is called to yield execution to the OS as is normal in such timing loops. What happens if an item is deleted from an order really depends on what the function of the number is in the first place. If its used outside the database to refer to an item e.g. item 6 of order number 99 then you would probably not want to renumber the items if one is subsequently deleted. I can't see any reason other than this why it would be stored in a column, so if the number is being stored then it should not change. If renumbering is allowed then it makes storing it in a column unnecessary as the item numbers can always be computed. On the subject of 'ranking' queries you might be interested in the following which is my standard response to the question regularly posed as to how to sequentially number rows, usually financial transactions, where each transaction is recorded by date, but without distinguishing between multiple transactions on the same day. It relies on there being a unique key column, an autonumber TransactionID in this case, but there can be gaps in the autonumber sequence and all transactions on one day don't have to be entered as a block; they can be entered in any order e.g. with some of yesterday's being entered yesterday and some tomorrow, with today's being entered today: SELECT (SELECT COUNT(*) FROM Transactions AS T2 WHERE T2.TransactionDate <= T1.TransactionDate AND ( T2.TransactionID <= T1.TransactionID OR T2.TransactionDate <> T1.TransactionDate)) AS RowCounter, T1.TransactionDate, T1.TransactionID FROM Transactions AS T1 ORDER BY T1.TransactionDate, T1.TransactionID; The subquery is correlated with the outer query on the transaction date being the same or before the outer query's current transaction date. This would be enough if there were only one transaction per day, but as there can be more than one it is further correlated by the autonumber transactionID being the same as or less than the outer query's current transactionID or the transactions not being on the same day. The OR operation is parethesised to force it to evaluate independently of the AND operation. Without the OR operation it would be necessary for the TransactionID values within each day to be sequential without any intervening values in the sequence for one day appearing in a row for an other day, which can't be guaranteed. This in effect orders the rows by transaction ID within each transaction date. Ken Sheridan Stafford, England > I responded to steer the OP away from the use of autonumber. There was only > one other response at the time. The OP already understood the limitation of [quoted text clipped - 178 lines] > >> >>> format > >> >>> it has to be in to import it. Any ideas? Regarding sequential numbering, in my case it was necessary to have a checklist be in a particular order. If the order of checklist items is entered incorrectly it is necessary to change the order. The only way I could think of to do that was to number the row, and order by that field. Regarding the ranking query, it seems to be based in part on the TransactionID representing the order in which records are entered, although I certainly may not be reading that correctly. I mention it because I have heard that sequential autonumber is not a reliable indicator of order, although I have not seen evidence of why this would be so. I expect there are things about this SQL I do not understand, and maybe I will discover more when I can experiment with this for a while, but I guess would have expected a <> comparison on TransactionID. Anyhow, thanks for all of the information, and for taking the time to write it down in detail. I really appreciate it, and will conduct my own experiments as soon as I can. > As regards the use of the Rnd function it randomizes the timing of the > loop [quoted text clipped - 282 lines] >> >> >>> format >> >> >>> it has to be in to import it. Any ideas? >Regarding sequential numbering, in my case it was necessary to have a >checklist be in a particular order. If the order of checklist items is >entered incorrectly it is necessary to change the order. The only way I >could think of to do that was to number the row, and order by that field. If you need to define an arbitrary order, then yes, that's the best bet. You may want to leave gaps if there might be new checklist items and if you have an integer sequence - that is, you could have sequence numbers like 10, 20, 30, ..., 120 and still be able to slip in 25 and 55. >Regarding the ranking query, it seems to be based in part on the >TransactionID representing the order in which records are entered, although >I certainly may not be reading that correctly. I mention it because I have >heard that sequential autonumber is not a reliable indicator of order, >although I have not seen evidence of why this would be so. Two reasons: for one, an Autonumber will essentially always have gaps. If you expect 1, 2, 3 order you may be disappointed! Not only will deleted records leave a gap (which won't get filled in), but even starting to enter a new record and hitting <ESC> <ESC> to cancel it will "use up" an autonumber value. The gapped autonumbers will still be sequential, though. However, if you ever use Database Replication - such as by copying the database to a "Briefcase" - all the autonumbers will become Random rather than Sequential. A random autonumber will have on unpredictable value between -2^31 and +2^31-1 - two billion odd either way. One record might have 1451227416 and the next record -824116815.  SignatureJohn W. Vinson [MVP] In your case you clearly do need to store the numbers and these can't be auto-generated as the values cannot be derived from other data or from when a row is inserted. If the latter were the case then a time-stamp would be the best bet. With my sample transactions query the use of the autonumber guarantees that the numbering is sequential, and that transactions for each day appear in the correct positions in the sequence in relation to other days' transactions. You are quite correct in saying that it does not absolutely guarantee that transactions within each day will be in the order in which they were inserted into the table. In practice they normally will be, but it is possible to insert a value into an autonumber field manually with an 'append' query so, while autonumbers (other than when random ones are used as John has pointed out) will generally be sequential (again as John has pointed out), but not necessarily in an unbroken sequence, this can be subverted by the user. This is unlikely of course, but one should always be conscious of Murphy's Law and assume that if something can go wrong, sooner or later it will go wrong. The sample query was originally written to provide a solution for people who wanted to number rows, but had only recorded each transaction by date, not date and time; I always take pains to point out to them that if they want the granularity of the numbering to be by transaction entry time rather than by transaction date they should either time-stamp each row or generate a guaranteed sequential TransactionID. In either case the column must be uniquely indexed of course. As regards your statement "I guess would have expected a <> comparison on TransactionID" that would reduce each number by 1 as the outer query's current row would not be included in the count. If we look at the WHERE clause expression by expression: T2.TransactionDate <= T1.TransactionDate This evaluates to True for any row with a date on or before the outer query's current row's date. As there may be multiple transactions on that date, however, the count of rows would give the same number for all rows on the date, with gaps in the overall numbering. This would actually be what's required in a race for instance where competitors tying with the same times would all be given the same position; but its not what's wanted here so this expression is combined with the following in a Boolean AND operation: T2.TransactionID <= T1.TransactionID This evaluates to True if the TransactionID is the same or less than the outer query's current row's TransactionID. Consequently only rows up to and including the current TransactionID are counted, giving separate numbers for each transaction on the same day. If it could be guaranteed that the transactions for each day are entered together then this would be enough, but its possible that a transaction for today might be entered later, after transactions for tomorrow or later, so the TransactionID would not be <= the outer query's current TransactionID and a relevant row would not be counted. To ensure that it is counted the above expression is combined with the following one, which evaluates to True if the transaction is on a different date to the outer query's current transaction, in a Boolean OR operation, thus causing the parenthesised expressions to evaluate to True if either criteria is satisfied: T2.TransactionDate <> T1.TransactionDate Ken Sheridan Stafford, England > Regarding sequential numbering, in my case it was necessary to have a > checklist be in a particular order. If the order of checklist items is [quoted text clipped - 285 lines] > >> >> >>sure in the query that it is part of that query/form. this will > >> >> >>authomatically give you a number each time something new is added. Thnaks to you and to John for all of the added information. I understand that almost inevitably there will be gaps in the numbering with autonumber. I think I remember having heard that replication will cause autonumber to be unreliable as the record order, and that an update query can break the sequence. As I understand it, in a simple database (and certainly for purposes of the example) the autonumber can be used as a sequence number. If the sequence is set by a time stamp or a code-generated sequential number, that field can be used instead of the autonumber field. Thank you very much for the extra explanation about the ranking query. I have done some experimenting, and have discovered that (to use your example) T2.TransactionID <= T1.TransactionID in the OR part produces the same results as T2.TransactionID >= T1.TransactionID. That was the result I expected before I conducted the experiment. Based on your explanation of the OR, it makes sense. Also, using T2.TransactionID <> T1.TransactionID, as I had wondered about, produces a list that starts with 0. I noticed, as you said, that using just T2.TransactionDate <= T1.TransactionDate produces a list with ties given the same number, although if there is a three-way tie for third the order will be 1, 2, 5, 5, 5, 6 rather than 1, 2, 3, 3, 3, 6. I expect that can be resolved if the situation arises, but I won't worry about it for now. I'm quite satisfied with what I have learned in this exchange. After a while the concepts will settle into my brain so that I don't need to spend so much time understanding it; then I will be ready to expand on it. I really appreciate the time you and others have spent on the explanations. I have made a big step forward as a result. > In your case you clearly do need to store the numbers and these can't be > auto-generated as the values cannot be derived from other data or from [quoted text clipped - 416 lines] >> >> >> >>authomatically give you a number each time something new is >> >> >> >>added. The 'race results' query is in fact very simple; you just count all the runners with times less than each runner and add 1. The place of a runner behind a group of tied runners will be adjusted down, so if two runners tie for 5th place say, the next runner will be in 7th place: SELECT Runner, RunTime, (SELECT COUNT(*)+1 FROM Marathon As M2 WHERE M2.RunTime < M1.RunTime) AS Place FROM Marathon As M1 ORDER BY RunTime; Ken Sheridan Stafford, England > Thnaks to you and to John for all of the added information. I understand > that almost inevitably there will be gaps in the numbering with autonumber. [quoted text clipped - 285 lines] > >> >> > is > >> >> > a Thanks again. That works. In my earlier tests the tie for fifth place showed as a tie for sixth place, and there was no fifth place. Using < rather than <= is the difference, which seems to make sense now that I think about it. > The 'race results' query is in fact very simple; you just count all the > runners with times less than each runner and add 1. The place of a runner [quoted text clipped - 365 lines] >> >> >> > is >> >> >> > a Shannaj, You will need to use a ranking subquery in your query. To give a general idea, the SQL of your query will be something like this: SELECT [Something], [Something Else], (SELECT Count(*) FROM [YourTable] AS T1 WHERE T1.[OrderNumber] = T2.[Order Number] AND T1.[OrderLine] <= T2.[OrderLine]) AS RowNumber FROM [YourTable] AS T2 ORDER BY [OrderLine] SignatureSteve Schapel, Microsoft Access MVP > But if I do this, it won't start back at 1 when the order number changes. > Right? Thank you so much for your help! >Shannaj, > [quoted text clipped - 9 lines] >> But if I do this, it won't start back at 1 when the order number changes. >> Right? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.