Transform Excel table into Access format
- Thread starter jaryszek
- Start date
Hi plog,
I would like to normalize table data from Excel to Access in best efficient way (Tab1 in example database - this is an Excel table for normalization).
1. How to make this database relational ?
I have build relation between table EmloyeeDocuments and Employees - could you please see at it?
The idea for whole database is:
- User can input Person data, automatically JobPart will be attaching to it, and all documents connected to this person.
Additionally there should be a text boxes where for each month (from january to june) user should write the money for each document for each name, for example:
Person, JobPart, DocumentName, WorkDate, Money
John, 1 , Dokument1, ,2017-01-01, 1000
John, 1 , Dokument1, ,2017-02-01, 500
John, 1 , Dokument1, ,2017-03-01, 10
John, 1 , Dokument1, ,2017-04-01, 100
John, 1 , Dokument1, ,2017-05-01, 1
John, 1 , Dokument1, ,2017-06-01, 22
John, 1 , Dokument2, ,2017-01-01, 12
John, 1 , Dokument2, ,2017-02-01, 33
John, 1 , Dokument2, ,2017-03-01, 2
John, 1 , Dokument2, ,2017-04-01, 55
John, 1 , Dokument2, ,2017-05-01, 34
John, 1 , Dokument2, ,2017-06-01, 55
I am trying to do it with user-friendly way in Access so I am asking about help.
Best wishes,
Jacek
I would like to normalize table data from Excel to Access in best efficient way (Tab1 in example database - this is an Excel table for normalization).
1. How to make this database relational ?
I have build relation between table EmloyeeDocuments and Employees - could you please see at it?
The idea for whole database is:
- User can input Person data, automatically JobPart will be attaching to it, and all documents connected to this person.
Additionally there should be a text boxes where for each month (from january to june) user should write the money for each document for each name, for example:
Person, JobPart, DocumentName, WorkDate, Money
John, 1 , Dokument1, ,2017-01-01, 1000
John, 1 , Dokument1, ,2017-02-01, 500
John, 1 , Dokument1, ,2017-03-01, 10
John, 1 , Dokument1, ,2017-04-01, 100
John, 1 , Dokument1, ,2017-05-01, 1
John, 1 , Dokument1, ,2017-06-01, 22
John, 1 , Dokument2, ,2017-01-01, 12
John, 1 , Dokument2, ,2017-02-01, 33
John, 1 , Dokument2, ,2017-03-01, 2
John, 1 , Dokument2, ,2017-04-01, 55
John, 1 , Dokument2, ,2017-05-01, 34
John, 1 , Dokument2, ,2017-06-01, 55
I am trying to do it with user-friendly way in Access so I am asking about help.
Best wishes,
Jacek
From a purely relationship standpoint the relationships look fine. However, logically they don't make sense. I mean, according to your relationships an employee can only have one EmployeeDocument?
It might be best if you stepped back from all database things for a moment and explain how the real world process this database is for works. Explain what your organization does. Do not use any database jargon, don't tell me about drop downs and text boxes, simply explain what real world process this is for.
It might be best if you stepped back from all database things for a moment and explain how the real world process this database is for works. Explain what your organization does. Do not use any database jargon, don't tell me about drop downs and text boxes, simply explain what real world process this is for.
Minty
AWF VIP
- Local time
- Today, 17:01
- Joined
- Jul 26, 2013
- Messages
- 10,640
@Plog - Frothy has picked up this on another thread - they are crossing over enormously. https://www.access-programmers.co.uk/forums/showthread.php?t=294029
Minty,
thank you for your post but Employees from this process are different from Employees from second process (from Different System, unable to join them).
I am learning also creating relational database here, in second post there are method to choose multiple documents for users - this is different.
I have to drill into this topic to learn this...
Please help and do not deny it...
Jacek
thank you for your post but Employees from this process are different from Employees from second process (from Different System, unable to join them).
I am learning also creating relational database here, in second post there are method to choose multiple documents for users - this is different.
I have to drill into this topic to learn this...
Please help and do not deny it...
Jacek
Yes logic is the most important here plog ;-)
Employee can have a lot of kinds of documents. (so one employee - a lot of documents).
Ok, So:
Accountant has data for Person(Name and surname) from SAP - NameOfDocument, WorkTime.
And from paper Accountant has to write for all months (for first half an year - january to june) all sums into database. This is needed for client's report.
So i need an userform for do it simply and user-friendly.
So user should choose Person, and for each document attached to this person write manually for each month all sums from paper...
thank yuo for helping,
Jacek
Employee can have a lot of kinds of documents. (so one employee - a lot of documents).
Ok, So:
Accountant has data for Person(Name and surname) from SAP - NameOfDocument, WorkTime.
And from paper Accountant has to write for all months (for first half an year - january to june) all sums into database. This is needed for client's report.
So i need an userform for do it simply and user-friendly.
So user should choose Person, and for each document attached to this person write manually for each month all sums from paper...
thank yuo for helping,
Jacek
Minty
AWF VIP
- Local time
- Today, 17:01
- Joined
- Jul 26, 2013
- Messages
- 10,640
Are the employees all based in the same company but different departments?
Add a department field and filter your employee lists to reflect the change and keep them in one place. Just because they are currently spread about in disparate systems doesn't mean that is the best way forwards.
Add a department field and filter your employee lists to reflect the change and keep them in one place. Just because they are currently spread about in disparate systems doesn't mean that is the best way forwards.
There are in different departments, different systems.
This is not possible to do it in one big database. The data are totally different.
First database is for clients as companies, second is for clients as employees.
I not want to join this data. Additionally I can not combine this data - managers sets up process in my company.
Please focus to solve the problem connected with this database.
Jacek
This is not possible to do it in one big database. The data are totally different.
First database is for clients as companies, second is for clients as employees.
I not want to join this data. Additionally I can not combine this data - managers sets up process in my company.
Please focus to solve the problem connected with this database.
Jacek
Please focus on answering my questions:
Explain how the real world process this database is for works. Explain what your organization does. Do not use any database jargon, don't tell me about drop downs and text boxes, simply explain what real world process this is for.
Explain how the real world process this database is for works. Explain what your organization does. Do not use any database jargon, don't tell me about drop downs and text boxes, simply explain what real world process this is for.
No it is not. You start of by describing the fields of a database system, you talk about a report, mention sums in a database, talk about another report, talk about a form and how you want it to operate.
No database jargon. It's career day at an elementary school. Explain to the kids what it is your business does.
No database jargon. It's career day at an elementary school. Explain to the kids what it is your business does.
So is this understanding correct :
Each Person works on a number of Documents each month. Time spent on each document is recorded and needs to be entered (once per Person/Document per month ?)
Each Person has many Documents.
Can any Document be worked on by more than one Person ?
What sort of numbers of Documents and Persons are we talking about ?
Each Person works on a number of Documents each month. Time spent on each document is recorded and needs to be entered (once per Person/Document per month ?)
Each Person has many Documents.
Can any Document be worked on by more than one Person ?
What sort of numbers of Documents and Persons are we talking about ?
BeeJayEff thank you, you are understanding me ;-) (i know this is hard 
plog thank you also for your support end explanations.
Yes exactly
Work Time is stable, this is relation: Person - WorkTime (1:1) - John always will have WorkTime 1 and Smith always will have WorkTime 0,5
Here it is no time, here is money spent on each document which needs to be recorded (one Person and few documents attached to him per month)
Yes it is true
Yes, documents can be repeated for many Person
This is good question.
I think it will be about 20 person per week and about 3-5 documents for them per week.
Thank you Guys for you help,
Jacek
plog thank you also for your support end explanations.
Yes exactly
Work Time is stable, this is relation: Person - WorkTime (1:1) - John always will have WorkTime 1 and Smith always will have WorkTime 0,5
Here it is no time, here is money spent on each document which needs to be recorded (one Person and few documents attached to him per month)
Yes it is true
Yes, documents can be repeated for many Person
This is good question.
I think it will be about 20 person per week and about 3-5 documents for them per week.
Thank you Guys for you help,
Jacek
Frothingslosh
Premier Pale Stale Ale
- Local time
- Today, 12:01
- Joined
- Oct 17, 2012
- Messages
- 3,276
Honestly, man, this should be in the same application as the other, just in a different menu.
Admittedly, there's not a lot you can do if the law has come down from On High, but since the databases share data, they should be combined if at all humanly possible.
Regardless of that, however, the two client databases really should be combined into one. What you're currently doing is actually storing important data (Client Type) in the database NAME, not in the database, and it sounds like other than that, the databases should literally be identical.
Admittedly, there's not a lot you can do if the law has come down from On High, but since the databases share data, they should be combined if at all humanly possible.
Regardless of that, however, the two client databases really should be combined into one. What you're currently doing is actually storing important data (Client Type) in the database NAME, not in the database, and it sounds like other than that, the databases should literally be identical.
Thank You Froth.
I can't combined it unfortunately - this is my manager will. Sorry i know that it is not good way of building database but i can do nothing - i am only employee not manager.
Databases doesnt share data
This data is from different system and for Person not companies - data are totally different.
The biggest problem for me is how to write amounts for each month for each document for each person in easy way.
But i will try to do sample database and share my results here.
Jacek
I can't combined it unfortunately - this is my manager will. Sorry i know that it is not good way of building database but i can do nothing - i am only employee not manager.
Databases doesnt share data
This data is from different system and for Person not companies - data are totally different.
The biggest problem for me is how to write amounts for each month for each document for each person in easy way.
But i will try to do sample database and share my results here.
Jacek
Minty
AWF VIP
- Local time
- Today, 17:01
- Joined
- Jul 26, 2013
- Messages
- 10,640
Your manager presumably isn't a database person. You are. You need to explain to him the wasted effort that will be required (for him read money) if you have to develop and people have to keep switching between two different systems. this is all about streamlining and improving process and helping to get the job done and the money in.
You have been asked to provide this solution, at the moment your manager is tying your hands to a solution that is not ideal, and will not save / generate the best return.
Sell the ideas to him, even make them sound like they are his ideas, if it helps...
You have been asked to provide this solution, at the moment your manager is tying your hands to a solution that is not ideal, and will not save / generate the best return.
Sell the ideas to him, even make them sound like they are his ideas, if it helps...
Minty,
thank you that you are involed and advise me in the matter of my Employer.
But I wish i could change something.
I was talking with managers, with boss and this wasnt help me unfortunately.
Even my wife is laughing that my comapny is strange
They want to have in this way and i can do nothing so please focus on this dabase and setting it as relational.
Sorry i tried.
------------>
Now i would like to focus on this database and setting relations.
Here is simple database and i don't know exactly how to relate dates with documents
(1 document and 6 dates connected to it). I am trying but with no success yet..
Best Wishes,
Jacek
thank you that you are involed and advise me in the matter of my Employer.
But I wish i could change something.
I was talking with managers, with boss and this wasnt help me unfortunately.
Even my wife is laughing that my comapny is strange
They want to have in this way and i can do nothing so please focus on this dabase and setting it as relational.
Sorry i tried.
------------>
Now i would like to focus on this database and setting relations.
Here is simple database and i don't know exactly how to relate dates with documents
(1 document and 6 dates connected to it). I am trying but with no success yet..
Best Wishes,
Jacek
Last edited:
Update:
1. I am trying to figure out relations between tables:
https://ibb.co/dfH1iQ
EmloyeeDocumentID in table EmployeeDocuments is connected with EmployeeDocumentID in Employees table. Employes are unique.
So each Employee can have multiple documets attached to them.
Now users should input for each Person new document (new record ) in table Employess?
https://pasteboard.co/bF5TeXXE.jpg (John is example here).
Hmm i think that here is missing something...
In attachment there is a John inouted by user. User should know what documents are attached to each Person but fullfiling all records manually...?
I imagine that i can add only one Person name and access automatically for example using query will add each documents for this Person.
2. In EmployeeDocuments i would like to live only DocumentName field and relate this table with new one with Dates attached to documents and field money.
How can i relate?
Please help,
Jacek Antek
1. I am trying to figure out relations between tables:
https://ibb.co/dfH1iQ
EmloyeeDocumentID in table EmployeeDocuments is connected with EmployeeDocumentID in Employees table. Employes are unique.
So each Employee can have multiple documets attached to them.
Now users should input for each Person new document (new record ) in table Employess?
https://pasteboard.co/bF5TeXXE.jpg (John is example here).
Hmm i think that here is missing something...
In attachment there is a John inouted by user. User should know what documents are attached to each Person but fullfiling all records manually...?
I imagine that i can add only one Person name and access automatically for example using query will add each documents for this Person.
2. In EmployeeDocuments i would like to live only DocumentName field and relate this table with new one with Dates attached to documents and field money.
How can i relate?
Please help,
Jacek Antek
Last edited:
