Update Query 1

[badcode]

I am trying to a simple update query. I have over 1000 records but I need to assign sorted records a sequential value example
------------------------------
Town | Area | Location |
------------------------------
01 | 04 | 01
03 | 01 | 02
06 | 09 | 05
01 | 03 | 02
06 | 01 | 02
01 | 01 | 01

needs to become
------------------------------
Town | Area | Location | Value
------------------------------
01 | 04 | 01 | 01
01 | 03 | 02 | 02
01 | 01 | 01 | 03

Any suggestions

 

[randy700]

Add an autonumber field.
Your value column will be updated immediately.


Randy

 

[randy700]

That is to say, make the value field type Autonumber.


Randy

 

[badcode]

Autonumber does not work The sequence in the example starts at one but it may start at any number

 

[shannonp]

Maybe try a coding route instead?:

    Dim myDB As DAO.Database
    Dim myRS As DAO.Recordset
    Dim mySort As Long
    
    Set myDB = CurrentDb()
    Set myRS = myDB.OpenRecordset("Select * From [blue][b]myTable[/b][/blue] [!]Order By Town, Area DESC[/!]")
    
    mySort = 1
    
    With myRS
        Do While Not .EOF
            .Edit
            ![blue][b]Value[/b][/blue] = mySort
            mySort = mySort + 1
            .Update
            .MoveNext
        Loop
        .Close
    End With
    
    Set myRS = Nothing
    Set myDB = Nothing

Above assumes [blue]myTable[/blue] includes the fieldname "[blue]Value[/blue]". The [!]Order By[/!] clause can be changed depending on how you want your sort [!]Value[/!] to be sequenced.

My code can probaby be improved on but hopefully its what you were looking for.


Shann

 

[MacroScope]

Why are you using an Update Query? Are you changing the values?

To accomplish what you want to see, use a Select Query with totals, and use Group By on the first number (Town). All the 01's etc. will be together. Set both the second field (Area) and the third field (Location) to Sort Ascending. Then use the query results any way they work best for you.

 

[badcode]

I will go the programming route. I am changing the values in the 'value' field as they are blank and need a sequential number assigned based on their town.

 

[MacroScope]

Suit yourself. Once they're in the order you want, assigning sequential numbers is a snap. Just use that query as the basis for another Make Table Query and add an autonumber field. Presto! Sequential numbers.

And if you want them to start at 1 and the second time you run it they start at some other number, you base a third query on the table you just made and just subtract whatever number from each of the sequential numbers that brings it back to where you want it.

Code will do it, but it seems a pain in the ass to me. I could have this done in about 20 mins or less.

Ultimately it's the data, not the method that is important.

 

[badcode]

I would rather do a query but I don't always know the starting number. It may start with 1 or some other number based on a previous run. For example the first run may be 1...137 the second run may be 234...1896 etc.

 

[shannonp]

If you connect code (above) to a button on a form and replace mySort with an unbound field you can easily change the starting value on-the-fly.

You could also look for the highest value from a previous sequence run (perhaps using a simple DMax), you could then (if desired) assign this value as your next default starting number.

I agree that the programming route can be a "pain in the ass" but it has so much flexibilty and scope and ensures a persons ever changing needs can be met without too much hassle


Shann

 

[MacroScope]

Let's say the run returns the numbers 375-1000, but you want them to start with 275.

You simply run an update query and you set the value of the numbering field to [Numbering]-100. It'll update every number in the group to what you want to see.

 

[shannonp]

That'll work


Shann

 

[badcode]

MacroScope your update query worked sort of. It turns out that some of the numbers are duplicate (mis keyed) or are missing so I did not get a clean sequential run. Is there anyway it could accept an entered value and then sequentally assign the values?

 

[MacroScope]

There's no way to get duplicates or missing numbers if all the fields were updated to an autonumber field. Autonumbers by design do not repeat, and each new record is automatically assigned the next number higher than the previous highest number used. Missing numbers would result only if some of the data is not being transferred to the table doing the autonumbering. The only way I can imagine a duplicate value is if there was already a value in the field of the specific record that was never transferred.

Compare the number of records that you started with and the number that result from the autonumber update. There must be a difference to account for the discrepancies you mentioned. Once you've identified and corrected the discrepancy just try again. There's absolutely nothing to stop it from working unless somehow a part of the data is not being autonumbered.