Suggestions for coin database design needed

[src2]

I'm using Access2000 and I'm wanting to write a database to keep up with a coin collection. The part of the design that I could use some help with is in data entry. The person that is going to use this database will be entering the data over a course of several months. I'm sure that over the course of entering all the data the will come across items of the same type, denomination, grade, etc. that they have already entered but only need to update the current quantity. What I think they want is the ability to enter a new record and as each field of information is entered the current quantity is displayed and will decrease as additional fields are selected. until all the information is either unique or matches what has been previously entered. At that point they will add the quantity to the existing entries.

I'm not looking for real specific code as I haven't even started coding yet. What I am looking for is the best way to approach what I want it to do.

Thanks a lot

 

[lespaul]

check out 'Fundamentals of Relational Database Design'

At this point you shouldn't be worrying about data entry or form design. If your table design is sound then the rest will be easy too.

leslie

 

[src2]

Thanks for the link. I've already read a couple of things on relational design but I'll check this out too. However I was hoping for a little more help then reading on relational design and everything will be easy. My current thought is to try the following: As I transverse through my input fields have them requery the the quantity on hand. What do you think? Is there another option that would be better?

TIA

 

[lespaul]

Ok, you want to design a coin tracking database. Is this for a personal collector or a business? I'm going to assume a business and that they are buying and selling coins. So you are basically looking at an inventory tracking system. It doesn't matter that the inventory is coins.

First determine what you will be storing information about. Is there currently a paper tracking system they use? This will be a good start to defining the fields of your tables. Once you have a list of all the fields you want to store information about, determine what tables these fields should be stored in. I would guess that you will have a master table of coins. (THIS IS NOT A TABLE OF THE COINS IN STOCK, THIS IS A TABLE ABOUT THE COINS THAT COULD BE IN STOCK) Information about the COINS will include:

tblGeneralCoinInfo
CoinID (PK)
CoinDemonination
CoinMintDate
CoinMintLocation

tblCoinInventory (Specific Coin Information)
InventoryID (PK)
CoinTypeID (FK to tblGeneralCOinInfo)
Condition
Cost

Now, this design would be used if you are interested in tracking the purchase and sale of individual coins so that you can calculate profit on a per coin basis. You may not need that much detail.

Personally I would suggested a "canned" solution like Peachtree or Quickbooks. They have both been designed with the small business owner in mind and can do all the inventory control as well as integrated accounting.

Leslie

 

[Craig0201]

There is a Book Collection database example provided with Access. It may well be possible for you to adapt this.

Craig

 

[src2]

That's what I love about groups, thanks for all the advice.