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.