Access 2003, autonumbers

I've been trying to design an Access database for my coin collection. I've
been at it since 01, but that doesn't matter, does it. I can set the
autonumber to work for the alternate country names, so filtering is easier.
I'm having trouble being able to link the autonumber for the coins per
country, I want to be able to have ID#'s for each coin, per country, all
beginning at 001. Access won't let me have more then one autonumber per
table. This way each country has a coin ID 001, and continues for all coins
it has.

Any help assist greatly,

Dave H.

what's your current tables setup? please describe them as

TableName
FieldName (primary key)
AnotherFieldName
AnotherFieldName (foreign key from AnotherTableName)
AnotherFieldName

hth


"Dave Hubbard" ( donotspam ) wrote in message
...
I've been trying to design an Access database for my coin collection.
I've
been at it since 01, but that doesn't matter, does it. I can set the
autonumber to work for the alternate country names, so filtering is
easier.
I'm having trouble being able to link the autonumber for the coins per
country, I want to be able to have ID#'s for each coin, per country, all
beginning at 001. Access won't let me have more then one autonumber per
table. This way each country has a coin ID 001, and continues for all
coins
it has.

Any help assist greatly,

Dave H.

Tina, I have 2 tables so far.

1 tblCOUNTRY ID #
Country Name
Country ID # ( primary field )
Continent ID #
2tbl COUNTRY ( # )
Country ID #
Coin ID # ( primary field )
Denomination
Grading
Heads side picture
Tales side picture
Inscription
Motto

So Tina, I would like to be able to have both primary keys as autonumbers,
in their own respect. The tables are linked by the Country ID #, I'd have a
new table per country, in order to have the autonumber be incremented per
coin for each seperate country. I will have hundreds of different tables for
the different countries. I also don't want to have different countries
having the same ID #, unless you have another idea for ID ing the countries.

BFN

Dave


"tina" wrote:

what's your current tables setup? please describe them as

TableName
FieldName (primary key)
AnotherFieldName
AnotherFieldName (foreign key from AnotherTableName)
AnotherFieldName

hth


"Dave Hubbard" ( donotspam ) wrote in message
...
I've been trying to design an Access database for my coin collection.
I've
been at it since 01, but that doesn't matter, does it. I can set the
autonumber to work for the alternate country names, so filtering is
easier.
I'm having trouble being able to link the autonumber for the coins per
country, I want to be able to have ID#'s for each coin, per country, all
beginning at 001. Access won't let me have more then one autonumber per
table. This way each country has a coin ID 001, and continues for all
coins
it has.

Any help assist greatly,

Dave H.

Hey Tina,

I was just looking back through the past posts, while thinking. If I use an
auto number in the country name table, and an autonumber in each of the
seperate country coins table, then my autonumber deleima can be fixed. While
accessing the data in each country coin table, the autonumbers would be
increasing as by the amount of coins in collection for that country. Right?

Much appreciation for your help,

Dave Hubbard

"tina" wrote:

what's your current tables setup? please describe them as

TableName
FieldName (primary key)
AnotherFieldName
AnotherFieldName (foreign key from AnotherTableName)
AnotherFieldName

hth


"Dave Hubbard" ( donotspam ) wrote in message
...
I've been trying to design an Access database for my coin collection.
I've
been at it since 01, but that doesn't matter, does it. I can set the
autonumber to work for the alternate country names, so filtering is
easier.
I'm having trouble being able to link the autonumber for the coins per
country, I want to be able to have ID#'s for each coin, per country, all
beginning at 001. Access won't let me have more then one autonumber per
table. This way each country has a coin ID 001, and continues for all
coins
it has.

Any help assist greatly,

Dave H.

"Dave Hubbard" ( donotspam ) wrote in
message ...
Tina, I have 2 tables so far.

1 tblCOUNTRY ID #
Country Name
Country ID # ( primary field )
Continent ID #
2tbl COUNTRY ( # )
Country ID #
Coin ID # ( primary field )
Denomination
Grading
Heads side picture
Tales side picture
Inscription
Motto

So Tina, I would like to be able to have both primary keys as
autonumbers,
in their own respect. The tables are linked by the Country ID #,
I'd have a
new table per country, in order to have the autonumber be
incremented per
coin for each seperate country. I will have hundreds of different
tables for
the different countries. I also don't want to have different
countries
having the same ID #, unless you have another idea for ID ing the
countries.

BFN

Dave


Dave Hubbard,

I am not quite sure why you have two tables named:

1)[tblCOUNTRY ID #]
2)[tbl COUNTRY ( # )]

I cannot determine the difference between the contents of these
tables based on their names alone. Why would a country table, [tbl
COUNTRY ( # )], have coinage information in it? Is [Heads side
picture] an attribute of a country? (That's a rhetorical question.)

In any event, spaces and special characters
!@#$%^&*()_+[]{}/\?|,.`~ should all be avoided in object names.


I believe the table structures should go something like this:

Continents
ContinentID AutoNumber -- PK
ContinenentName TEXT(16)

Countries
CountryID Autonumber -- PK
ContinenentID INTEGER -- FK to Continents
CountryName TEXT(128)

Mints
MintID Autonumber -- PK
CountryID INTEGER -- FK to Countries
MintName TEXT(128)

Coins
CoinID Autonumber -- PK
MintID INTEGER -- FK to Mints
Denomination TEXT(48)
Grading TEXT(36)
HeadsPicture ?
TailsPicture ?
Inscription TEXT(48)
Motto TEXT(48)

Stamping
StampID Autonumber -- PK
CoinID INTEGER -- FK to Coins
StampDate DATETIME


In this example, the contents of each table are obvious based on the
name alone.


Sincerely,

Chris O.

"Dave Hubbard" ( donotspam ) wrote in
message ...

and an autonumber in each of the seperate country coins table,

While accessing the data in each country coin table,

Dave Hubbard,

There should not be separate country coin tables.

There should be one table for countries, and one table for coins.


Sincerely,

Chris O.

I will have hundreds of different tables for
the different countries.

stop! this is not normalized table design. suggest the following, as

tblContinents
ContinentID (primary key - Autonumber)
ContinentName

tblCountries
CountryID (primary key - Autonumber)
CountryName
ContinentID (foreign key from tblContinents)

tblCoins
CoinID (Number, Long Integer)
CountryID (foreign key from tblCountries)
(the rest of the fields that describe a specific coin)
use the *first two* above fields as a combination primary key for this
table.

the relationships are
tblContinents.ContinentID 1:n tblCountries.ContinentID
one continent may have many countries, but each country is on only one
continent.
tblCountries.CountryID 1:n tblCoins.CountryID
one country may have many coins, but each coin belongs to only one country.

note that the CoinID field in tblCoins is *not* an Autonumber data type.
rule of thumb is: never use an Autonumber to provide a value that will have
meaning to the user - or that the user will even see. for one thing, an
Aut0number does not and cannot guarantee sequential numbers without gaps;
for another, you cannot control the generation/assignment of Autonumbers.
bottom line is, you cannot generate the CoinID value you want at the table
level.

instead, use code in the data entry form bound to tblCoins, to generate a
CoinID value that meets your parameters of "sequential numbers that are
incremented separately for each country".

recommend you read up on data normalization and table relationships, which
are the most important aspects of building a relational database. see
http://home.att.net/~california.db/tips.html#aTip1 for more information;
also suggest you read the rest of the tips on that page, especially #5.

hth


"Dave Hubbard" ( donotspam ) wrote in message
...
Tina, I have 2 tables so far.

1 tblCOUNTRY ID #
Country Name
Country ID # ( primary field )
Continent ID #
2tbl COUNTRY ( # )
Country ID #
Coin ID # ( primary field )
Denomination
Grading
Heads side picture
Tales side picture
Inscription
Motto

So Tina, I would like to be able to have both primary keys as autonumbers,
in their own respect. The tables are linked by the Country ID #, I'd have
a
new table per country, in order to have the autonumber be incremented per
coin for each seperate country. I will have hundreds of different tables
for
the different countries. I also don't want to have different countries
having the same ID #, unless you have another idea for ID ing the
countries.

BFN

Dave


"tina" wrote:

what's your current tables setup? please describe them as

TableName
FieldName (primary key)
AnotherFieldName
AnotherFieldName (foreign key from AnotherTableName)
AnotherFieldName

hth


"Dave Hubbard" ( donotspam ) wrote in
message
...
I've been trying to design an Access database for my coin collection.
I've
been at it since 01, but that doesn't matter, does it. I can set the
autonumber to work for the alternate country names, so filtering is
easier.
I'm having trouble being able to link the autonumber for the coins per
country, I want to be able to have ID#'s for each coin, per country,
all
beginning at 001. Access won't let me have more then one autonumber
per
table. This way each country has a coin ID 001, and continues for all
coins
it has.

Any help assist greatly,

Dave H.

tina & Chris2 are right. You should /not/ have seperate country coin
tables.

You need to learn about "database normalization". Google on that term,
you should find tons of hits.

HTH,
TC

Thanks to all, maybe I should explain further. I have one table with all the
country names, and their ID #'s. I have others tables for each country
coins. This way I can have a seperate table listing the characteristics for
the coins from the seperate countries. So tbl COUNTRY ( 10 ), will just a
tabale having the characteristics of coins from the country having the ID # (
10 ).
Maybe this clearifies my ideas.
Unless I can have one table with country names, and one table with just
coins. I want to be able to have ID #'s starting at 1 for each countries
coins.

BFN

Dave

"TC" wrote:

tina & Chris2 are right. You should /not/ have seperate country coin
tables.

You need to learn about "database normalization". Google on that term,
you should find tons of hits.

HTH,
TC

Chris2, TC, and i all understand what you're trying to do, Dave. what we're
all telling you is that you're going about it the wrong way. putting
hundreds of tables in the database to separately accommodate each country's
coins is a violation of data normalization rules, it's unnecessary to
achieve your stated goal - and it will bring you nothing but misery in later
development stages of your database, trust me.

Unless I can have one table with country names, and one table with just
coins. I want to be able to have ID #'s starting at 1 for each countries
coins.

you can. i outlined that tables/relationships setup to you in my last post
elsewhere in this thread, as well as giving you a link to more information
about data normalization, so you can learn the "why" of the setup as well as
the "how".

hth


"Dave Hubbard" ( donotspam ) wrote in message
...
Thanks to all, maybe I should explain further. I have one table with all
the
country names, and their ID #'s. I have others tables for each country
coins. This way I can have a seperate table listing the characteristics
for
the coins from the seperate countries. So tbl COUNTRY ( 10 ), will just a
tabale having the characteristics of coins from the country having the ID
# (
10 ).
Maybe this clearifies my ideas.
Unless I can have one table with country names, and one table with just
coins. I want to be able to have ID #'s starting at 1 for each countries
coins.

BFN

Dave

"TC" wrote:

tina & Chris2 are right. You should /not/ have seperate country coin
tables.

You need to learn about "database normalization". Google on that term,
you should find tons of hits.

HTH,
TC