Create ID with consecutive numbers by month

rocketD · External Since: May 03, 2007 Posts: 10

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

Douglas J. Steele · External Since: Jan 12, 2009 Posts: 165

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 MVP
http://I.Am/DougSteele
(no e-mails, please!)

"rocketD" <darahx RemoveThis @gmail.com> wrote in message
news:cd1651d2-0636-4ed3-8403-5943ed985047@a39g2000pre.googlegroups.com...
> 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

rocketD · External Since: May 03, 2007 Posts: 10

On Nov 4, 12:17 pm, "Douglas J. Steele"
<NOSPAM_djsteele.TakeThisOut@NOSPAM_gmail.com> wrote:
> 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!)
>
> "rocketD" <dar....TakeThisOut@gmail.com> wrote in message
>
> news:cd1651d2-0636-4ed3-8403-5943ed985047@a39g2000pre.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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

Douglas J. Steele · External Since: Jan 12, 2009 Posts: 165

I'm assuming yrAdd and moAdd are both numeric.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"rocketD" <darahx.RemoveThis@gmail.com> wrote in message
news:46dcb538-5282-4f31-b416-f66f2a1c8e9f@q40g2000prh.googlegroups.com...
On Nov 4, 12:17 pm, "Douglas J. Steele"
<NOSPAM_djsteele.RemoveThis@NOSPAM_gmail.com> wrote:
> 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!)
>
> "rocketD" <dar....RemoveThis@gmail.com> wrote in message
>
> news:cd1651d2-0636-4ed3-8403-5943ed985047@a39g2000pre.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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

Darwin Phish · External Since: Nov 05, 2009 Posts: 1

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

On Nov 4, 1:45 pm, rocketD <dar... RemoveThis @gmail.com> wrote:
> 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

rocketD · External Since: May 03, 2007 Posts: 10

On Nov 5, 6:45 pm, Darwin Phish <darwinph....DeleteThis@gmail.com> wrote:
> 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
>
> On Nov 4, 1:45 pm, rocketD <dar....DeleteThis@gmail.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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!