Linking Excel Spreadsheet to Table- Please Help!

[Breanna]

This is going to be a long question but any information on the situation will be greatly appreciated.

I am creating a database for the company I work for. My boss wants it to have every information possible stored and accessible through the one database. Right now the time sheet that we use is a hard copy that the guys fill out and turn in each week. Before the database started I created an excel sheet for them to start using. My boss wants me to link the excel sheet to a table in access. The way it would work is each of the guys would fill theirs out and email them to me by Friday. I would then link it to the table so that we could easily pull information we needed through a query. I have no idea how to even start this. I read online on the office support website that you can link a spreadsheet but then access does not store the data and the data if needed to be changed had to be done through the excel spreadsheet. Doing it like that made it sound to me that each time sheet that was turned in each would would create a new table. I was hoping there was a way to have one table with all the times on it using an employee number as the relationship key. The biggest thing though is that the guys have to use excel to fill out the time sheet because getting each guy access is "not an option" (according to my boss)

As I mentioned I have no idea how to even start this. Or what key words to search for on Google. This is going to be a hard part of this project because most of what I know about access came from this forum (which is amazing by the way and everyone on here is super helpful and has been very patient with me) I have never used a program like this let alone set it up. Other ideas would be appreciated as well if what I need is too complicated or not possible.

The only thing I can think of for something like this to be on access is to have a form with the time sheet layout and a table with that all through access. But my boss doesn't want that.

Thank you in advance! I know this is asking alot.

 

[pbaldy]

Rather than link to it, I would import each into a table that contained all timesheet data. You could temporarily link to each and use an append query to pull the data into the main table, or use TransferSpreadsheet to import.

 

[Frothingslosh]

In this case, having created a similar app, you're definitely better off importing the data rather than linking it. If you link, then you need to either link to one spreadsheet with a summary of ALL the spreadsheet data, or you'll need to create new links for every employee every week, which will get WAY out of hand.

It wouldn't be difficult (for an experienced user - you may need help if you're not too familiar with VBA) to create a routine that would import the required data from each submitted time sheet using TransferSpreadsheet (or even direct worksheet reading).

Also, while getting each person Access may not be an issue, there IS a free runtime program that can be acquired from Microsoft and installed on each machine, that can then be used to run a special run-time version of your database. We will, however, hold off on burning that bridge until we get to it.

Where you should start is here:

Get a detailed, point-by-point description of what your boss wants this database to hold, and what he wants it to do. You can use Access to create anything from a simple hours-tracking system to a personnel management system to a job tracking and forecasting system. Find out what exactly what you're going to need to implement before you even THINK about firing up MS Access.

 

[Breanna]

Rather than link to it, I would import each into a table that contained all timesheet data. You could temporarily link to each and use an append query to pull the data into the main table, or use TransferSpreadsheet to import.

I tried to import a spreadsheet like you suggested. (although I do not know how to do that to an existing table) The issue I had with that is that the excel spreadsheet is not in a table format. I attached a prntscr copy of part of the timesheet to show what I mean. Having it layed out that way makes it impossible to just import it. Unless there is a way or formula or code or something to set it up so that when imported each cell in access table pulls info from a specific cell in the excel spreadsheet. If I could do it that way then importing would work great because the cell numbers in excel wont change for each employee.

I do not know what append query or transferspreadsheet is but I am going to look those up now.

 

[Breanna]

In this case, having created a similar app, you're definitely better off importing the data rather than linking it. If you link, then you need to either link to one spreadsheet with a summary of ALL the spreadsheet data, or you'll need to create new links for every employee every week, which will get WAY out of hand.

It wouldn't be difficult (for an experienced user - you may need help if you're not too familiar with VBA) to create a routine that would import the required data from each submitted time sheet using TransferSpreadsheet (or even direct worksheet reading).

Also, while getting each person Access may not be an issue, there IS a free runtime program that can be acquired from Microsoft and installed on each machine, that can then be used to run a special run-time version of your database. We will, however, hold off on burning that bridge until we get to it.

Where you should start is here:

Get a detailed, point-by-point description of what your boss wants this database to hold, and what he wants it to do. You can use Access to create anything from a simple hours-tracking system to a personnel management system to a job tracking and forecasting system. Find out what exactly what you're going to need to implement before you even THINK about firing up MS Access.

First off getting a detailed point by point is a no go. Trust me when I say Ive tried lol. He is too scatter brained for that and has already demanded the database be started. So I have part set up that has employee info and inventory info and things to go with that which are working so far. He gets into this mind set where is like randomly "add this" and doesnt have a full idea of how he wants it to run. (which i know is not the ideal way of getting started and has made things more complicated for me but thats the way it is) Anyway I am getting off track.

So I think linking would be a no go as well. You confirmed what I thought would happen if I did that from what I have read. That is too much and i need it together.

I am very very new to VBA but have done some with this database and got it working. (with help from here of course) I like your idea of creating the routine. Now would that be set up in the table itself or would I have to create a form with the vba that saved the data to the table? Does question make sence? As I mention in a different reply I was hoping it would be possible to set up each column in access table to pull data from a cell in excel.

I am going to read up on transferspreadsheet and direct worksheet reading.

 

[pbaldy]

Your Excel format will not lend itself to a straight import using either method. I suspect you'd have to use automation to grab the specific pieces you need. I missed the part about Access, and as Froth mentioned there is a free runtime version. Your best option would be to develop some sort of input screen in Access and let the users input directly into your database.

 

[CJ_London]

There are some things you can do to tidy up the spreadsheet and some data is not required to be imported because it is a calculation (so can be calculated in Access just as easily)

Suggest you need name (cell B1), date (cells A6, A11...) combined with in/out times. one or both the description columns and the rate code.

So you could have a second hidden worksheet which tidies this up into a table and import this instead.

The worksheet would look something for the Monday

 [COLOR=red]    A             B              C           D                E     
[/COLOR][COLOR=red] 1[/COLOR] EName      TimeIN        TimeOut            Description     Code
 [COLOR=red]2[/COLOR] =Sht1!B1  =Sht1!A6+Sht1!B4  =Sht1!A6+Sht1!C4  =Sht1!I4      =Sht1!I4 
[COLOR=red] 3[/COLOR] =Sht1!B1  =Sht1!A6+Sht1!B5  =Sht1!A6+Sht1!C4  =Sht1!I5      =Sht1!I5
  
[COLOR=red] 4[/COLOR]=Sht1!B1  =Sht1!A6+Sht1!B6  =Sht1!A6+Sht1!C4  =Sht1!I6      =Sht1!I6 
[COLOR=red] 5[/COLOR]=Sht1!B1  =Sht1!A6+Sht1!B7  =Sht1!A6+Sht1!C4  =Sht1!I7      =Sht1!I7

You will have potentially blank rows but if you have linked to this table you can avoid importing them by excluding them in the criteria of your insert query which copies the data into your access table.

 

[Frothingslosh]

Yeah, you will definitely need to either use CJ_London's suggestion and create a summary worksheet formatted in a way more legible to TransferSpreadsheet or else learn to use Excel Automation (basically, direct manipulation of Excel via Access VBA). TransferSpreadsheet is a MUCH faster way to import data, however.

 

[Breanna]

CJ_London,

So are you saying create an additional worksheet in the excel file that is formatted like a table and then import that one? So they would fill out the main timesheet, then it would autopopulate the info into the hidden sheet which is set up like a table. Then in access I would just import that hidden sheet instead of the main timesheet?

Also if i have a table and a query I can set the query to do calcutaions and it will put that data in the table? can I not just add a formula to the table itself? This I didn't even think of. I was just going to let excel do it lol.

 

[Frothingslosh]

This, by the way, is a serious case of nostalgia for me. The very first Access application I wrote, back in 1995 with Access 2, started out as what the OP is trying to do!

If only it had stayed so straightforward....

 

[Frothingslosh]

Also if i have a table and a query I can set the query to do calcutaions and it will put that data in the table? can I not just add a formula to the table itself? This I didn't even think of. I was just going to let excel do it lol.

As a rule, you do not store calculated data in tables. You simply calculate it when necessary (for forms, reports, and exports, typically). That way you save space and cut down on complexity.

For hours, for example, you would save the date, time in, and time out, but there's no need to save the total time worked each day or week - those can be calculated as needed.

 

[Breanna]

This, by the way, is a serious case of nostalgia for me. The very first Access application I wrote, back in 1995 with Access 2, started out as what the OP is trying to do!

If only it had stayed so straightforward....

Well maybe if I get super fancy then in 20 years I will be able to help some poor newbe like you are doing for me. And Ill say this is what my first project started out as!

 

[Breanna]

As a rule, you do not store calculated data in tables. You simply calculate it when necessary (for forms, reports, and exports, typically). That way you save space and cut down on complexity.

For hours, for example, you would save the date, time in, and time out, but there's no need to save the total time worked each day or week - those can be calculated as needed.

So leave the table itself simple and use a query or report later to calculate what is needed. That makes sense. In a query I thought you can only have columns that are already in a table.

If I have a table with date, time in, and time out can I have a query that also has total time after that? or would I have to have a column on the table for that but it would be blank?

 

[Frothingslosh]

Well maybe if I get super fancy then in 20 years I will be able to help some poor newbe like you are doing for me. And Ill say this is what my first project started out as!

I'll keep my fingers crossed that it doesn't turn into the mess mine did. In the end, they wanted it doing project forecasting and tracking with an accuracy that ran me straight into the Pentium floating-point error while doubling as an HR pesonnel and project maintenance system. The overall project tracking report was so convoluted that it literally could not be displayed in the query builder without breaking the query, and it took about two hours to run. Bloody thing had to be printed on side-by-side landscape-orientation 11x17 papers.

That was when I learned the horror of 'but how hard can it be to change?'.

 

[CJ_London]

So are you saying create an additional worksheet in the excel file that is formatted like a table and then import that one? So they would fill out the main timesheet, then it would autopopulate the info into the hidden sheet which is set up like a table. Then in access I would just import that hidden sheet instead of the main timesheet?

Yes - clearly the sheet they fill in must be locked down so they can't add or delete rows or columns and you need something to ensure they enter valid data - e.g. they can't enter 25 O'clock or have an out time earlier than the in time (in 08:00 out 01:00 - needs to be 13:00), but this is easily achievable in Excel

I

can set the query to do calcutaions and it will put that data in the table?

You can, but I would not store the calculated time for example - you calculate that as and when you need it. But you might have a function on the user name to find the user ID and store that instead of the name

 

[Breanna]

I am going to do it that way with the hidden spreadsheet in excel in a table format.

Once I make that part of excel how do I set up a table to import it into. (I know how to import it so it creates a new table but not how to do it into an existing one)

I am using an employee number that will be used as the relationship. I just havent added that to the excel yet because when that was first created it wasn't necessary.

 

[Frothingslosh]

So leave the table itself simple and use a query or report later to calculate what is needed. That makes sense. In a query I thought you can only have columns that are already in a table.

If I have a table with date, time in, and time out can I have a query that also has total time after that? or would I have to have a column on the table for that but it would be blank?

I assume you're using the query builder, so correct me if you're working directly with SQL.

In the query builder, you have a couple things you can do. For starters, you can create fields that are the result of either an in-line calculation or a function. For example, to figure out hours worked in-line:

TimeWorked: (DateDiff("n",[TimeIn],[TimeOut]))/60

Or you could write a function and call it thusly:

TimeWorked: HoursWorked([TimeIn],[TimeOut])

using this function:

Public Function Worked(ByVal TimeIn As Date, ByVal TimeOut As Date) As Single
 
    Worked = (DateDiff("n", TimeIn, TimeOut)) / 60
 
End Function

(In this case, there's not much point in setting it up as a function, but with more complicated calculations it's something to keep in mind.)

Either way would give you the hours worked for that line.
You could then write a query based on THAT query, use the 'sorting and grouping' options and select the following:

Employee - Group By
Date - Group By
TimeWorked - Sum

That would end up returning a list of each employee and the hours worked for each day. You could extend upon or limit that list as needed.

So, clear as mud so far?

 

[CJ_London]

can I have a query that also has total time after that

yes - something like

 SELECT EName, format(InTime,"mm/dd/yyyy") AS WorkDate, Format(inTime,"hh:mm") as Start, Format(OutTime,"hh:mm") AS Finish, Format(outTime-inTime,"hh:mm") as TotalTime
 FROM myTable

 

[Breanna]

Frothingslosh,
Yeah that does make sense. I will have to get the table set up first then start on that.

 

[Frothingslosh]

Frothingslosh,
Yeah that does make sense. I will have to get the table set up first then start on that.

CJ_London's version may well be a better way to go, too. Depends on how your system is set up!