Transform Excel table into Access format

[jaryszek]

Uptade:

I have found very interesting article:

http://www.fmsinc.com/Microsoftaccess/query/crosstab-report/index.html

Here i can see that all dates are write into records and there is only one column for date.

So in my sample database i should do something similar : combine documents with only one field date and for each document there should be 6 rows.

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

later i will do a form where user will be writing money for each month...
This is a good approach Guys?

I am courious it is possible using table normalization data or i have to use only VBA to do it?

Jacek

 

[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

 

[Uncle Gizmo]

Also, see my Blog:-

https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access

A couple of examples, several videos, and a free tool to help convert excel layout to MS Access layout....

Sent from my SM-G925F using Tapatalk

Early on in this thread I posted the above:-

You said something like "This is not easy for me" I'm not sure if you were referring to my contribution or not.


I would suggest you revisit my blog. You will find text versions and video versions, along with sample files, and a free tool for converting your Excel type data into Access type data.

If you have any difficulty with the instructions, difficulty in using the tool, in understanding what's going on, then please post back here and I will do my best to help.

 

[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