By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,581 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

unique numbers for table

P: n/a
i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?

Sep 30 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a

<ja***********@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
>i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?
The best way is, you don't.

That is, if you construct an identifier with meaningful sub-parts, you have
to extract those to do anything with them. It is better to store the
information separately, and concatenate it (if you feel compelled to do so)
in VBA code or expressions when you present / show the data to someone. That
way, you can search on the Fields, instead of having to extract them to
search or having to store redundant information.

If you do store them separately, there is a very convenient feature
(AutoNumber) you can use for internal things... joins, foreign keys, etc..
but it's not really for display to a user.

Larry Linson
Microsoft Access MVP
Sep 30 '06 #2

P: n/a
ja***********@gmail.com wrote:
i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In VBA it'd be something like this:

strPrefix = Format(Year(Date()),"yy") & "TTAG" & _
IIf(County='x' Or County='y' Or County='z',12,00)

strKey = strPrefix & Format(Nz(DMax("KeyValue","Table_name", _
"KeyValue LIKE '" & strPrefix & "*'"),0),"00")

You didn't say what to put when the county is not one of X, Y, or Z, so
I put "00" in the IIf() function's False option.

The DMax() function looks for a key that starts with the first part of
the key you're creating, the strPrefix. Translated, it'd look something
like this:

DMax("KeyValue","Table_Name","KeyValue Like '06TTAG12*'")

which should find the next number to add to the end of the key (be sure
to change the column and table names to your column & table names).

The Nz() function around the DMax() function will return zero when there
isn't any Max for that prefix.

The Format() function around the Nz() function will change a single
digit number to a double-digit number.

BTW, I'm sure you realize you can only have 99 keys w/ that prefix. Is
that enough?
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7SHYechKqOuFEgEQJCpACg7yepO8EDovfKDwPShQ3nnj OtJPcAoMhd
VTXdMM6T6CTPN4mSGdWQ+NyC
=OtBW
-----END PGP SIGNATURE-----
Sep 30 '06 #3

P: n/a
Thank you very much for the information. on the counties bit, there
are 52 counties grouped into 25 sections, so i would need to go 1
through 25. can i do that with this code?

MGFoster wrote:
ja***********@gmail.com wrote:
i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In VBA it'd be something like this:

strPrefix = Format(Year(Date()),"yy") & "TTAG" & _
IIf(County='x' Or County='y' Or County='z',12,00)

strKey = strPrefix & Format(Nz(DMax("KeyValue","Table_name", _
"KeyValue LIKE '" & strPrefix & "*'"),0),"00")

You didn't say what to put when the county is not one of X, Y, or Z, so
I put "00" in the IIf() function's False option.

The DMax() function looks for a key that starts with the first part of
the key you're creating, the strPrefix. Translated, it'd look something
like this:

DMax("KeyValue","Table_Name","KeyValue Like '06TTAG12*'")

which should find the next number to add to the end of the key (be sure
to change the column and table names to your column & table names).

The Nz() function around the DMax() function will return zero when there
isn't any Max for that prefix.

The Format() function around the Nz() function will change a single
digit number to a double-digit number.

BTW, I'm sure you realize you can only have 99 keys w/ that prefix. Is
that enough?
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7SHYechKqOuFEgEQJCpACg7yepO8EDovfKDwPShQ3nnj OtJPcAoMhd
VTXdMM6T6CTPN4mSGdWQ+NyC
=OtBW
-----END PGP SIGNATURE-----
Oct 1 '06 #4

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could, but it wud be very messy. It wud be better to break the
count part into a Case statement. E.g.:

Select Case strCounty
Case "Alameda", "Contra Costa" "Diablo"
strCounty = "01"
Case "Santa Clara", "San Jose", San Clemente"
strCounty = "02"
Case ... etc. ...

End Select

Just substitute your county names in the Case lines and the appropriate
number in the "strCounty =" line. Then replace the IIf() function that
decided the county number w/ the strCounty variable.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR9q9YechKqOuFEgEQJqygCghaSqjFH3eDvDfz2mf0Ff3N Ucf2kAoKCW
1oLwiXptLVt1khgcD5/qVuyd
=7KaL
-----END PGP SIGNATURE-----

eu********@gmail.com wrote:
Thank you very much for the information. on the counties bit, there
are 52 counties grouped into 25 sections, so i would need to go 1
through 25. can i do that with this code?

MGFoster wrote:
>ja***********@gmail.com wrote:
>>i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In VBA it'd be something like this:

strPrefix = Format(Year(Date()),"yy") & "TTAG" & _
IIf(County='x' Or County='y' Or County='z',12,00)

strKey = strPrefix & Format(Nz(DMax("KeyValue","Table_name", _
"KeyValue LIKE '" & strPrefix & "*'"),0),"00")

You didn't say what to put when the county is not one of X, Y, or Z, so
I put "00" in the IIf() function's False option.

The DMax() function looks for a key that starts with the first part of
the key you're creating, the strPrefix. Translated, it'd look something
like this:

DMax("KeyValue","Table_Name","KeyValue Like '06TTAG12*'")

which should find the next number to add to the end of the key (be sure
to change the column and table names to your column & table names).

The Nz() function around the DMax() function will return zero when there
isn't any Max for that prefix.

The Format() function around the Nz() function will change a single
digit number to a double-digit number.

BTW, I'm sure you realize you can only have 99 keys w/ that prefix. Is
that enough?
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7SHYechKqOuFEgEQJCpACg7yepO8EDovfKDwPShQ3nnj OtJPcAoMhd
VTXdMM6T6CTPN4mSGdWQ+NyC
=OtBW
-----END PGP SIGNATURE-----
Oct 1 '06 #5

P: n/a
ja***********@gmail.com wrote:
i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?
Atomicity: Each attribute must contain a single value, not a set of
values.

Think about this.

Oct 1 '06 #6

P: n/a
will this code allow me to generate the number when they his submit and
store it in the table with the data they just submitted

MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could, but it wud be very messy. It wud be better to break the
count part into a Case statement. E.g.:

Select Case strCounty
Case "Alameda", "Contra Costa" "Diablo"
strCounty = "01"
Case "Santa Clara", "San Jose", San Clemente"
strCounty = "02"
Case ... etc. ...

End Select

Just substitute your county names in the Case lines and the appropriate
number in the "strCounty =" line. Then replace the IIf() function that
decided the county number w/ the strCounty variable.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR9q9YechKqOuFEgEQJqygCghaSqjFH3eDvDfz2mf0Ff3N Ucf2kAoKCW
1oLwiXptLVt1khgcD5/qVuyd
=7KaL
-----END PGP SIGNATURE-----

eu********@gmail.com wrote:
Thank you very much for the information. on the counties bit, there
are 52 counties grouped into 25 sections, so i would need to go 1
through 25. can i do that with this code?

MGFoster wrote:
ja***********@gmail.com wrote:
i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In VBA it'd be something like this:

strPrefix = Format(Year(Date()),"yy") & "TTAG" & _
IIf(County='x' Or County='y' Or County='z',12,00)

strKey = strPrefix & Format(Nz(DMax("KeyValue","Table_name", _
"KeyValue LIKE '" & strPrefix & "*'"),0),"00")

You didn't say what to put when the county is not one of X, Y, or Z, so
I put "00" in the IIf() function's False option.

The DMax() function looks for a key that starts with the first part of
the key you're creating, the strPrefix. Translated, it'd look something
like this:

DMax("KeyValue","Table_Name","KeyValue Like '06TTAG12*'")

which should find the next number to add to the end of the key (be sure
to change the column and table names to your column & table names).

The Nz() function around the DMax() function will return zero when there
isn't any Max for that prefix.

The Format() function around the Nz() function will change a single
digit number to a double-digit number.

BTW, I'm sure you realize you can only have 99 keys w/ that prefix. Is
that enough?
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7SHYechKqOuFEgEQJCpACg7yepO8EDovfKDwPShQ3nnj OtJPcAoMhd
VTXdMM6T6CTPN4mSGdWQ+NyC
=OtBW
-----END PGP SIGNATURE-----
Oct 1 '06 #7

P: n/a
eu********@gmail.com wrote:
will this code allow me to generate the number when they his submit and
store it in the table with the data they just submitted
The eldest Oyster looked at him,
But never a word he said:
The eldest Oyster winked his eye,
And shook his heavy head--
Meaning to say he did not choose
To leave the oyster-bed.

But four young Oysters hurried up,
All eager for the treat:
Their coats were brushed, their faces washed,
Their shoes were clean and neat--
And this was odd, because, you know,
They hadn't any feet.

Oct 1 '06 #8

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know; it depends on what/how you're doing (it).

If you have a bound form w/ separate controls that hold each part of the
key you can combine the different parts into one "field" when the data
is saved, by using the Form's BeforeUpdate event. Just concatenate the
values from each relevant control into the key string; then save the key
string into a column. E.g.:

Me.KeyColumn = <a function to get the data into one string>

Just use the VBA examples I gave, as the body of the function.

Have you been reading the posts by Lyle Fairfield and Larry Linson? It
is a good idea to make sure you are following the Normal Form rules.
You can still have the concatenated key, if you wish, or you can use the
key's source columns (fields) as part of a Primary Key (PK) (PKs can be
composed of more than one column).

Try reading a good book on database design. I usually recommend
_Database Design for Mere Mortals_, by Hernandez. There are discussions
about PKs on many database news groups. Use Google to find more
citings.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRSBz3IechKqOuFEgEQJwmwCg1jCbjTdHbW676RSH1XN/c3MBWRgAoOuU
VAud7v77+qseNCllWSwe3fPV
=1Brq
-----END PGP SIGNATURE-----

eu********@gmail.com wrote:
will this code allow me to generate the number when they his submit and
store it in the table with the data they just submitted

MGFoster wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could, but it wud be very messy. It wud be better to break the
count part into a Case statement. E.g.:

Select Case strCounty
Case "Alameda", "Contra Costa" "Diablo"
strCounty = "01"
Case "Santa Clara", "San Jose", San Clemente"
strCounty = "02"
Case ... etc. ...

End Select

Just substitute your county names in the Case lines and the appropriate
number in the "strCounty =" line. Then replace the IIf() function that
decided the county number w/ the strCounty variable.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR9q9YechKqOuFEgEQJqygCghaSqjFH3eDvDfz2mf0Ff3N Ucf2kAoKCW
1oLwiXptLVt1khgcD5/qVuyd
=7KaL
-----END PGP SIGNATURE-----

eu********@gmail.com wrote:
>>Thank you very much for the information. on the counties bit, there
are 52 counties grouped into 25 sections, so i would need to go 1
through 25. can i do that with this code?

MGFoster wrote:
ja***********@gmail.com wrote:
i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?
>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In VBA it'd be something like this:

strPrefix = Format(Year(Date()),"yy") & "TTAG" & _
IIf(County='x' Or County='y' Or County='z',12,00)

strKey = strPrefix & Format(Nz(DMax("KeyValue","Table_name", _
"KeyValue LIKE '" & strPrefix & "*'"),0),"00")

You didn't say what to put when the county is not one of X, Y, or Z, so
I put "00" in the IIf() function's False option.

The DMax() function looks for a key that starts with the first part of
the key you're creating, the strPrefix. Translated, it'd look something
like this:

DMax("KeyValue","Table_Name","KeyValue Like '06TTAG12*'")

which should find the next number to add to the end of the key (be sure
to change the column and table names to your column & table names).

The Nz() function around the DMax() function will return zero when there
isn't any Max for that prefix.

The Format() function around the Nz() function will change a single
digit number to a double-digit number.

BTW, I'm sure you realize you can only have 99 keys w/ that prefix. Is
that enough?
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7SHYechKqOuFEgEQJCpACg7yepO8EDovfKDwPShQ3nnj OtJPcAoMhd
VTXdMM6T6CTPN4mSGdWQ+NyC
=OtBW
-----END PGP SIGNATURE-----
Oct 2 '06 #9

P: n/a
This has all been really helpful. I really appreciate it.

MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know; it depends on what/how you're doing (it).

If you have a bound form w/ separate controls that hold each part of the
key you can combine the different parts into one "field" when the data
is saved, by using the Form's BeforeUpdate event. Just concatenate the
values from each relevant control into the key string; then save the key
string into a column. E.g.:

Me.KeyColumn = <a function to get the data into one string>

Just use the VBA examples I gave, as the body of the function.

Have you been reading the posts by Lyle Fairfield and Larry Linson? It
is a good idea to make sure you are following the Normal Form rules.
You can still have the concatenated key, if you wish, or you can use the
key's source columns (fields) as part of a Primary Key (PK) (PKs can be
composed of more than one column).

Try reading a good book on database design. I usually recommend
_Database Design for Mere Mortals_, by Hernandez. There are discussions
about PKs on many database news groups. Use Google to find more
citings.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRSBz3IechKqOuFEgEQJwmwCg1jCbjTdHbW676RSH1XN/c3MBWRgAoOuU
VAud7v77+qseNCllWSwe3fPV
=1Brq
-----END PGP SIGNATURE-----

eu********@gmail.com wrote:
will this code allow me to generate the number when they his submit and
store it in the table with the data they just submitted

MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could, but it wud be very messy. It wud be better to break the
count part into a Case statement. E.g.:

Select Case strCounty
Case "Alameda", "Contra Costa" "Diablo"
strCounty = "01"
Case "Santa Clara", "San Jose", San Clemente"
strCounty = "02"
Case ... etc. ...

End Select

Just substitute your county names in the Case lines and the appropriate
number in the "strCounty =" line. Then replace the IIf() function that
decided the county number w/ the strCounty variable.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR9q9YechKqOuFEgEQJqygCghaSqjFH3eDvDfz2mf0Ff3N Ucf2kAoKCW
1oLwiXptLVt1khgcD5/qVuyd
=7KaL
-----END PGP SIGNATURE-----

eu********@gmail.com wrote:
Thank you very much for the information. on the counties bit, there
are 52 counties grouped into 25 sections, so i would need to go 1
through 25. can i do that with this code?

MGFoster wrote:
ja***********@gmail.com wrote:
i have to build a table within access that receives it's information
from an ASP web page and generates the primary key, with numbers and
letters, from the data submitted. a numer would be like this
06TTAG1202, but a little more complicated . the 06 would be from the
current year at time of submission, the TTAG would remain constant, the
12 would come from the information in the county feild (counties x,y,
and z would receive the designation 12) and the 02 would mean that this
is the second record with a unique number matching all of the previous
portions. how do you make access generate numbers like this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In VBA it'd be something like this:

strPrefix = Format(Year(Date()),"yy") & "TTAG" & _
IIf(County='x' Or County='y' Or County='z',12,00)

strKey = strPrefix & Format(Nz(DMax("KeyValue","Table_name", _
"KeyValue LIKE '" & strPrefix & "*'"),0),"00")

You didn't say what to put when the county is not one of X, Y, or Z, so
I put "00" in the IIf() function's False option.

The DMax() function looks for a key that starts with the first part of
the key you're creating, the strPrefix. Translated, it'd look something
like this:

DMax("KeyValue","Table_Name","KeyValue Like '06TTAG12*'")

which should find the next number to add to the end of the key (be sure
to change the column and table names to your column & table names).

The Nz() function around the DMax() function will return zero when there
isn't any Max for that prefix.

The Format() function around the Nz() function will change a single
digit number to a double-digit number.

BTW, I'm sure you realize you can only have 99 keys w/ that prefix. Is
that enough?
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7SHYechKqOuFEgEQJCpACg7yepO8EDovfKDwPShQ3nnj OtJPcAoMhd
VTXdMM6T6CTPN4mSGdWQ+NyC
=OtBW
-----END PGP SIGNATURE-----
Oct 2 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.