Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2261
  • Last Modified:

Merge Excel Spreadsheet with Access database

I'm helping a co-worker redesign their timesheet.  I have an excel spreadsheet set up to use as an employee timesheet for 100+ employees.  There are 4 cells that I would need to change for employee name, employee id, department, and pay period.  I also have an access database with information that I imported from excel.  How do I merge the excel timesheet with the access database so I could print each of the 100+ employee's timesheets with out having to manually enter their 4 cells of information?  

Many thanks in advance.
0
jagermeister3
Asked:
jagermeister3
1 Solution
 
DreamboatCommented:
If I'm getting you right, the problem is that you've got many records for employee 1, employee 2, and so on. But you don't know how to get Access to collect the other 4 pieces, correct?

Where do these 4 pieces exist in the time sheets, if they exist?

I suspect you're going to need some Access VBA code to accomplish this, and you may want to go to community support at:
http://www.experts-exchange.com/Community_Support/

and ask them to delete your question here, then repost your question here:
http://www.experts-exchange.com/Databases/MS_Access/

Good luck!
0
 
jagermeister3Author Commented:
You're close. And I'm not sure if I'll need to repost the question but let me explain it and you can tell me if I need to repost.

I have a file designed in excel with multiple columns and rows so employees can manuall fill it out in pen or pencil.  I have 4 cells that I would like to replace, "Employee Name" "Employee ID" "Department" and "Pay Period".

I'd like be able to merge or change out the cells with the table in Access.  Say "Joe Smith" "04532" "Sales" "04/20/03-04/27/03" there are 100+ employees with this information.

Once this is merged accounting could print out the timesheets after running a query? or merging the cells?

Thanks
0
 
DreamboatCommented:
OH!
I see. I don't know how I could explain that in writing or even explain it without seeing your DB and Excel file. :(
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jagermeister3Author Commented:
would it help if I coud send you my excel file that has the time sheet on it?
0
 
gakibbieCommented:
If you want to go from Excel to access you can save the Excel file as a csv (Comma Seperated Value) file and import it directly into access as a seperate table.  From there you can run an append or update query in Access to update your existing database.

If you are going the other direction you can export a table from Access as a csv but then you would be stuck manually updating Excel unless you do some tricky VBA code with search and replace.

Let me know which way you want to go and I will give you the particulars and any code necessary.

Cheers,
Aaron
0
 
DreamboatCommented:
Actually, she's trying to create timesheet PAPER FORMS for the different employees.

Do me a favor, Jag. If you can...Copy your DB through Windows Explorer. Open the copy and remove most of the real names. Change some of the rest of them so they're not recognizable data. Go to Tools-Compact and Repair...and run that.

Send me this copied DB along with your Excel worksheet.
K?

I can probably get you working strictly in Access.

I assume none of these people are entering their time directly into Excel, and the Excel workbook is used solely for the purpose of pringing...

Anne@TheOfficeExperts.com
0
 
drubarthCommented:
I must not be understanding all your needs, because as I do understand the problem the answer seems quite simple.

If all your data is in Access, why don't you simply create an Access report to print the individual timesheets.

If some of your data is in Excel, you can link to your Excel worksheet directly (you don't need to save it in a CSV format, but your headings do have to be setup on the first row with no spaces) and then create a query to pull your data together.  Then again just use an Access report for printing your timesheets.

Good luck!

dr
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now