Country-Number

[Guest]

I am building a database and would like to use a country-number as a primary
key to identify different matters. I'm not sure how to go about it, though.
I need to be able to have duplicates on the "number" part, since the same
number could be used for a different country. Further complicating matters,
though, is that I want the database to autonumber based upon which country is
selected.

For instance, if the last country-number assigned to the United States was
U.S.-100, I want it to autofill 101 when U.S. is selected. Likewise, if the
last country-number assigned to Mexico was Mexico-50, I want it to autonumber
with 51 when Mexico is selected, even though the number 51 may have been used
for another country.

Any help would be appreciated.

 

[Guest]

No can do. Primary keys much be unique so you can't have a duplicate.
Autonumbers are just numbers and can't contain text. Further you can't depend
on autonumbers to be sequential.

What you can do is have two fields that combined are a primary key or unique
index. One field could be Country and the other CountryNumber. You could then
find the next available number with something like below as the default value
for the CountryNumber field or apply is on an event on a form such as On Exit.

=DMax("[CountryNumber]", "TableName", "[Country] = 'REVENUE_CARGO_HISTORY'")
+ 1

Two warnings:
1. Domain aggregate functions like DMax can be slow if you have a lot of
records in that table.
2. If you have a lot of records, that could mean that more than one person
is entering records into the database. It's possible that 2 or more people
could grab the same number in a multi-user environment.

However if it's a small table (let's say less than 10,000 records) and only
one person is adding records, DMax will work just fine.

 

[Guest]

Perfect! Works like a charm!

No can do. Primary keys much be unique so you can't have a duplicate.
Autonumbers are just numbers and can't contain text. Further you can't depend
on autonumbers to be sequential.

What you can do is have two fields that combined are a primary key or unique
index. One field could be Country and the other CountryNumber. You could then
find the next available number with something like below as the default value
for the CountryNumber field or apply is on an event on a form such as On Exit.

=DMax("[CountryNumber]", "TableName", "[Country] = 'REVENUE_CARGO_HISTORY'")
+ 1

Two warnings:
1. Domain aggregate functions like DMax can be slow if you have a lot of
records in that table.
2. If you have a lot of records, that could mean that more than one person
is entering records into the database. It's possible that 2 or more people
could grab the same number in a multi-user environment.

However if it's a small table (let's say less than 10,000 records) and only
one person is adding records, DMax will work just fine.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am building a database and would like to use a country-number as a primary
key to identify different matters. I'm not sure how to go about it, though.
I need to be able to have duplicates on the "number" part, since the same
number could be used for a different country. Further complicating matters,
though, is that I want the database to autonumber based upon which country is
selected.

For instance, if the last country-number assigned to the United States was
U.S.-100, I want it to autofill 101 when U.S. is selected. Likewise, if the
last country-number assigned to Mexico was Mexico-50, I want it to autonumber
with 51 when Mexico is selected, even though the number 51 may have been used
for another country.

Any help would be appreciated.