Question Coin collection database

[NickyBE]

Hi everyone,

I've made an Access database for my euro coin collection this weekend and even though it looks nice (in my opinion), it doesn't really do what I'd like it to do. I'm a bit lost on how to turn it into a practical database that shows me what I want to see, so I'm hoping someone could give me some tips/ideas.

What I have until now:
I wanted an overview (with images) that shows me all the coins per country, so I've made a table (see attachments).
This table results in the form in attachment.

I was pretty happy about this until I tried to make a list (with images) of all coins I don't have yet. I've made a report based on a query but sadly that doesn't work because it shows all the coins of the non-complete countries, also those coins that I already have. I assume this is because they're all in one row.

I was thinking I could solve this by changing my table. Instead of using one row per country, I could use one row per coin, but then I won't be able to have the same form (lay-out) anymore. I think.
I'd really like to have them next to each other like it is now.

Any tips/ideas ?

PS: I hope I gave enough information about what's already in the database. If not, please ask!

 

[jdraw]

You asked for any tips/ideas, so I'm going to suggest you spend a little time learning database design.Here is a tutorial that you'll have to work through. But you will learn about tables and fields -entities and attributes - relationships and Normalization. This is basic to database design. Also, it starts with a clear description of the purpose of the database. You need this to know whether or not your database is doing what you want.

Good luck with your project.

 

[NickyBE]

Thanks for the suggestion, but apparently that doesn't work for me. I tried but I keep bumping into errors or problems. I just wanted a quick and easy overview of my collection and to be able to extract a list of all the coins I don't have yet. I guess Access isn't what I'm looking for after all.

 

[JHB]

I guess Access isn't what I'm looking for after all.

I think you are right, if you don't want to investigate the time to learn the normalization process, which include how to setup tables correct.
Because the problem you discovered is a direct causal brought about by lack of normalization!

 

[essaytee]

Thanks for the suggestion, but apparently that doesn't work for me. I tried but I keep bumping into errors or problems. I just wanted a quick and easy overview of my collection and to be able to extract a list of all the coins I don't have yet. I guess Access isn't what I'm looking for after all.

Those errors or problems you're bumping into need to be converted to questions of the forum. Yes, it's a slow process but that's the nature of the beast.

 

[NickyBE]

Ok, I haven't given up yet (I'm actually still desperate to have a nice and practical overview of my collection), but there's always one big problem that returns and for which I can't seem to find a solution.
But I think this belongs more into the "Theory and practice of database design" section.

How to organize my tables/data so I can have a form like the one in attachment, but in which I can edit my data?

I already tried different ways, but it never really works:

All coins in one table doesn't work because later on, I won't be able to filter each coin individually (e.g. to create a list of all the coins I don't have yet).

One table per coin (eg tbl1cent, tbl2cent,...) doesn't work either because then I won't be able to edit the data in my form (because I'll have too many tables with one-to-many relationships, if I understood another topic in this forum correctly).

Is there any way to have the form in attachment (or something similar) in which I can edit my data?

 

[jdraw]

Nicky,

I take it from your responses that you did not work through the tutorial I suggested. I'm going to suggest you take 2 hours and work through that tutorial. It will be the best 2 hours of database learning, and you will have knowledge of the concepts involved.

An easy to use database and interface isn't an over the weekend project. It takes design and testing. Access is not going to do the analysis and design work for you, no matter what M$oft or marketers tell you.
Good luck.

 

[NickyBE]

I did read the tutorial. The first part at least.
I understand that in his example, certain fields need to be in a specific table and so on, but I don't know how to do this with a coin collection. And in the second part, he's already assigning relationships and so on while I still have no idea which tables I need.

 

[essaytee]

I did read the tutorial. The first part at least.
I understand that in his example, certain fields need to be in a specific table and so on, but I don't know how to do this with a coin collection. And in the second part, he's already assigning relationships and so on while I still have no idea which tables I need.

For starters I'd say you need two tables (Countries and Coins).

table_Countries
Country_ID - Primary Key
Country_Name

table_Coins
Coin_ID - primary key
Country_ID - Foreign key (linked to table_Countries)
Coin_Description_1 (contains stuff like cent, pence, dollar etc.)
Coin_Value (contains 1, 2, 5 etc.)
Coin_Image_Path
Coin_I_Have_It (yes / no )

The above is just a sample but it should give you an idea of which fields go in which tables.

Steve.

 

[JHB]

And a field (or more correct a table) for the release year of the coin.

 

[essaytee]

And a field (or more correct a table) for the release year of the coin.

And a date for expiration of the coin (we no longer have 1 & 2 cent coins and soon our 5 cent coin we be no longer).

I suppose the 'I have it field' could be removed as the 'Image path' field could serve the same purpose.

 

[jdraw]

I did read the tutorial. The first part at least.
I understand that in his example, certain fields need to be in a specific table and so on, but I don't know how to do this with a coin collection. And in the second part, he's already assigning relationships and so on while I still have no idea which tables I need.

Well he shows you how to identify the tables involved, and what goes in which table. Why not start by looking at his starting point -- a description of the business/problem indicating how things fit together. Just try and see if, using his rules and comments, you get the same diagram that he does. He has solutions, but the learning is by reading and doing. Solve his already solved problem by doing it.

Then write a description of what your database/business is about. Follow his verbiage and use your terms. Identify the tables and the fields involved.

You are not going to learn how to analyze a situation/problem and solve it without some effort. You seem, to me at least, to be hoping for some magical epiphany or osmosis to bring knowledge and skill into your head. It doesn't happen that way with database. You may have natural talents for basketball/hockey/baseball/soccer etc, but in my view you're going to have to do some work to learn the basics.

And, once you learn the basics and the general process, you can design most any database. The forum is here to assist and advise, but we won't do it for you. You show an honest effort and most forum participants will help.

There is software out there for coin collection. This one offers a free trial. You might want to try it, just to get some idea of the tables and fields involved.


Good luck.

 

[NickyBE]

For starters I'd say you need two tables (Countries and Coins).

table_Countries
Country_ID - Primary Key
Country_Name

table_Coins
Coin_ID - primary key
Country_ID - Foreign key (linked to table_Countries)
Coin_Description_1 (contains stuff like cent, pence, dollar etc.)
Coin_Value (contains 1, 2, 5 etc.)
Coin_Image_Path
Coin_I_Have_It (yes / no )

The above is just a sample but it should give you an idea of which fields go in which tables.

Steve.

Thank you for the sample, Steve, but if I insert all coins into one table, will I be able to see the coins next to each other in a form? (like the one in attachment in my first post?) I'd really like to see all coins per country without having to scroll or go to a next record.

Well he shows you how to identify the tables involved, and what goes in which table. Why not start by looking at his starting point -- a description of the business/problem indicating how things fit together. Just try and see if, using his rules and comments, you get the same diagram that he does. He has solutions, but the learning is by reading and doing. Solve his already solved problem by doing it.

Then write a description of what your database/business is about. Follow his verbiage and use your terms. Identify the tables and the fields involved.

You are not going to learn how to analyze a situation/problem and solve it without some effort. You seem, to me at least, to be hoping for some magical epiphany or osmosis to bring knowledge and skill into your head. It doesn't happen that way with database. You may have natural talents for basketball/hockey/baseball/soccer etc, but in my view you're going to have to do some work to learn the basics.

And, once you learn the basics and the general process, you can design most any database. The forum is here to assist and advise, but we won't do it for you. You show an honest effort and most forum participants will help.

There is software out there for coin collection. This one offers a free trial. You might want to try it, just to get some idea of the tables and fields involved.

Good luck.

I'm not asking you to do it for me I was just hoping to get some advice on how to start this whole thing. I already tried multiple times and it never worked.
I'm surprised that it's so difficult to create a simple overview of my collection. I didn't think it would take me this long (and I still haven't succeeded! )

And the software isn't an option btw. I've tried several programs, but none of them show me what I want to see.

 

[jdraw]

Nicky,
If you honestly think you're going to go from 0 to a fully functional coin collection database with an easy to use interface and reporting facilities in a weekend or a few hours, you are extremely naive or overestimating your learning abilities or in dreamland.
You have been given ideas and tips. Do the tutorial.
However, based on this quote

I've tried several programs, but none of them show me what I want to see.

I think you had better write down a list of the things you want to see. Then post it here and get some focused responses and/or options. But we don't know what exactly you want to see, and my guess is - you don't either.

Take some time, write down a list in point form and show the readers.

Here's a step by step process from Barry Williams' site
http://www.DatabaseAnswers.org
re an approach to Database Design

****************************************************

In this General Approach we define the Steps in a structured method to design a Database, and there
is another Approach at the bottom of the page.

You can have a look at this Page to see how this Approach applies to the design of a Database for an HR Department.
The Approach defined here is aimed at beginners and experienced practitioners.
It makes some recommendations to simplify basic design decisions on key structures.

These are the Steps in a Top-Down Approach :-

    Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
    Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
    Analyze the Things of Interest and identify the corresponding Tables.
    Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
    For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities. If you are just starting out, I suggest that you postpone this level of analysis.
    At this point, you can produce a List of Things of Interest.
    Establish the relationships between the Tables.
    For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
    Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
    Identify the Static and Reference Data, such as Country Codes or Customer Types.
    Obtain a small set of Sample Data,
    e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
    "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
    Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
    For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
    Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
    You need to define a Primary Key for all Tables.
    For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.
    I recommend that names of Reference Data Tables all start with 'REF_'.
    For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
    This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
    However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
    It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be. Which means, of course, that it often never gets done.
    Confirm the first draft of the Database design against the Sample Data.
    Review the Business Rules with Users,(if you can find any Users).
    Obtain from the Users some representative enquiries for the Database,
    e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
    Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
    Development staff, etc. and repeat until the final Database design is reached.
    Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

****************************************************

There are other approaches, but this is general and should get you started.

Good luck.