If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Country-Number
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. |
#2
|
|||
|
|||
Country-Number
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. "Brian" wrote: 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. |
#3
|
|||
|
|||
Country-Number
Perfect! Works like a charm!
"Jerry Whittle" wrote: 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. "Brian" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|