Transform Excel table into Access format

[jaryszek]

Hi Guys,

in attachment there is an example Excel workbook.

I have a table with Work Time, Person, name of document and months and sums connected to it.

I would like to do the same table in Access using access functionality in order to give users possibility to input data in easier way then in Excel and have additionally big database from it

All help will welcome

Thank you
Best wishes
Jacek

 

[plog]

Read up on normalization: https://en.wikipedia.org/wiki/Database_normalization

That's the method of properly structuring your tables. The hardest thing people have when learning normalization is overcoming the fact that tables often do not mimic the desired output. Your tables will probably not look like that spreadsheet, instead you will use queries to achieve that. Focus on setting up your tables correctly, then worry about having everything look like that spreadsheet.

 

[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

 

[jaryszek]

Thank you Guys,

I was reading your links and blogs and this is not simple for me.
I have access table (in attachment) cloned from Excel and I would like to ask you how to start ?
Where should I look to find the solution?

My idea is:

1. create table with primary Key ID, Person
2. create second table with Key ID amd name of documents and work time
3. Join this 3 tables together and input data for each useR?

It make no sense.

My users only have to input data for each name and for each document attached to it for each month. So for example For Person John for document 1 and for work time 1 user should input all dates in date columns (01-2017 to 06-2017).
How can i transform data here?

Please help and sorry for newbie question
Jacek

 

[Minty]

You should never have columns with values as their field names. Those values should be stored in a field. So in your example you would store

PersonID, DocumentID, TheValue, WorkTime , WorkDate

What does Work Time represent?
What is The Value in your spreadsheet

 

[jaryszek]

Thank You Minty for your tip,

WorkTime is a column which represents string with time of work for cpecific Employee.
For example for Person John there is 1 - full time job but for Bil there is only 0,5 - it is 1/2 of full time job (part time job).

Value is an money field (string can be).

In attachment there is new database from me with Tab2 where i moved date values into records.
It is a good approach ?

Jacek

 

[Minty]

That's much more like it, however you need to store what you want to work with, not what you currently have.

Your workdate field must be a date field if you want to group by weeks, months or years, storing it as a string will lead to no end of issues.

Similarly your Value must be a number or decimal field if you want to add them up. Don't Call the field Money(Value) ,or anything else with spaces or brackets in it. Value is a reserved word choose something else LineValue?

If you store the employee id (not their name) you do not need to store the working time as you can pull that in from the employee record.

Again Store the documentID which you should have from your other table of documents not the document name, much more efficient, and less error prone. You can display the document name on the form by linking it to your document list table.

 

[jaryszek]

Thank you Misty!

It is clearer right now, but few more steps to do it finally

That's much more like it, however you need to store what you want to work with, not what you currently have.

Your workdate field must be a date field if you want to group by weeks, months or years, storing it as a string will lead to no end of issues.

Similarly your Value must be a number or decimal field if you want to add them up. Don't Call the field Money(Value) ,or anything else with spaces or brackets in it. Value is a reserved word choose something else LineValue?

I did as you said.

If you store the employee id (not their name) you do not need to store the working time as you can pull that in from the employee record.

I did TAB_Employee (you can see it as attachment) with EmployeeID as number, Person and Work Time.

Again Store the documentID which you should have from your other table of documents not the document name, much more efficient, and less error prone. You can display the document name on the form by linking it to your document list table.

I did Tab_Document with DocumentID as number and name of documents.

So now I am prepared to do relational database?

How to start doing it?

Thank you once again!
Best Wishes,
Jacek

 

[Minty]

Well that sounds okay for those two parts.
You really now need to work out what you want the database to actually do for you. If you get the design and planning wrong you will end up re-writing things after the event.

Write down with good old fashioned pen and paper what your process currently is.
Identify the steps and at each step what you would need to record or process to "see" that in a database.

If you can't process it on paper you can't do it in the database.

You will probably end up with a list of reports / things you would like to see. These are the outputs from your database, they may be reports or forms to display list(s) of items, summary reports, summary forms. Don't worry about those at this point, but make sure you know what they need to display.

Once you know what you are trying to get to , you will then have a much better idea of what you need to store.
Only you can do the parts up to here. We don't know your business or your processes.

However we do have a reasonable idea of the best practice ways to store and manage your data, once you know what you need to do with it. And I'd be amazed if there isn't a close fit to an existing data model for what you need.

 

[jaryszek]

Thank you Minty for your answer!

I know what is purpose: I want to see for each user for each kind of document (document1, document2, and document3) for first half an year all sums which where spent. (maybe this pen and paper is working ? ;-))

Like in Excel spreadsheet.

Form should have fields:
1. Person
2. Work Time
3. Name of documents
4. field for every sum within each month

The 4th point is the most difficult for me. I can not imagine how to add sums for each person for each month. Do an checkboxes and coressponding fields? Or maybe simple prepare 6 tex boxes with allow users to input every sum? Maybe this is good idea.

I will tomorrow try to prepare simple Form and show it to you.

Thanks for helping,
Jacek

 

[jaryszek]

Okey I have problem with that.

I can start new form from my Tab2 (in attachment above sample database) and input few comboboxes and write code in VBA (put all fields - ID, WorkTime, Person, Name of document, WorkData, Money) but i dont know if it is the good way.

The best solution (i think) will be create relational database.
Here I have a problem, my thoughts:

I would like to Join Tab_Employee with Tab2 (my Main table) and Tab_Document and:
Tab2 has already Primary Key (autonumber) and this will not joinining with Tab_Employee (only 1,2 and 3 as Key)

Name of document in Tab_document is a string table with document ID - i dont know how to join this with my Main Tab.

And one more thing,
I suppose that my Main tab should be created from my small tables as relational database.

Could you please help me with making this?
I have to learn it.

Thank you very much,
Best Wishes,
Jacek

 

[jaryszek]

Okey I have problem with that.

I can start new form from my Tab2 (in attachment above sample database) and input few comboboxes and write code in VBA (put all fields - ID, WorkTime, Person, Name of document, WorkData, Money) but i dont know if it is the good way.

The best solution (i think) will be create relational database.
Here I have a problem, my thoughts:

I would like to Join Tab_Employee with Tab2 (my Main table) and Tab_Document and:
Tab2 has already Primary Key (autonumber) and this will not joinining with Tab_Employee (only 1,2 and 3 as Key)

Name of document in Tab_document is a string table with document ID - i dont know how to join this with my Main Tab.

And one more thing,
I suppose that my Main tab should be created from my small tables as relational database.

Could you please help me with making this?
I have to learn it.

Thank you very much,
Best Wishes,
Jacek

 

[jaryszek]

Could anyone take an glance at it ?

Thank you!

Jacek

 

[plog]

I'm looking at the last screenshot you posted. First the ticky-tack things:

1. Poor table names. Name your tables after real world objects of the system you are modeling. 'Tab2' and all the other 'Tab' prefixes are not helping me to understand what data those tables contain. Seems like you can't let go of Excel. Without knowing anything about your data and just looking at the fields it contains, I would suggest 'EmployeeDocuments' instead of Tab2.

2. Spaces in names. You should only use alphanumeric and underscores for field and table names. [Name Of Document] should be turned into [NameOfDocument], or better yet, [DocumentName].

Now the bigger stuff.

3. Use Date/Time fields correctly. Those fields can hold both the date and the time, you don't need two of them. So the data in [Work time] and [WorkDate] in Tab2 can be put into just one of those fields.

4. Duplicated/redundant fields. Why do you have [Work time] and [WorkTime] fields in 2 different tables? Could those values ever be different in matching records? If not, you need to remove one of those fields, you don't duplicate data in a database.

5. Unlinked table. What's the purpose of Tab_Document and why is it independent of the rest of the system?

Also, why isn't Tab1 in that screenshot? Like I said before, you need to focus on setting up your tables correctly. Forget forms for now. Get your tables set up to accomodate your data.

 

[jaryszek]

Thank you plog ! Very much!

Ok:

1 and 2 I corrected.

3 --> I have changed WorkTime for JobPart because this is not the same as time value. It is number value only (part of job, for example 1 means full jon, 0,5 work only for
part-time job). Sorry, it could be misunderstanding.

4. These was an mistake.

5. Tab_1 is a blueprint or a pattern how my Excel table is looking like.
I want to transform this table into access format (data normalization).
And i wondering how.

Main problem for me:
1. Can I join relate table EmployeeDocuments (main table) with Employees table using Person field as primary key?
2. How to relate DocumentName from table EmployeeDocuments with table Documents - what key should i use?

Thank You Guys for your help and support!

Best Wishes
Jacek

edit:

I wanted to add that I see that :
1. I can delete Employee_ID field from Employee Table
2. Make Person as primary key
3. Create relation between person in EmployeeDocuments and person field in Employees table:

4. I do not see a purpose to have DocumentName in seperate table.

 

[plog]

Question 1--> Yes, but you shouldn't. You put an ID field in Employees, that is the field/value you should use as a foreign key in EmployeeDocuments. What happens when you get a 2nd and 3rd Bill? What happens when John transistions to Joan? Also, numbers are more efficient than text for this purpose.

Question 2-->Same idea--you use DocumentID from Documents in EmployeeDocuments...however...

Tables with only 1 real field of data, don't need to exist. If all Documents has is 1 real field (autonumber primary keys don't count as real fields--just fields that hold your data do), then you don't need that table at all.

Also, JobPart is done incorrectly. You don't need it in both Employee and EmployeeDocuments since those two tables are related. I don't fully understand what JobPart is, so could you explain it--that would let me know what to do with it.

 

[jaryszek]

1. Ok thank you- i understand your thinking plog.
So I should use field Person from table Employess as foreign key in table EmployeeDocuments?

Problem is that i can relate only Id_Primary field in EmployeeDocuments with Person field in Employee Table as foreign key.
ID_Primary is not matching with my Person field.

2. I deleted Documents table - it is not neccessary here.

JobPart - ok if you are working at full-time in Poland it is 40 hours per week.
Part time job (0,5 here) is only 20 hours per week. It is code which is used in my company , there is no need to use library fot it.

Yes you have right, only one PartTime field in one table should be.
But after end I need have all data from this 3 tables in one big table (junction table).

Thank you for your help,
Jacek

 

[jaryszek]

Up

Jacek

 

[plog]

Huh?

plog

 

[jaryszek]

I am asking for your help

Jacek