?
Solved

Merge Excel Spreadsheet with Access database

Posted on 2003-02-25
7
Medium Priority
?
2,248 Views
Last Modified: 2008-01-16
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
Comment
Question by:jagermeister3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Expert Comment

by:Dreamboat
ID: 8020073
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
 

Author Comment

by:jagermeister3
ID: 8020241
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
 
LVL 22

Expert Comment

by:Dreamboat
ID: 8020342
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:jagermeister3
ID: 8020584
would it help if I coud send you my excel file that has the time sheet on it?
0
 

Expert Comment

by:gakibbie
ID: 8021555
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
 
LVL 22

Accepted Solution

by:
Dreamboat earned 200 total points
ID: 8021751
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
 

Expert Comment

by:drubarth
ID: 8022541
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question