Create ID with consecutive numbers by month

  • Thread starter Thread starter rocketD
  • Start date Start date
R

rocketD

Hello,

I have a database of participants in a research study, and we have a
numbering convention to make sure each person gets a unique identifier
that tells us some information. The numbers are like this:
39901001, where "3" indicates the study, "9901" indicates yymm
participant was added to the database, and "001" is the consecutive
number of the participant to be added for that particular yymm. For
example, if a person was the third to be added in Feb. 99, it would be
39902003.

What I want to do is avoid mistakes by having users with limited
Access capability use a form where the ID auto-generates. I can
figure out how to get the information for the first 5 numbers entered,
but not the consecutive numbering by yymm. Here's what I have
specified in the field now:

=3 & Format(Now(),"yy") & Format(Now(),"mm") which gives 30911, and
that's great....but now I need the three digit consecutive number
(which means it has to have leading zeros if under 100). Ostensibly,
I want to get a count of the folks already entered for the current
month and year, then add 1. That way, it would keep increasing
incrementally with every new record. I tried a query but can't get it
to work.

Any ideas for the next step, or a better way?

Thanks,
Dara
 
What you're describing is a so-called "smart key", and the use of smart keys
is generally considered to be a bad idea.

In essence, you're trying to hide multiple pieces of information in a single
field: something that violates database normalization principles.

You should store the three pieces of information as three separate fields in
the table and create a query that returns all of the data plus a calculated
field that concatenates the three fields together. You'd then use the query
wherever you'd otherwise have used the table.
 
What you're describing is a so-called "smart key", and the use of smart keys
is generally considered to be a bad idea.

In essence, you're trying to hide multiple pieces of information in a single
field: something that violates database normalization principles.

You should store the three pieces of information as three separate fieldsin
the table and create a query that returns all of the data plus a calculated
field that concatenates the three fields together.  You'd then use the query
wherever you'd otherwise have used the table.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)











- Show quoted text -
Click to expand...

Thanks for your suggestion. I understand what you're saying - right
now I have fields called yearAdded and monthAdded which autopopulate
with
=Format(Now(), "yy")
whenever a person adds a new participant. I am not averse to adding a
field that stores the cumulative count of people as they are added
each month, and then concatenating the fields in the query; I was
actually trying to describe doing something close. The problem is, I
don't know how to get that consecutive number restarting each month.
Am I making sense? What the table might look like is this:
FName LName yrAdd moAdd numAdd
Jane Doe 09 11 1
Jim Smith 09 11 2
Chicken Little 09 11 3
Lucy Price 09 12 1 .... etc.

Then I would need to concatenate those last three fields, with a 3 at
the front and 3 digits in the last number (e.g., leading zeros). Is
there a way I can do this automatically? If I am having people count
up the number of people added every time they enter someone, it's just
begging for a mistake.

Thanks,
Dara
 
I'm assuming yrAdd and moAdd are both numeric.

"3" & Format(yrAdd, "00" & Format(moAdd, "00") & Format(numAdd, "000")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What you're describing is a so-called "smart key", and the use of smart
keys
is generally considered to be a bad idea.

In essence, you're trying to hide multiple pieces of information in a
single
field: something that violates database normalization principles.

You should store the three pieces of information as three separate fields
in
the table and create a query that returns all of the data plus a
calculated
field that concatenates the three fields together. You'd then use the
query
wherever you'd otherwise have used the table.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)











- Show quoted text -
Click to expand...

Thanks for your suggestion. I understand what you're saying - right
now I have fields called yearAdded and monthAdded which autopopulate
with
=Format(Now(), "yy")
whenever a person adds a new participant. I am not averse to adding a
field that stores the cumulative count of people as they are added
each month, and then concatenating the fields in the query; I was
actually trying to describe doing something close. The problem is, I
don't know how to get that consecutive number restarting each month.
Am I making sense? What the table might look like is this:
FName LName yrAdd moAdd numAdd
Jane Doe 09 11 1
Jim Smith 09 11 2
Chicken Little 09 11 3
Lucy Price 09 12 1 .... etc.

Then I would need to concatenate those last three fields, with a 3 at
the front and 3 digits in the last number (e.g., leading zeros). Is
there a way I can do this automatically? If I am having people count
up the number of people added every time they enter someone, it's just
begging for a mistake.

Thanks,
Dara
 
If, as Doug Steele suggest, you store the values into separate feels
(which is an excellent suggestion), you can calculate how many people
have already been added in the current month with this expression:

DCOUNT("*","participants","[yearAdded] = " & format(now(),"yy" & " and
[monthAdded] = " & format(now(),"mm")

Increment this value by one when you add a new participant. When
displaying it, use format([value],"000") to get the leading 0's.

Noah
 
If, as Doug Steele suggest, you store the values into separate feels
(which is an excellent suggestion), you can calculate how many people
have already been added in the current month with this expression:

DCOUNT("*","participants","[yearAdded] = " & format(now(),"yy" & " and
[monthAdded] = " & format(now(),"mm")

Increment this value by one when you add a new participant.  When
displaying it, use format([value],"000") to get the leading 0's.

Noah

Hello,
Click to expand...
I have a database of participants in a research study, and we have a
numbering convention to make sure each person gets a unique identifier
that tells us some information.  The numbers are like this:
39901001, where "3" indicates the study, "9901" indicates yymm
participant was added to the database, and "001" is the consecutive
number of the participant to be added for that particular yymm.  For
example, if a person was the third to be added in Feb. 99, it would be
39902003.
Click to expand...
What I want to do is avoid mistakes by having users with limited
Access capability use a form where the ID auto-generates.  I can
figure out how to get the information for the first 5 numbers entered,
but not the consecutive numbering by yymm.  Here's what I have
specified in the field now:
Click to expand...
=3 & Format(Now(),"yy") & Format(Now(),"mm")   which gives 30911, and
that's great....but now I need the three digit consecutive number
(which means it has to have leading zeros if under 100).  Ostensibly,
I want to get a count of the folks already entered for the current
month and year, then add 1.  That way, it would keep increasing
incrementally with every new record.  I tried a query but can't get it
to work.
Click to expand...
Any ideas for the next step, or a better way?
Click to expand...
Thanks,
Dara- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Oh, that worked very well! I can't get it to work in the table, it
doesn't recognize the DCount function as a valid entry for the Default
Value property of a field, but I put it in the form, which is the only
place most users will ever be able to access anyway.

In the Default Value property of the form ID field, here is what I
came up with:
=3 & Format(Now(),"yy") & Format(Now(),"mm") & Format((DCount
("*","Questionnaire","[yearAdded] = " & Format(Now(),"yy") & " and
[monthAdded] = " & Format(Now(),"mm"))+1),"000")

Thanks Doug and Noah for your help!
 
Back
Top