Transfer from Excel to Access

[AlexR]

Hello everyone!

For a few weeks now I have discovered Access and its marvellous power.
I am working in a HR department and I have to be in charge with 400+ employees.
We keep track of them in Excel, but I would really like to transfer the entire information in Access as I think, on the long run it would be much easier to do all sorts of operations.
I have tried to import the excel files into Access (it’s impossible to just take each entry and paste it in Access) and I have managed to do some things.
But I came across a problem. I have the employees and I have the days off that they take each month.

Employee Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
E1............4...1.....0
E2............4...2.....1
E3........... 0...1.....0
.
.
.
E400

And I need a total for each employee because I need to file reports, which means total on rows, not columns.
While I was trying to find an answer to my question I came across this as a response to someone trying to do something similar:

"Stop...go directly to jail...do not pass GO...do not collect $200.

You should not be storing data like that. It is "spreadsheet design", not relational database design. If it's not too late, you should redesign the tables following the rules of normalization. Here's a start:____"
and there is a link to a site about relations and normalisation, but I am not able to post it.

I understand the answer, but I can’t see a way of redesigning my tables. For me this is logic: to have a list of people that runs down the page
My question is: is it actually possible to do what I have in mind (transferring my info to Access) or for something like this should I stick to Excel? I don’t have programming skills. I can pick a thing or two, but it’s entirely copy/paste with follow 1 2 3 steps to do it.

I love the fact that I can run queries and I have forms and I know that after I have my entire database I will be able to generate data much easier.

Thank you for your help,
Alex

 

[DavidAtWork]

There's no reason why you shouldn't import all your excel data into Access. You need to do some initial preparation of your speadsheet(s) beforehand though. If each table of data isn't already on separate worksheet, then each block of data needs to given a named range and that will allow you to import each range or worksheet.
Once all your data is in Access, then you can get to work on it's design, first by thinking about your data and how you should design your table structure with the appropriate level of normalisation.
The problem you mention above can be solved by using an update query, set the field 'Total', update to: [Jan]+[Feb]+[Mar] etc. There are several ways to do this total depending on where/when you want to use/display the value
David

 

[AlexR]

thank you for your answer!

The spreadsheets are nicely made and the import works quite well.
But since I have to make a lot of sums on rows and almost never on columns and since it's still not clear for me how can I do it, I have concerns about my idea of transfering all my data to access.

In any case, I will gladly accept any suggestions about how can I improve the process

Alex

 

[tehNellie]

The problem you mention above can be solved by using an update query, set the field 'Total', update to: [Jan]+[Feb]+[Mar] etc. There are several ways to do this total depending on where/when you want to use/display the value
David

Don't store calculated values in your tables. You're duplicating data, can calculate it using a query when you need it and don't run the risk of data that your total was calculated using being changed after the calculation has run.

In a more normalised format you'd have something along the lines of:

tblEmployees
-------------
EmployeeID - PK, autonumber
EmployeeName 
etc etc

tblholidays
-------------
HolidayID
EmployeeID
Period - Date, formatted to month/year so the day is irrelevant
daysTaken - integer

To total the holiday by employee can be achieved simply in a query

SELECT employeeID, employeeName, sum(daysTaken) as TotalHolidayTaken
From tblEmployees
INNER JOIN tblHolidays ON
  tblHolidays.employeeID = tblEmployees.employeeID
GROUP BY tblEmployees.EmployeeID, employeeName

A standard SELECT Query would give you the information in columns rather than rows:

Employee Month  DaysTaken
Dave   January     1
Dave   March       1
Dave   June        10
Fred   April       1

etc
But if you want to revert to a row format you can create a CrossTab query which essentially Pivots your data. Months where no holiday is taken can be catered for by replacing NULL with a 0 if you wanted them displayed.