Big Database-How can i create and find specific clues in access ? (basic chronologies)

[Michael1926]

Hello everyone. My name is Michael and i'am a new member in the forum.



I want to tell you about my problem that i confront with access.

I have a big database in Excel and i want to pass this base in Access. I know how to do it but the problem is that the db in Excel has some wrongs and it must be created again from the beggining, so i decided to build up the new db in access.

The problem is that i don't know how to create the right columns and the specific Querries. For example,

My d.b. is all about the Magazines.

In the first Table i insert the below:

I've one Column with the Title
I've a second Column with the Publisher
I've a third Column with the Order like this -> M1, M2, M3, M4 etc (meaning magazine 1, magazine2, etc)

In the second Table i insert the below:

In the first Column is when the magazine published
in the second Column if it's new or old

and in the third Table i have the chronologies.

I want to know how to find the magazines basic chronology. For exampe the d.b in Excel is like this:



chronologies is like that-> 1901 1902 1903 1904 1905 1906 1907 etc until 2019

Magazine title
-------------
Time 1
Satisfaction 1 1 1
National Geographic 1 1 1
People 1 1 1
Famous Singers 1 1
Rock And Roll 1 1 1 1 1 1 1

etc

For the table above the Magazine "Time" excists only in 1901 , the magazine "Rock and Roll" excists in 1901 until 1907 etc

So, in Excel is like this when i want to found which magazines are excisting in the date of 1904 i find the date 1904 i press the button filter and it shows me the results. But this db is so big and the way is pretty strange and hard to find everytime these "1" in everydate.

Also, if i want to find a way to see how many magazines are excisting in the period of time in 1901 until 1905 how can i ?


The important of all this stuff is that i cannot find a way to search the clues basic Chronology. For example i want to find how many magazines are excisting in 1998 which there are a lot of magazines with this date and thats a problem. Also how to find how many magazines are excstiong in a specific decade, like 1970 until 1980 which is also a problem for me.

Note that a magazine can be in a different time, like excisting in 1901 1902 1903 and the republishing again in 2000 2001 etc , also a problem.


I dont know if i became very clear as far as my problem with db is concerned so my apologies for my text and the examples if there are not so clear.


Best regards , Michael.

[ArviLaanemets]

tblMagazines: MagazineID, [Publisher], [Title], ...
MagazineID is an autonumeric primary key for table. It is menat for application internal use - usually such keys are hidden from application users.
In square brackes are names of fields which may be linked to other tables in case those can change over time. E.g. You can have a field Publisher in tblMagazines, but when there are magazines which had different publishers over time, then you need publisher name in separate table instead, something like tblMagazinePublishers: MPID, MagazineID, PublisherID, ValidFrom. Plus a table where all publishers are registered.
The same for magazine name. When for every magazine there is a single name over history, then you can have magazine name in tblMagazines. When even a single magazine name was changed over time, you need a table, where you register magazine names history, like tMagazineNames: MNID, MagazineID, MagazineName, ValidFrom.
When you must have publishers or names history, you can have according field in tblMagazines too anyway. Then you register publisher or name changes only, when such changes exist - otherwise it is read from tblMagazines.

tblMagazineNumbers: MNID, MagazineID, MagazineNum, PublishedDate, ...

I see no need for specific table to register chronologies - PublishedDate field in tblMagazineNumbers has same functionality.

It remains a mystery for me, what you mean with "new or old"! Do you have several exemplars of same number of magazine, and you need an overview about quality of those copies? When yes, then you need a table
tblItems: ItemID, MNID, ItemRegNo, ItemCurrentQuality

[Michael1926]

First of all, thank you a lot for the answer!!! You gave me a direction how to create the db, at least now, i've an idea how to start. I think, i have a lot of work to do for my database, so many tables and names but thats the challenge : P.

What i meant with new or old ,is that a magazine which published before 2000, i consider it, as an "old" and after 2000 as a "new". Now, it would be a good idea to make that table you mentioned with the quality, it will be very helpful.

also i wanted to ask you: Can i print the specific features which access will show me? Like, the magazine "Time", in how many dates are showed in the db. The answer can be printed or must be written by me?

[ArviLaanemets]

also i wanted to ask you: Can i print the specific features which access will show me? Like, the magazine "Time", in how many dates are showed in the db. The answer can be printed or must be written by me?

You design a report based on query. In reporting form, you select a report you want to create, select filters, and click on button to run the report. The VBA procedure called by button event reads input data from form, and runs the report according them. You can preview the report, print the report directly from button, or using Windows print command from preview, sent it as mail attachment, etc.

[ssanfu]

Arvil has good suggestions for you, but you seem to be in Excel mode. There is even a term for that: Committing Spreadsheet. Excel and Access are two VERY different animals. About the only thing they have in common is that they can be programmed using VBA.


You should work through these tutorials before doing anything else. Don't just watch them - do them.

The next thing is to try and design the tables using paper and pencil, whiteboard, cardboard, etc.
===============================================
"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
===============================================

Then read about Normalization. Here are some links:
What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


Entity-Relationship Diagramming
==============================
Entity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html
Entity-Relationship Diagramming: Part II http://rogersaccessblog.blogspot.com...g-part-ii.html
Entity-Relationship Diagramming: Part III http://rogersaccessblog.blogspot.com...-part-iii.html
Entity-Relationship Diagramming: Part IV http://rogersaccessblog.blogspot.com...g-part-iv.html


The Normal Forms
=========================
The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html




THEN, you can get into Access and start creating objects.
Here is a link to Naming Conventions

Some other naming suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.


Post is you have questions about your design...

Good luck with your project.....

[Michael1926]

You design a report based on query. In reporting form, you select a report you want to create, select filters, and click on button to run the report. The VBA procedure called by button event reads input data from form, and runs the report according them. You can preview the report, print the report directly from button, or using Windows print command from preview, sent it as mail attachment, etc.

Thank you very much for your help! and the answers.

[Michael1926]

Thank you a lot for the answer! The links and infos you gave me, was very educational At the moment i'am in the last part and i think i need to revise from the beggining to make more clear the terms and the usages of "Normalization" and "Relationships". I also want to ask you a question as far as the tablemagazine is concerned for my example.

I've the tablemagazine and the facts are
1) tablemagazine can have a lot of potential chronologies and the opposite so Relation all - all
2) tablemagazine can have a lot of potential titles (M1,M2,M3 etc) and the opposite so relation all - all
3) tablemagazine can have a lot of potential public locations and the opposite so relation all - all

My main characteristic is the tablemagazine. So i have to choose the "Tablemagazine as a foreign key" right? and the other keys will be the foreign? The primary key can be the autonumberd at any relation right?

[Michael1926]

Arvil has good suggestions for you, but you seem to be in Excel mode. There is even a term for that: Committing Spreadsheet. Excel and Access are two VERY different animals. About the only thing they have in common is that they can be programmed using VBA.


You should work through these tutorials before doing anything else. Don't just watch them - do them.

The next thing is to try and design the tables using paper and pencil, whiteboard, cardboard, etc.
===============================================
"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.
===============================================

Then read about Normalization. Here are some links:
What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


Entity-Relationship Diagramming
==============================
Entity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html
Entity-Relationship Diagramming: Part II http://rogersaccessblog.blogspot.com...g-part-ii.html
Entity-Relationship Diagramming: Part III http://rogersaccessblog.blogspot.com...-part-iii.html
Entity-Relationship Diagramming: Part IV http://rogersaccessblog.blogspot.com...g-part-iv.html


The Normal Forms
=========================
The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html




THEN, you can get into Access and start creating objects.
Here is a link to Naming Conventions

Some other naming suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.


Post is you have questions about your design...

Good luck with your project.....

Thank you a lot for the answer! The links and infos you gave me, was very educational At the moment i'am in the last part and i think i need to revise from the beggining to make more clear the terms and the usages of "Normalization" and "Relationships". I also want to ask you a question as far as the tablemagazine is concerned for my example.

I've the tablemagazine and the facts are
1) tablemagazine can have a lot of potential chronologies and the opposite so Relation all - all
2) tablemagazine can have a lot of potential titles (M1,M2,M3 etc) and the opposite so relation all - all
3) tablemagazine can have a lot of potential public locations and the opposite so relation all - all

My main characteristic is the tablemagazine. So i have to choose the "Tablemagazine as a foreign key" right? and the other keys will be the foreign? The primary key can be the autonumberd at any relation right?

[ArviLaanemets]

So i have to choose the "Tablemagazine as a foreign key" right? and the other keys will be the foreign? The primary key can be the autonumberd at any relation right?

Is Tablemagazine the name of Primary Key in magazines table? You can any field have any names you like, but it is better, that after couple of years you can look at your table design, and understand what is stored in all those fields. Especially is it essential with Primary/Foreign keys. Looking at contents of such fields don't give you any clue, what is for what.

For ID fields here are 2 main shools.
Some of us mark all primary keys with PK, and all foreign keys with FK (like PK_KeyName_ID, or KeyName_PK).
Others (I myself too) prefer to use same name for Primary Key in parent table, and Foreign Keys in all child tables. When you don't set table relations at project level, then whenever you create a query, Access creates relations for tables used in query for this query automatically, and sets those relations based on keys Access recognizes as related - the decision is based on key name. When keys have different names, you have to set those relations manually - and I that didn't want to bother with it is one reason I didn't define them at start And it is possible (haven't tested it), that without relations defined for query, access doesn't use indexes at all - which makes query slow.

Every table must have a Primary Key. Unless you have in your table some field, which is unique (there is never more than 1 row with same value), and can be never empty, the easiest way is have an autonumeric field as Primary Key (and later it makes creating queries easier, when most of query conditions have numeric values).
Every table which is linked with another (one or several rows of data in child table belong to specific row in parent table) must have a Foreign Key additionally to Primary Key. Foreign Key values must match with proper Primary key value in parent table. When you use autonumeric fields as Primary keys, matching Foreign Keys must be long integer datatype.

About relationships at project level - there are reasons to define them, and there are reasons not to do so. As I use multi-form solutions, I seldom define them (I had occasions, where defined relations did not let me to design form as I wanted).