Alphabetical order

[constantG]

Is there a way of changing records to align themselves in alphabetical order and change the DMax counter numbers assigned to those records to match?

i.e. (I use the DMax counter on the forms "before insert" event)

can I change the following (read in two columns "count & name"):

count - name
0001 - peters
0002 - butress
0003 - anderson
0004 - simons

to:

count - name
0001 - anderson
0002 - butress
0003 - peters
0004 - simons

If not then the people entering the data will have to sort it out themselves before entering it.
If it can be done then can I also use this for a daily input and not to the whole table?

In advance

 

[MarkK]

I don't see the purpose of storing the sequential numbers. What value does this additional effort add to any process or result that you generate? What is the payoff? Is there one?

 

[constantG]

I don't see the purpose of storing the sequential numbers. What value does this additional effort add to any process or result that you generate? What is the payoff? Is there one?

Maybe I didn't explain myself... here goes.

I am in the navy and whilst I, and my colleagues are away at sea we receive messages from our loved ones. each message must be sent in alphabetical order each day. I have created a database whereby the person sending the messages can input that information, however each message must be sequentially numbered (hence the DMax counter) and in alphabetical order for that day.
So when he inputs the data, they are sequentially numbered but if he doesn't sort them out first then they are out of alphabetical order.

Hope this helps.

Thanks

 

[constantG]

Just thinking,

Maybe there is a way of assigning the numbers to the messages once all messages have been entered for that day. If so would that be hard to incorporate (for a beginner)?

fingers crossed

 

[WayneRyan]

ConstantG,

There are a lot of details missing. You could just send the messages at the end
of the day using a query:

Dim Counter As Long
Dim rst As DAO.Recordset

Counter = 1
Set rst = CurrentDb.OpenRecordset("Select * From YourTable Order By Name)

While Not rst.EOF And Not rst.BOF
   '
   ' Right Now, you have:
   '    Message Number --> Counter
   '    Name           --> rst!Name
   '    Message        --> rst!Message
   '
   ' Then activate your email
   '
   Counter = Counter + 1
   rst.MoveNext
   Wend

If you really want to renumber the actual data:

Dim Counter As Long
Dim rst As DAO.Recordset

Counter = 1
Set rst = CurrentDb.OpenRecordset("Select * From YourTable Order By Name)

While Not rst.EOF And Not rst.BOF
   '
   rst.Edit
   rst!Count = Counter
   rst.Update
   '
   Counter = Counter + 1
   rst.MoveNext
   Wend

Note that "Count" and "Name" are reserved words and should be changed.

hth,
Wayne

 

[Atomic Shrimp]

Is the requirement to number the records one that is imposed by operational protocol, rather than database design?

 

[constantG]

Wayne,

The database does not actually send the messages, it is just a way for my staff to log them.

Your second example looks like it should do the trick. BTW I used "count" & "name" only as an example in the post. The actual fields on the form are "FamNumber" and "Surname"

Atomic, yes.

 

[Atomic Shrimp]

There have been some threads on this board on numbering query results in the past - I can't seem to find them at the moment - but you might like to try searching on terms including numbering, query, autonumber, results...

 

[MarkK]

It's still not clear to me that there is any value added to the process by creating these sequential numbers. If you need a list alpha sorted on last name then alpha sort the list on last name. How does having a seven in front of the seventh item improve the product?

 

[Atomic Shrimp]

It's still not clear to me that there is any value added to the process by creating these sequential numbers. If you need a list alpha sorted on last name then alpha sort the list on last name. How does having a seven in front of the seventh item improve the product?

It sounds like they need to be numbered to comply with some military protocol for sending messages - if that's the case, arguing that the number is redundant is probably about as useful as beating your head with a stick.

The messages need to be sent in alphabetical order of name, and they need to be presented in a numbered list, as far as I understand it. There may even be a valid reason for that - for example:
-- if there are two or more messages for the same name in one batch
-- if the message may invite valid modes of response by number or name depending on the situation ("This is a reply to the message sent to J Smith" vs "message 5 was garbled beyond recognition, please retransmit")

 

[MarkK]

- constantG has not stated anywhere that there is a specific requirement for sequentially numbering each item. He has stated he requires a list alpha sorted on last name.
- I have helped people here in the past who are spending a ton of time trying to maintain sequential numbering schemes for their lists out of some preconception that this is an important feature of a list. When pressed to explain why they need sequential numbers, they realize that they don't actually need them, and that the best solution to the problem is to abandon it.
- In a database there is not a strong concept of an ordered list. Any query typically returns a subset of records, a subset of fields, or a summary of many records or fields, and any sorting is applied only at the time the query is performed. That a particular record is, say, third in the list has no meaning in respect to the data in that record. In a multi-user environment maybe you run the query again the record no longer exists. Now a different record is third.
- The ordinal position of a record has no value. That Smith sorts ahead of Smythe has value.

 

[boblarson]

- constantG has not stated anywhere that there is a specific requirement for sequentially numbering each item. He has stated he requires a list alpha sorted on last name.

Then you missed it - go back and read post #3 again

however each message must be sequentially numbered (hence the DMax counter) and in alphabetical order for that day.

(size and bolding added by me for emphasis).

 

[boblarson]

Oh and sorry if that last message seemed harsh. It wasn't meant that way.

I also found this:

Is the requirement to number the records one that is imposed by operational protocol, rather than database design?

Atomic, yes.

 

[MarkK]

No worries Bob, but you've also only responded to the first line of my post. I'm still curious if people have thoughts or observations about sequentially numbered lists.
Cheers,

 

[JANR]

There have been some threads on this board on numbering query results in the past - I can't seem to find them at the moment - but you might like to try searching on terms including numbering, query, autonumber, results...

Stephen Lebans has a samplebase to add sequencial numbers to queries and forms, perhaps this is what you or the OP where looking for

http://www.lebans.com/rownumber.htm

JR

 

[boblarson]

No worries Bob, but you've also only responded to the first line of my post. I'm still curious if people have thoughts or observations about sequentially numbered lists.
Cheers,

Yes, I wouldn't, as a general rule, advocate for sequential numbering of records (invoice numbers would be an exception). But even at that, I would not reorder them on a normal basis.

However, given certain operational directives, sometimes you gotta do things out of the norm and this is one of those cases. Wayne's code should do the trick, if I read it correctly.

 

[Atomic Shrimp]

No worries Bob, but you've also only responded to the first line of my post. I'm still curious if people have thoughts or observations about sequentially numbered lists.
Cheers,

I agree that numbering an alphabetic list of unique items is in many cases redundant, however, what would be redundancy in purely electronic data often becomes a necessity when it has to go out into meatspace.

For example, with a printed, non-numbered alphabetic list of names used for organising transmitted radio messages, one message is garbled in the process - the sending radio operator will only know the alphabetic name, the recieving radio operator (who didn't get the message clearly) will only know that it's the 39th message, this then means that either:

-The receiving operator says "message 39 was garbled - please retransmit" - so the sending operator has to count down 39 rows in his printed report.
Or
-The receiving operator has to specify "Message after Smith and before Sykes was garbled, please retransmit" - which is a complex instruction, prone to misunderstanding.

 

[constantG]

Hi,

Sorry it's taken so long to get back to you all, btw thanks for all your support. Atomic shrimp is totally correct in that the messages may be referred to by the people receiving them and that the system which sends the messages has no connection with the database. Each person receives a message roughly about once a week and each one needs to be sequentially numbered from 0 at the start of the boats' deployment until the end (roughly about 3000 messages). I will check out all your suggestions and will definitely get back to you all and let you know how I get on.

Again, thanks. Your passion on all things access is second to none on the interweb

 

[constantG]

BTW, I can't get wayne's idea to work, not really sure what I need to replace in your code. My table is called "famgram_input_table" the number field is "FamNumber" and the name field is "Surname"

Where would I place this code on a command button?

 

[WayneRyan]

ConstantG,

Yes, put it on a command button ... while you have the code in Design View:

Tools --> References

and make sure that the Microsoft DAO DLL is selected.

Dim Counter As Long
Dim rst As DAO.Recordset

Counter = 1
Set rst = CurrentDb.OpenRecordset("Select * From famgram_input_table Order By Surname)

While Not rst.EOF And Not rst.BOF
   '
   rst.Edit
   rst!FamNumber = Counter
   rst.Update
   '
   Counter = Counter + 1
   rst.MoveNext
   Wend

hth,
Wayne