Transform Excel table into Access format

[Minty]

The data you have displayed appears to be normalized, but looking at your pictures you still haven't grasped the employee / document relationship.

The link you have posted explains how to get an output from properly normalized data into a non-normalized format - handy for reports to bosses. It is a very normal use of crosstab queries.

The easiest way I can describe very basic normalization is to think of storing the data Vertically - this is database style.
If you are storing data horizontally you are probably not normalized and are still thinking in spreadsheet storage. There is a link in my signature have a read.

 

[jaryszek]

Minty thank you !!!

I have read your link in your footer earlier, watching tutorials about data normalization and in theory i know that database should be in vertically way.
I know that i have to set up relations between my tables but i dont feel it entirely.
This is because of lack of experience so i am searching for help in yours Forum Guys.

I have idea how to do relation between documents and Person - it is relation many to many and i have to make junction table with it.
Next step will be making third table with documents ID and DateOfDocuments column.

And the hardest part - make user friendly form for all of this so making UI interface.

I will do sample database in Monday in order to show you.

Thank you once again,
Best Wishes for weekend
Jacek

 

[jaryszek]

Thank you virat8586.

I know how to linking Excel with Access. Now I am trying to create the sample database and transform my Excel file into Access Format (create relational database).

Ok what in the sample database (in attachment):
1. I created many to many relationship between Table Employess and Documents.
2. I created also query in order to check if this working (working)
3. I created Form connected to it.

This is starting to make sense
Problem is when I am changing document1 to for example "ddd" this document1 will be assign to all employees. I want to change it ONLY for one employee.

Ok with what i have problem here:
1. How to do adding to this database DateOfDocument and Money (for each month for each document?)

I think I have to create relationship between EmployeeDocuments table and table DocumentsWorkAndMoney.
I created the example relationship between EmployeeDocumentID in DocumentsWorkAndMoneyTable and EmployeeDocumentID in EmployeeDocuments table - it is one to one relationship:

https://ibb.co/iDGSA5

I would like to have result table like:

oUTPUT TABLE:
NameOfDocument, Date, money
document 1 , 01-01-2017
document 1 , 01-02-2017
document 1 , 01-03-2017
document 1 , 01-04-2017
document 1 , 01-05-2017
document 1 , 01-06-2017

What should i do?

Maybe create another junction table and many to many relationships? Or is the easiest way?

Please help,
Jacek

 

[jaryszek]

Hi Uncle Gizmo.

I know concept from your blog. thank you for your help!

I am closing the topic and starting new one because this is starting a little bit confusing...

I want to set up relational database in Access step by step.
In your tutorial (which is very good by the way) there is an option to transform table exactly from Excel to Access. I am here building sample database which is only based on Excel (it is a concept only).

Thank you for your help and support.

Jacek