Counting, summing, matching, summing again...

[Guest]

I build reports that show data such as how many people from a census table
are covered by which HMOs in a particular state. In one report I show the
percentage covered by 1 carrier, percentage covered by 2 carriers and so
on... I have another that I show which HMOs will cover a census population in
each state. I do this all with querries in access by grouping different sets
and counting the zips that match between the census and the carrier tables. I
also match this with the USPS postal file which is about 50,000 records.
Right now the data is in SQL and the qureries and reports are in Access.

The problem is speed and complexity. I learned to do this all with queries
but there are many work arounds built in to enhance speed. Sometimes there
are several make tables or append queries to help speed up a crosstab or
such. This all becomes a huge mess trying to trace the multiple tables and
quries feeding into each other I want to do this with code with ado... What
would be the best approach to dealing with this? I need simplicity and speed.
What do you think? I've got a couple of hours to find a solution...

Thanks!

 

[Ron Weiner]

IMHO

Create Views, Stored Procs, and UDF's on your Sql server to handle ALL of
the data manipulation. Then use Access ADO, DAO, PassThru queries, or
whatever combination thereof you deem necessary to deliver data to your
reports.

This way you will never be passing huge amounts of data down the wire to
Access for further processing. The only thing Access will be responsible
for is formatting the data in a way that makes the most sense for your user
community.

OBTW this will require more than a few spare hours if your requirements are
as complex as you let on. BUT If you want to taste the flavor of LIFE, Take
BIG Bytes. Moderation is for monks.

Ron W