Numbering a field in a query

  • Thread starter Thread starter Jim Baker
  • Start date Start date
J

Jim Baker

I have a query which involves about 40 members. Each of the members needs
a membership number. The list is sorted in aphabetical order and therefore
manually
consecutively numbered by me.
Every so often the membership changes and I need to renumber the
membership list automatically, at the moment I still have to do it manually.
I do not want to renumber at the report stage as too many other factors
rely on the query been initially correct.
Any ideas
Many thanks
Jim
 
I have a query which involves about 40 members. Each of the members needs
a membership number. The list is sorted in aphabetical order and therefore
manually
consecutively numbered by me.
Every so often the membership changes and I need to renumber the
membership list automatically, at the moment I still have to do it manually.
Click to expand...

Now wait a minute.

Does that mean that Ralph Zostrel will need to use Member number 39
one week, 41 the next week because two new members joined, 40 the week
after becuase Jim Aarons moved away?

Whate is the POINT of this number? If you want to count members, count
members; if you want a unique, stable, predictable membership ID
assign one (manually or programmatically and then DON'T CHANGE IT!
 
The POINT of the number is it is a members draw number, which is held
each month and is therefore only temporarily allocated if you like.
So unique numbering is NOT an option!
Click to expand...

I'd suggest a different approach: don't store the number at all.
Instead calculate it on the fly in a Query using an expression:

DrawNumber: DCount("*", "[yourtable]", "[LastName] & ' ' & [FirstName]
<= '" & [LastName] & " " & [FirstName] & "'")
 
Hi John
Yes, I tried out the expression, the only problem is that the only table I
have refers to all members,
past and present, with a current member tick box, so the resultant numbering
is a bit random,
unless I can create a table of current members only from the existing table,
which seems like a good idea, but how would I do that if it is feasible.
Or if 'CurrentMember' in Table1 (which is a Yes/No box) can be incorporated
in the
query with your expression, perhaps that would be a better idea.
Sorry if this seems a bit basic, but I am anxious to crack this one.
Many thanks
Jim

DrawNumber: DCount("*", "[Table1]", "[LastName] & ' ' & [FirstName]
<= '" & [LastName] & " " & [FirstName] & "'")
Click to expand...



John Vinson said:
The POINT of the number is it is a members draw number, which is held
each month and is therefore only temporarily allocated if you like.
So unique numbering is NOT an option!
Click to expand...

I'd suggest a different approach: don't store the number at all.
Instead calculate it on the fly in a Query using an expression:

DrawNumber: DCount("*", "[yourtable]", "[LastName] & ' ' & [FirstName]
<= '" & [LastName] & " " & [FirstName] & "'")
Click to expand...
 
Excellent John,
The only problem left is although the query is now correctly numbered,
the format is single 0, can I add a FORMAT command to your
expression, otherwise I am getting the sequence of numbers misplaced because
of the
single number format.
Many thanks for your help so far.
Very grateful
Jim


John Vinson said:
Or if 'CurrentMember' in Table1 (which is a Yes/No box) can be incorporated
in the
query with your expression, perhaps that would be a better idea.
Click to expand...

It can, easily. The third argument to DCount() is just a Query string
and can be almost arbitrarily complex; two criteria is a piece of
cake:

DrawNumber: DCount("*", "[Table1]", "[LastName] & ' ' & [FirstName]
<= '" & [LastName] & " " & [FirstName] & "' AND [CurrentMember] =
True")
Click to expand...
 
Hi John,
Yes, in the report the numbering goes 1, 10, 11.....19, 2, 20, 21 ...... 29,
3, 30, 31 etc
regardless of how I format the Draw number Box.
The query reads 1, 2, 3, 4,.........9, 10, 11 etc
Hope that is a bit clearer for you!!
Jim


"John Vinson" <jvinson@STOP_SPAM.WysardO
fInfo.com> wrote in message
 
Hi John,
Yes, in the report the numbering goes 1, 10, 11.....19, 2, 20, 21 ...... 29,
3, 30, 31 etc
regardless of how I format the Draw number Box.
The query reads 1, 2, 3, 4,.........9, 10, 11 etc
Hope that is a bit clearer for you!!
Click to expand...

How are you sorting the Report? What is its Recordsource - the query
directly?

What's happening, somehow, is that you're converting the sequential
number to Text and sorting that text field. The Format() function does
this, so that's what I'd look for, perhaps in the Report's Sorting and
Grouping dialog.
 
Hi John
Yes, the record source in the report was the query directly, which is where
I thought the problem
really lied. So I went back to one of your earlier suggestions in the format
of the Draw Number field, ie

<<You can, indeed, wrap the entire expression in a Format(<expr>,
"0000") function call if you want (say) four digits with leading
zeros. This will be a Text value not a numeric one, but it should sort
correctly>>

the query output then became 01, 02, etc and the report sequence then came
out right as well.
I could not really work out how to format in the Reports Sorting and
Grouping dialog.
However all seems OK now. I have learned a lot from you John, and am very
grateful.
Thanks for your patience.
Best Wishes
Jim
 
Back
Top