Data from Access database needed in Excel spreadsheet...how to do it?

[Duncan Edment]

I have the following scenario.

A database is used to record time spent on projects by employees in the
department. This information is then transferred, manually, into an Excel
spreadsheet so that the accountants can subsequently bill customers. The
information that is transferred to the spreadsheet, is the total number of
hours worked on each project, on a week by week basis.

The spreadsheet lists the information as follows:

Project 09/01/05 16/01/05 23/01/05 30/01/05 Total

Alpha 08:50 40:15 25:37 75:25 150:07


NOTE: The dates along the top, are week ending dates, and the number of
hours worked is totalled in the Total column, showing the number of hours
worked in that billing month. Clear so far?

At the moment, what I have to do is run a query entering the Start and
Finish dates--in the case of the 40:15 entry above, these would be 10/01/05
& 16/01/05 respectively--and the query returns the number of total number of
hours worked in that week for each project. This information is then
manually transferred to the spreadsheet, which is quite time consuming and
laborious for the number of projects we are working on.

There must be an easier way of doing this? My questions are as follows:

1. Is this the best / only way of completing this task?
2. Could Access be automated to transfer the data automatically, to
the relevant w/e cell for the specific project, each time a
macro / VBA function is run?
3. Could the Excel spreadsheet--and I know this part is probably OT
for this group--be modified, so that each cell links to a query
in the database--or holds the query itself--and, using the
relevant dates, retrieves the information into the cell?

Hope all this is clear, and that someone can provide me with an answer.

Many thanks for your time and patience.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

To e-mail, please remove "NO_SPAM."

 

[John Nurick]

Hi Duncan,

Probably all you need do is this:

1) Create a Crosstab query that totals the time by week and project as
per your example below. Base it on a parameter query (to collect
criteria such as the dates you want the output to span).

2) Every month (or whatever) export the crosstab to a workbook.

 

[Duncan Edment]

John,

It looks like I've suffered from the dreaded "not specifying the
requirements of the problem correctly" syndrome again!

Whilst I agree with the solution you provide, it wouldn't really help
me.

You see, if I run with your suggestion and export the Crosstab, what I
get are only those projects that were worked on for that specific week.
This then has to be put into a separate workbook and the data manually
transferred to the relevant cells of the "Totals" workbook. From this
"Totals" workbook, the main sheet is updated, showing total number of
hours worked so far YTD, along with a percentage of the time spent on
each project, against the total time worked that week, and the YTD
total.

Again, using this method would involve some manual entry, and thereby,
an element of "user error"--as has happened in the past--customers
billed too much, too little etc.

What I am really looking for--and possibly have extremely high-hopes and
expectations--is a way for the Access database to export the information
to Excel spreadsheet, and place it in the relevant cells. Or, for the
Excel spreadsheet to query the Access database and place the information
in the appropriate cell for the project / week ending. Too much to ask?

Many thanks for your suggestion, and for any further help you can
provide.

Regards

Duncan

 

[John Nurick]

Hi Duncan,

My instinctive response is that if you're only getting one week's worth
of projects in your crosstab, you're crosstabbing the wrong data. As a
rule it's much preferable to manipulate data at the set level with SQL
queries rather than manipulating individual values with VBA.

Start with a Select query that returns a record for each project for
each week since the start of the year. Then crosstab that. Use the
Column Headings property of the query (in SQL view, an IN qualifier in
the PIVOT clause) to force the query to create a column for every week
even if no work has yet been done.

But if you do have to put data into individual cells in a worksheet
there are basically two ways to go. Either write VBA code that
constructs and runs update queries with syntax like this:

UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet1$B4:B4]
SET
F1=55
;

or use Automation to open the workbook in Excel and manipulate it with
VBA code running in Access. See e.g.

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235

John,

It looks like I've suffered from the dreaded "not specifying the
requirements of the problem correctly" syndrome again!

Whilst I agree with the solution you provide, it wouldn't really help
me.

You see, if I run with your suggestion and export the Crosstab, what I
get are only those projects that were worked on for that specific week.
This then has to be put into a separate workbook and the data manually
transferred to the relevant cells of the "Totals" workbook. From this
"Totals" workbook, the main sheet is updated, showing total number of
hours worked so far YTD, along with a percentage of the time spent on
each project, against the total time worked that week, and the YTD
total.

Again, using this method would involve some manual entry, and thereby,
an element of "user error"--as has happened in the past--customers
billed too much, too little etc.

What I am really looking for--and possibly have extremely high-hopes and
expectations--is a way for the Access database to export the information
to Excel spreadsheet, and place it in the relevant cells. Or, for the
Excel spreadsheet to query the Access database and place the information
in the appropriate cell for the project / week ending. Too much to ask?

Many thanks for your suggestion, and for any further help you can
provide.

Regards

Duncan

 

[Duncan Edment]

John,

Many thanks for your reply. I'm busy looking at your links now, and
your comments make sense to me now.

Thanks again

Duncan

Hi Duncan,

My instinctive response is that if you're only getting one week's
worth
of projects in your crosstab, you're crosstabbing the wrong data. As a
rule it's much preferable to manipulate data at the set level with SQL
queries rather than manipulating individual values with VBA.

Start with a Select query that returns a record for each project for
each week since the start of the year. Then crosstab that. Use the
Column Headings property of the query (in SQL view, an IN qualifier in
the PIVOT clause) to force the query to create a column for every week
even if no work has yet been done.

But if you do have to put data into individual cells in a worksheet
there are basically two ways to go. Either write VBA code that
constructs and runs update queries with syntax like this:

UPDATE
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet1$B4:B4]
SET
F1=55
;

or use Automation to open the workbook in Excel and manipulate it with
VBA code running in Access. See e.g.

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235