Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Implementing Excel with Access
- Thread starter Petemush
- Start date
This ultimately depends on what you are planning to do....your question is very vague.
If you just want to take some queries and move them to excel....just do an export/save as. If you want to link the excel spreadsheet to a query in Access, I believe excel has a wizard for creating the connection. If you want to do individuals cell manipulation, etc, then VBA code with connections to the excel spreadsheets will work best....
As you can tell, there is a lot that could be done. Whatchya looking for? Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)
Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com
If you just want to take some queries and move them to excel....just do an export/save as. If you want to link the excel spreadsheet to a query in Access, I believe excel has a wizard for creating the connection. If you want to do individuals cell manipulation, etc, then VBA code with connections to the excel spreadsheets will work best....
As you can tell, there is a lot that could be done. Whatchya looking for? Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)
Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com
- Thread starter
- #3
Right, sorry it took so long to reply but I was trying to get out of my boss exactly what she wanted.
At the moment, I have a database holding details of employees, training courses, and how the two relate to each other.
I have a crosstab query which creates a table with employee's surnames across the top, training courses down the side and if they've been on a course, it has the date they went in the relevant cell to indicate they've completed that particular course.
Some things to point out:
The user picks from a drop down list the employees that should appear in the table.
The courses that are shown are only those that are related to at least one employee picked.
An employee can be related to a course in four ways:
Completed
Booked On
Needed
Did Not Attend
So for any employee picked to be in the table, all the courses related to the employee in any way will be included in the table.
Once the crosstab query has been done, it's exported to excel with the following code:
DoCmd.OpenQuery "qrycrstabEmployeesCourses"
DoCmd.OutputTo acOutputQuery, "qrycrstabEmployeesCourses", acFormatXLS, _
"J:\Leo\EmployeeDetails.xls", -1
This all works, and I can even have similar spreadsheets that show courses needed or courses booked on etc etc.
What I would like to do is combine all these so you get an uber-spreadsheet which shows if a course is completed or if an employee is booked on it or if an employee needs it.
I'm pretty sure I can't do much more with the crosstab query so was wondering if anyone had any idea about how to go about this?
I'd be very grateful!
Cheers,
Pete
PS Sorry about the long winded explanation.
At the moment, I have a database holding details of employees, training courses, and how the two relate to each other.
I have a crosstab query which creates a table with employee's surnames across the top, training courses down the side and if they've been on a course, it has the date they went in the relevant cell to indicate they've completed that particular course.
Some things to point out:
The user picks from a drop down list the employees that should appear in the table.
The courses that are shown are only those that are related to at least one employee picked.
An employee can be related to a course in four ways:
Completed
Booked On
Needed
Did Not Attend
So for any employee picked to be in the table, all the courses related to the employee in any way will be included in the table.
Once the crosstab query has been done, it's exported to excel with the following code:
DoCmd.OpenQuery "qrycrstabEmployeesCourses"
DoCmd.OutputTo acOutputQuery, "qrycrstabEmployeesCourses", acFormatXLS, _
"J:\Leo\EmployeeDetails.xls", -1
This all works, and I can even have similar spreadsheets that show courses needed or courses booked on etc etc.
What I would like to do is combine all these so you get an uber-spreadsheet which shows if a course is completed or if an employee is booked on it or if an employee needs it.
I'm pretty sure I can't do much more with the crosstab query so was wondering if anyone had any idea about how to go about this?
I'd be very grateful!
Cheers,
Pete
PS Sorry about the long winded explanation.
- Status
Similar threads
- Locked
- Question
- Locked
- Question