Need help building query

[bhipwell via AccessMonster.com]

Hello,

I need input as to how to go about building a query so I can create a report
bringing together many different pieces of information. The database
involves employee benefits.

The database contains insurance data for health, dental, vision, life,
voluntary life, disability and other products. The data includes the
premiums of each product, employer contributions, etc.

I have a query for each product that does a number of calculations for that
product. The one common piece of data each query provides is the amount each
product costs per month.

I want to build a report that lists each product and the cost per month. I
did this buy creating a "query of queries." Therefore, I have about a dozen
queries in one query to give me each products' cost per month.

Since each query has to run on each record, the new BIG query is very, very
slow to run.

Help! Thoughts anyone?

BH

 

[Jeff Boyce]

"How" depends on "what" -- it would help to have a better picture of the
underlying data from which you are trying to pull this. How is your
database structured?

It would also help to see the SQL statement that is your query ... open the
query in design view, switch to SQL view, copy it and paste it into your
next post here.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhipwell via AccessMonster.com]

Hi Jeff,

The SQL would be massive. Here is the picture in a nutshell.

The BIG query contains 16 little queries, one for each product. In addition,
there is a query with employee data and a query with employer data.

The little queries calculate monthly premiums, payroll deductions and
benefits amounts based on the elections choosen by the employee.

What is happening is each the 16 queries (18 counting the employee and
employer queries) runs against each record in the database, slowing the speed
of the query.

I do have a question. Since I am looking for data referring to only one
employee out of thousands, is there a way to have the BIG query first find
the employee and then run the little queries?

BH

 

[Jon M.]

This is just a thought, but couldn't you just identify your employee in the
employee query first by entering whatever criteria you would search that by,
i.e. employee#, social, last name, etc. And then run your other queries
based on that result?

So as part of your employee query put in the criteria line of the field you
want to search [Employee Info], and when you run the big query it will ask
for the [Employee Info] and base the results of the other queries on that
information.

 

[bhipwell via AccessMonster.com]

I will give that a try.

Didn't think of the little employee query identifing the employee first, I
was originally thinking that I would need the big queries SQL to fist find
the employee then run the other queries.

Thanks!

I'll let you know how it turns out.

BH

 

[Jeff Boyce]

I can't tell from your description, but it seems possible that your
underlying data structure is not well-normalized.

"So what?", you ask? Access is a relational database, and its
features/functions expect relationally-organized data.

I'm having trouble visualizing why you are using "16 little queries, one for
each product". Then again, I don't know how your data is structured.

If you care to provide more information, folks here may be able to offer
alternate approaches...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

If you run the BIG query from a form where you select an employee, you could
refer to the control you use to select the employee in each of your little
queries.

SELECT *
FROM HealthBenefits
WHERE EmployeeID = [Forms]![frmSelectEmployee]![txtEmployeeID]

Doing that for each of the sixteen little queries would probably speed things
up considerably.

I suspect that your data structure is not optimal and may need to be
redesigned, but that is only a suspicion.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bhipwell via AccessMonster.com]

Thanks for the reply, I will try your suggestion as well.

Regarding normalization, my database was just recently "normalized." The
many queries results from pretty heavy calculation duties. For example, the
life insurance query does the following for each employee:

- calculates the maximium benefit based on the employee's age
- calculates the monthly premium
- calculates the payroll deduction
- identifies the plan name
- identifies the carrier name

This query is used to provide data in a stand alone report for life insurance.
I have a query like this for each product. Since I want report that combines
the plan name, monthly premium and payroll deductions for each product, I
ended up with a big query with all this little ones.

BH

 

[bhipwell via AccessMonster.com]

Just noticed after a little retooling, the query takes only about 1 second to
run. It is the report that takes forever now to load. Thoughts?

BH

 

[John Spencer]

Thoughts:

--- Make sure the report uses the query as its source.

--- Rebuild the report in a new report based on the query to make sure that
the report is not corrupted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bhipwell via AccessMonster.com]

Hi John,

I have benefited from many of your replys to posts. Thanks!

I started building a brand new report. When I did this, the first thing I
did was set the record source to the BIG query. Now when I run the report,
which is completely blank, it is taking forever. Yet, when I try the query
on a stand alone basis (and outside the report), it only takes a second, tops.


Thoughts?

BH