Access 2003, autonumbers

[Dave Hubbard]

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]

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

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

hth

 

[Dave Hubbard]

Tina, I have 2 tables so far.

1> tblCOUNTRY ID #
Country Name
Country ID # ( primary field )
Continent ID #
2>tbl 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]

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

 

[Chris2]

Tina, I have 2 tables so far.

1> tblCOUNTRY ID #
Country Name
Country ID # ( primary field )
Continent ID #
2>tbl 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.

 

[Chris2]

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.

 

[tina]

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

 

[TC]

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

 

[Dave Hubbard]

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

 

[tina]

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

 

[TC]

Further to tina's excellent comments:

Designing a database looks simple on the face of it. But it's full of
nasty traps for the beginner. These traps will not be apparent to you,
at the start. But they will become greatly apparrent down the track,
when you try to write your queries, forms & reports. Suddenly you'll
find it difficult or impossible to get the data back, in the ways that
you want. Your forms will show duplicate records (ie., several records
where you only want one), and/or missing ones. You'll spend countless
hours trying to rewrite your SQL statements, when in fact, the problem
is that the table structure is not correct.

It's like building a house on a foundation of sand. It looks fine
initially, byut then you notice it's starting to lean to the left;
oops! Then it falls over. Then you have to rip it all up, start again,
and do it all properly. Better to have done it properly in the first
place!

Trust us on this - you need to drop what you're doing, & do some
reading about "database normalization", before you continue.

HTH,
TC

 

[Dave Hubbard]

Tina & TC,
Much thanks, it looks like I'll have to put a pause with my developing
process. I'll go look up the stated articles.

Take care,

Dave Hubbard

 

[Chris2]

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.

You can do this if you wish, but everything that MS Access might
make easy for you will be thrown away.

This way I can have a seperate table listing the characteristics for
the coins from the seperate countries.

Extended characteristics of a coin, by country, can be further
normalized in separate tables. I would have to know a lot more
about these characteristics, and might have to go with Super Type
and Sub Type table arrangements, but in the end, it is a problem
with known solutions.

So tbl COUNTRY ( 10 ),

Just as a side note, when you type out "tbl COUNTRY ( 10 )", I am
thrown for a complete loop. It appears to be part table name part
Data Definition Language (DDL). Is this really the name of a table?
With spaces and () (both of which should not be used in table
names)?

will just a tabale having the characteristics of
coins from the country having the ID # ( 10 ).
Maybe this clearifies my ideas.

Oh, I believe I understood the first time.

Please, don't do it. There are other solutions.

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 mentioned something about this previously. Can you please
explain what difference it will make having ID #'s starting at 1 for
each country's coins? I know only the faintest bits and pieces of
numismatics. Is there some requirement defined by this fascinating
branch of collecting that requires that the coins of each country be
assigned a number starting with 1? Otherwise, from a programming
and db design standpoint, I can't really think of one. Especially
not when every coin will reside in the Coins table I mentioned.


Googling on Database Normalization will provide many good articles.
Most of them are written for other database professionals (the
actual written rules of Database Normalization will throw you for a
loop if you don't know exactly what they are talking about).

Knowledge about Database Normalization also goes hand in hand with
some knowledge of Data Modeling.

Data Modeling is a process of describing the "entities" related to
whatever process or business (or game, personal hobby, etc.) is
under discussion. Usually this involves lots of lines and boxes and
notations on a chart. There are several methods available to "data
model" something, and several of these methods exist to make the
eventual job of designing a relational database easier.

Database Normalization is a proces of decomposition, of breaking
down information until you get down the basic elements that describe
entities, and making sure that the information kept about an entity
(i.e. "table") *really* belongs to it, and not to something else (or
somewhere else).


Good Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I quite like this whole site, since it has a handy menu on the right
describing many important aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

---------------------------

In addition to the tables I already mentioned:


Continents
ContinentID AUTOINCREMENT -- PK
ContinenentName TEXT(16)

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

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

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

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


You can add:

CoinAttributeTypes
CoinAttributeTypeID AUTOINCREMENT -- PK
AttributeName TEXT(48)

CoinAttributes
CoinAttributeID AUTOINCREMENT -- PK
CoinID INTEGER -- FK to Coins
CoinAttributeTypeID INTEGER -- FK to CoinAttributeTypes
AttributeValue TEXT(48)


These two tables allow you to add an unlimited list of additional
custom attributes for any particular coin. The fixed an unchanging
attributes go in Coins, and the variable custom attributs go in
CoinAttributes. Some work could expand it to provide for templated
lists of custom attributes by country.


Sincerely,

Chris O.

 

[Dave Hubbard]

You can do this if you wish, but everything that MS Access might
make easy for you will be thrown away.



Extended characteristics of a coin, by country, can be further
normalized in separate tables. I would have to know a lot more
about these characteristics, and might have to go with Super Type
and Sub Type table arrangements, but in the end, it is a problem
with known solutions.


Just as a side note, when you type out "tbl COUNTRY ( 10 )", I am
thrown for a complete loop. It appears to be part table name part
Data Definition Language (DDL). Is this really the name of a table?
With spaces and () (both of which should not be used in table
names)?

COUNTRY ( 10 ) is the 10th country from an alphabetic list of all the
countries. I thought giving each country an ID # would make it easier for
sorting.

Oh, I believe I understood the first time.

Please, don't do it. There are other solutions.



You mentioned something about this previously. Can you please
explain what difference it will make having ID #'s starting at 1 for
each country's coins? I know only the faintest bits and pieces of
numismatics. Is there some requirement defined by this fascinating
branch of collecting that requires that the coins of each country be
assigned a number starting with 1? Otherwise, from a programming
and db design standpoint, I can't really think of one. Especially
not when every coin will reside in the Coins table I mentioned.

By starting each counties coins by one, if a coin has an ID(89) then that
country has 89 coins from there. I won't have to figure out the quantity
from each country, the above coin also tells me that that country DOESN'T
have less then 89 coins.

Googling on Database Normalization will provide many good articles.
Most of them are written for other database professionals (the
actual written rules of Database Normalization will throw you for a
loop if you don't know exactly what they are talking about).

Some of the articles listed by googling on Database Normalization were a
little confusing, and above me.

Knowledge about Database Normalization also goes hand in hand with
some knowledge of Data Modeling.

Data Modeling is a process of describing the "entities" related to
whatever process or business (or game, personal hobby, etc.) is
under discussion. Usually this involves lots of lines and boxes and
notations on a chart. There are several methods available to "data
model" something, and several of these methods exist to make the
eventual job of designing a relational database easier.

Database Normalization is a proces of decomposition, of breaking
down information until you get down the basic elements that describe
entities, and making sure that the information kept about an entity
(i.e. "table") *really* belongs to it, and not to something else (or
somewhere else).


Good Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I quite like this whole site, since it has a handy menu on the right
describing many important aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

Thank you for the above links, they seem to be more my level.

---------------------------

In addition to the tables I already mentioned:


Continents
ContinentID AUTOINCREMENT -- PK
ContinenentName TEXT(16)

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

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

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

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


You can add:

CoinAttributeTypes
CoinAttributeTypeID AUTOINCREMENT -- PK
AttributeName TEXT(48)

CoinAttributes
CoinAttributeID AUTOINCREMENT -- PK
CoinID INTEGER -- FK to Coins
CoinAttributeTypeID INTEGER -- FK to CoinAttributeTypes
AttributeValue TEXT(48)


These two tables allow you to add an unlimited list of additional
custom attributes for any particular coin. The fixed an unchanging
attributes go in Coins, and the variable custom attributs go in
CoinAttributes. Some work could expand it to provide for templated
lists of custom attributes by country.


Sincerely,

Chris O.

To give you a little of my specs, I'm a survivor of a car accident from
early '89. I was told that I would never be able to lead a normal life, but
have decided to do the best I can on any given day. These newsgroups give me
a good link to needed information.

Best Regards,

Dave Hubbard

 

[tina]

rest assured, Dave, you were completely normal in your initial approach to
building a database, as compared to the average newbie. most newbie folks
make the same types of mistakes in designing tables/relationships, unless
they've already had training in relational design principles - so you fall
right in with the rest of the crowd.
and while you may have certain challenges in daily living, it's obvious that
there's nothing wrong with your thought processes. you'll do fine with
Access development once you get the basics of data
normalization/relationships under your belt. good luck with your project,
and come on back when you have questions - we're always here.

 

[Chris2]

By starting each counties coins by one, if a coin has an ID(89) then that
country has 89 coins from there. I won't have to figure out the quantity
from each country, the above coin also tells me that that country DOESN'T
have less then 89 coins.

Dave Hubbard,

Glad to be of help.

The xxxxID column in each table uniquely identifies each row. That
is its meaning and purpose.

By trying to use it to denote the count of coins, you have added a
second meaning to it.

Relational database tables tend to work best if each column's values
mean only one thing. (This is referred to as "collapsing values",
and goes against 1st Normal Form; and "1st Normal Form" is described
in the various links I provided.)

If you delete a coin (what if a coin was mis-entered?), this can
cause problems, especially if you are using Autonumber columns.
Autonumber columns do not allow you to re-use numbers, and in any
event can suddenly jump to higher numbers when certain things
happen, leaving sometimes large gaps. It is possible to create a
programmatic system of increasing the coin numbers, but my advice is
to use the "xxxxID" columns only as internal identifiers that you
never see on your forms or reports.

Even if you are not using Autonumber columns, any programmatic
solution you have that generates "the next highest" number will also
have to include provisions for searching out and filling unused
"lower" gaps. And what happens in the interval between the deletion
of a coin and the insertion of a new coin to "fill up" the gap?
Wrong data comes out of any query that is run, that is what happens.

You can use (based on my example tables):

(Untested, sorry.)

SELECT CY1.CountryName
,COUNT(C1.CoinID) As CoinCount
FROM (Coins AS C1
INNER JOIN
Mints AS M1
ON C1.MintID = M1.MintID)
INNER JOIN
Countries AS CY1
ON M1.CountryID = CY1.CountryID
GROUP BY CY1.CountryName

Or something very similar, to determine the number of coins in each
country. The number of coins in other groupings can be used to
determined (by Mint, etc.).

You can add a WHERE clause with a parameter in a query:

WHERE CY1.CountryName = [Enter Country]

Or to take a control reference from a form:

WHERE CY1.CountryName = Forms!YourForm!YourControl

In order to return results for single countries.

The above query does not care about the values, or gaps, in the
xxxxID columns, it just tells you what the count is for the
specified group.


Sincerely,

Chris O.

 

[Dave Hubbard]

By starting each counties coins by one, if a coin has an ID(89) then that
country has 89 coins from there. I won't have to figure out the quantity
from each country, the above coin also tells me that that country DOESN'T
have less then 89 coins.

Dave Hubbard,

Glad to be of help.

The xxxxID column in each table uniquely identifies each row. That
is its meaning and purpose.

By trying to use it to denote the count of coins, you have added a
second meaning to it.

Relational database tables tend to work best if each column's values
mean only one thing. (This is referred to as "collapsing values",
and goes against 1st Normal Form; and "1st Normal Form" is described
in the various links I provided.)

If you delete a coin (what if a coin was mis-entered?), this can
cause problems, especially if you are using Autonumber columns.
Autonumber columns do not allow you to re-use numbers, and in any
event can suddenly jump to higher numbers when certain things
happen, leaving sometimes large gaps. It is possible to create a
programmatic system of increasing the coin numbers, but my advice is
to use the "xxxxID" columns only as internal identifiers that you
never see on your forms or reports.

Even if you are not using Autonumber columns, any programmatic
solution you have that generates "the next highest" number will also
have to include provisions for searching out and filling unused
"lower" gaps. And what happens in the interval between the deletion
of a coin and the insertion of a new coin to "fill up" the gap?
Wrong data comes out of any query that is run, that is what happens.

You can use (based on my example tables):

(Untested, sorry.)

SELECT CY1.CountryName
,COUNT(C1.CoinID) As CoinCount
FROM (Coins AS C1
INNER JOIN
Mints AS M1
ON C1.MintID = M1.MintID)
INNER JOIN
Countries AS CY1
ON M1.CountryID = CY1.CountryID
GROUP BY CY1.CountryName

Or something very similar, to determine the number of coins in each
country. The number of coins in other groupings can be used to
determined (by Mint, etc.).

You can add a WHERE clause with a parameter in a query:

WHERE CY1.CountryName = [Enter Country]

Or to take a control reference from a form:

WHERE CY1.CountryName = Forms!YourForm!YourControl

In order to return results for single countries.

The above query does not care about the values, or gaps, in the
xxxxID columns, it just tells you what the count is for the
specified group.


Sincerely,

Chris O.

So Chris or Tina if I'm trying to Normalize my database my tables should
look like this?

If my table in 1NF is:
tblFIRST
CountryName
ContinentName
CoinID# -- PK
Denomination
Grading
ObverseDevice (picture heads side)
ReverseDevice (picture tails side)
Inscription
Motto

Then my tables in 3NF would be:
tblCONTINENT
ContinentName
ContinentID# -- PK
--------------------------
tblCOUNTRY
CountryName
CountryID# -- PK
ContinentID# -- FK to tblCONTINENT
--------------------------
tblCOINS
CoinID# -- PK
CountryID# -- FK to tblCOUNTRY
Denomination
Grading
ObverseDevice (picture heads side)
ReverseDevice (picture tails side)
Inscription
Motto
--------------------------

This way I won't have to link by the country or continent names, but by
either the CountryID# or the ContinentID#. I think this should help minimize
the space needed to run my application.
Understanding this ACCESS 2003 is really confusing, but I'm not afraid to
try anything.

Much appreciation with any assistance,

Dave Hubbard

 

[tina]

your 3rd normal form table design looks fine to me. the only note i might
include: if you have a standard set of "grades" that you apply to all coins
(i have no idea what this would be - excellent condition, good condition,
poor condition? whatever), then you could also include a

tblGRADES
GradeID -- PK
GradeName

then instead of the Grading field in tblCOINS, use the GradeID field as a
foreign key to tblGRADES.

since you know what "grading" is (and i don't), you'll need to decide if the
above suggestion is appropriate for your database or not.

hth

 

[Chris2]

So Chris or Tina if I'm trying to Normalize my database my tables should
look like this?

Then my tables in 3NF would be:
tblCONTINENT
ContinentName
ContinentID# -- PK
--------------------------
tblCOUNTRY
CountryName
CountryID# -- PK
ContinentID# -- FK to tblCONTINENT
--------------------------
tblCOINS
CoinID# -- PK
CountryID# -- FK to tblCOUNTRY
Denomination
Grading
ObverseDevice (picture heads side)
ReverseDevice (picture tails side)
Inscription
Motto
--------------------------

This way I won't have to link by the country or continent names, but by
either the CountryID# or the ContinentID#. I think this should help minimize
the space needed to run my application.
Understanding this ACCESS 2003 is really confusing, but I'm not afraid to
try anything.

Much appreciation with any assistance,

Dave Hubbard

Dave Hubbard,

The above looks great.

Remember to review it to make sure that it does represent all the
pieces of information you would like to collect (there is no way I
can know as much as you about this field of collecting).


Sincerely,

Chris O.

 

[Chris2]

your 3rd normal form table design looks fine to me. the only note i might
include: if you have a standard set of "grades" that you apply to all coins
(i have no idea what this would be - excellent condition, good condition,
poor condition? whatever), then you could also include a

tblGRADES
GradeID -- PK
GradeName

then instead of the Grading field in tblCOINS, use the GradeID field as a
foreign key to tblGRADES.

since you know what "grading" is (and i don't), you'll need to decide if the
above suggestion is appropriate for your database or not.

hth

tina,

Yes, that's a good catch. There are standard gradings in coin
collecting, IIRC.


Sincerely,

Chris O.