Thread: Data from Access to Excel

Hi,

I have a database with 45000+ lines and increasing every month. I need
to create summary report ( one page or two ) based on this database.

I have tried using sumproduct with multiple criteria to extract the
information I require directly within the report/template. This works
fine, however I am finding 3 big disadvantages with this method.

1. The database is currently 45000+ lines and will soon cross the excel
limit of 64k lines.
2a. By using Sumproduct with multiple criteria ( upto 6 criteria's
before a summary total is derived), the calculation performed by excel
slows down the whole process drastically.
2b. I have some drop down in my report ( to select by country or
segment etc..), the recalculation takes around 30 sec to a minute at
times when changed.
3. With the database in the excel file, the file is extremely bulkly to
send it to a number of people.
4. I have 2 different databases which need to be looked into for the
summary one or two page report.

I am thinking of having the database extract into Access and extracting
the same into Excel.

1. Is there a process by which I can connect to Access database via
excel and get the summarised info in excel similar to sumproduct in the
cell within the output template/report.

2. Once the Access database is updated, I would need to refresh the
excel file to get the updated report.

3. I am open to any other suggestions on how to work with 2 or 3 huge
databases and quickly extract summary information in the way I want and
the final output file would not be bulky for circulation.

Using Pivot table is not an option since it has certain limitations.

Regards
Sandip.

Store your data in Access, do your queries in Access and use either MS Query
to bring the data into Excel or VBA if you want to send out the information
to your clients along with the report.

MH

"Sandip" <sanshah01@yahoo.com> wrote in message
news:1148909380.096746.192930@j73g2000cwa.googlegroups.com...
> Hi,
>
> I have a database with 45000+ lines and increasing every month. I need
> to create summary report ( one page or two ) based on this database.
>
> I have tried using sumproduct with multiple criteria to extract the
> information I require directly within the report/template. This works
> fine, however I am finding 3 big disadvantages with this method.
>
> 1. The database is currently 45000+ lines and will soon cross the excel
> limit of 64k lines.
> 2a. By using Sumproduct with multiple criteria ( upto 6 criteria's
> before a summary total is derived), the calculation performed by excel
> slows down the whole process drastically.
> 2b. I have some drop down in my report ( to select by country or
> segment etc..), the recalculation takes around 30 sec to a minute at
> times when changed.
> 3. With the database in the excel file, the file is extremely bulkly to
> send it to a number of people.
> 4. I have 2 different databases which need to be looked into for the
> summary one or two page report.
>
> I am thinking of having the database extract into Access and extracting
> the same into Excel.
>
> 1. Is there a process by which I can connect to Access database via
> excel and get the summarised info in excel similar to sumproduct in the
> cell within the output template/report.
>
> 2. Once the Access database is updated, I would need to refresh the
> excel file to get the updated report.
>
> 3. I am open to any other suggestions on how to work with 2 or 3 huge
> databases and quickly extract summary information in the way I want and
> the final output file would not be bulky for circulation.
>
> Using Pivot table is not an option since it has certain limitations.
>
> Regards
> Sandip.
>

Hi,

This answers one question that it is better to use Access as a
database.

However is there any syntax which can be placed within various cells in
the report which can access the access database and provide the summary
information in the report/template. ( Please note that the extract
required is based on multiple criteria)

If not, is Microsoft query the only solution. I have no idea on how to
use VBA as mentioned above.

I am not versed with excel/access extraction process and hence a reply
in a little bit detailed way would be very much appreciated.

Regards
Sandip.

Also look at TransferSpreadsheet function from Access

http://office.microsoft.com/en-us/as...866541033.aspx

Use the query you created as the source.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Sandip" wrote:
| Hi,
|
| This answers one question that it is better to use Access as a
| database.
|
| However is there any syntax which can be placed within various cells in
| the report which can access the access database and provide the summary
| information in the report/template. ( Please note that the extract
| required is based on multiple criteria)
|
| If not, is Microsoft query the only solution. I have no idea on how to
| use VBA as mentioned above.
|
| I am not versed with excel/access extraction process and hence a reply
| in a little bit detailed way would be very much appreciated.
|
| Regards
| Sandip.
|

Hi,

I read through the link mentioned and its asked for using the Macro or
VB in access.

I am looking for some way of accessing 3 huge databases from access to
create summary reports in excel using sumproduct like function in
excel.

I have tried using sumproduct by having the databases in excel and it
works fine, however, the calculation sometimes takes an hour and the
file become too bulky to email.

Hence if i can keep the databases in access and derive the same output
in excel (using sumproduct like function) by connecting to access, the
whole process will be much quicker and simpler.

If possible, is there any tutorial or help available to that I can read
through and try is out.

Regards
Sandip.



Dave Patrick wrote:
> Also look at TransferSpreadsheet function from Access
>
> http://office.microsoft.com/en-us/as...866541033.aspx
>
> Use the query you created as the source.
>
> --
>
> Regards,
>
> Dave Patrick ....Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>
> "Sandip" wrote:
> | Hi,
> |
> | This answers one question that it is better to use Access as a
> | database.
> |
> | However is there any syntax which can be placed within various cells in
> | the report which can access the access database and provide the summary
> | information in the report/template. ( Please note that the extract
> | required is based on multiple criteria)
> |
> | If not, is Microsoft query the only solution. I have no idea on how to
> | use VBA as mentioned above.
> |
> | I am not versed with excel/access extraction process and hence a reply
> | in a little bit detailed way would be very much appreciated.
> |
> | Regards
> | Sandip.
> |

Yes, easily done in Access. These may help.

http://office.microsoft.com/en-us/as...880271033.aspx
http://www.databasejournal.com/featu...0895_3101941_1

You might also poke around here as well.

http://www.microsoft.com/communities...&lang=en&cr=US

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Sandip" wrote:
| Hi,
|
| I read through the link mentioned and its asked for using the Macro or
| VB in access.
|
| I am looking for some way of accessing 3 huge databases from access to
| create summary reports in excel using sumproduct like function in
| excel.
|
| I have tried using sumproduct by having the databases in excel and it
| works fine, however, the calculation sometimes takes an hour and the
| file become too bulky to email.
|
| Hence if i can keep the databases in access and derive the same output
| in excel (using sumproduct like function) by connecting to access, the
| whole process will be much quicker and simpler.
|
| If possible, is there any tutorial or help available to that I can read
| through and try is out.
|
| Regards
| Sandip.