Numbering Records

[Guest]

Hi, I have a table with fields Last_Name and First_name. This table I will be
adding names to in the future. I want to somehow create a query/macro/process
that will sort the names by Last_Name then by First_Name and THEN
'Autonumber' them. The results can go to another table.

 

[Rick B]

Why? The order of records in a table is not relevant. Saving them in two
tables is redundant. What happens when you add a new record - will the
record that used to be number 15 now be number 16 - if so, the numbers are
of no value to you.

What are you actually trying to accomplish as the end result? It can be
done without the need to make a new table and number the records.

 

[BruceM]

Create a query. Add Last_Name and First_Name left to right in the query
design grid. Select Ascending as the Sort choice. Add whatever fields you
wish.

Now the names are sorted. Do not put them into another table. If you do,
you are missing the point and the advantage of a relational database. What
is the idea behind the numbering?

 

[Guest]

k, so the end-result is a report grouped by the Last_Name in alphabitical
order, with various record entries grouped by each last name. What I'm trying
to do is a running sum 'per' Last_name (or 'per group'). The problem with
doing it within the report is that each Last_Name has or may have 'duplicate
entries'...so it either does a running sum of each entry or just run sums the
entries per group and starts over each group. I want it to look like this:

1 Able,Roy Design div 04a13 Yes
Design div 04121 No
Design div 03415 Yes
2 Avis, Ray Human Res 03124 Yes
3 Bob, John Resources 0555 No

Problem part 2 is that I also need to be able to ADD names to this list and
I wish to set it up by letting people enter the info into a subform then
clicking on a box which will append details to appropriate tables. This is
where I need some process to sort again alphabitically and renumber based on
were they fall alphabitically. For example, if I added "Ackor, Ray" to the
above list:

1 Able, Roy Design div 04a13 Yes
Design div 04121 No
Design div 03415 Yes
2 Ackor, Ray Architecture 05553 No
3 Avis, Ray Human Res 03124 Yes
4 Bob, John Resources 05556 No

This is one of those picky things that the bosses want which doesnt make
'access' sense. So thanks for responding and any more help is greatly
appreciated. Ray

 

[Marshall Barton]

Do it in the report. put the =1 RunningSum text box in
person header section. The rest is automatic.

 

[Guest]

I do not see anything in your report that appears to be a sum.

Putting things in alphabetical order can be done in your report by Grouping
And Sorting. Then set the name field Hide Duplicates property to Yes.

You might want to hide the duplicates in the next column also. I would look
like this --

 

[Guest]

Sorry, wrong click --
Able, Roy Design div 04a13 Yes
04121 No
Human Res 03415 Yes
Ackor, Ray Architecture 05553 No
Design div 04413 Yes
Avis, Ray Human Res 03124 Yes
Bob, John Resources 05556 No

If you need to place a number in the name line the search Report Numbering.