Re: Data from Access database needed in Excel spread***...how to do it?

From: Duncan Edment (duncan_at_edment.NO_SPAM.freeserve.co.uk)
Date: 01/10/05 

Date: Mon, 10 Jan 2005 00:01:13 -0000

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

"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
news:56gtt0tdf6kga835591ea6r6040ctn83bl@4ax.com...
> 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 work***
> 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
>
>
>
>
>
> On Thu, 6 Jan 2005 22:05:39 -0000, "Duncan Edment"
> <duncan@edment.NO_SPAM.freeserve.co.uk> wrote:
>
>>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 *** 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 spread***, and place it in the relevant cells. Or, for the
>>Excel spread*** 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" <j.mapSoN.nurick@dial.pipex.com> wrote in message
>>news:mjkpt09561q9g58oh57nfmabl51ujes469@4ax.com...
>>> 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.
>>>
>>>
>>>
>>> On Wed, 5 Jan 2005 22:37:57 -0000, "Duncan Edment"
>>> <duncan@edment.NO_SPAM.freeserve.co.uk> wrote:
>>>
>>>>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
>>>>spread*** so that the accountants can subsequently bill customers.
>>>>The
>>>>information that is transferred to the spread***, is the total
>>>>number of
>>>>hours worked on each project, on a week by week basis.
>>>>
>>>>The spread*** 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 spread***, 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 spread***--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
>>>
>>> --
>>> John Nurick [Microsoft Access MVP]
>>>
>>> Please respond in the newgroup and not by email.
>>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.