Large Query

[bhipwell via AccessMonster.com]

Hello,

I just completed normalizing my database after getting my head around the
information that needed to be inputted. Now I am creating a number of
reporting options but I think I am reaching the limit of the query size and
am looking for alternative solutions.

I am in the employee benefits field and basically I have the following types
of tables:

CompanyInfo
EmployeeInfo

HealthInfo
DentalInfo
LifeInfo
VisionInfo
(And so on for about 12 types of benefits)

I now also have all these nice little queries with 4 or 5 fields that are
calculating the premium for each line of coverage per employee. Everything
is great so far.

However, now I want to create a report that provides a global overview of the
benefit cost. To do this, I need to pull and sum the information stored in
my neat little queries. Problem is, I have about a dozen of these little
queries to pull from. With some simple math problems to get some results I
am looking for, my query is ending up around 100 fields and locking up Access.


Any ideas on how to develop a more efficient report?

B

 

[Guest]

Do you actually have different tables for each type of benefit? If so, aren't
the structures of each quite similar? If I understand correctly that you want
to report across multiple tables, I expect you need to use a union query.