Basic Query Structure

  • Thread starter bhipwell via AccessMonster.com
  • Start date
B

bhipwell via AccessMonster.com

Hello,

Perhaps I have been looking at this too long, but I need some help. I have
been using IIF statements extensively. So much so I am now realizing the
ineffeciency in this approach. I need some access 101 to help me get back on
track. Here is the information...

TblEmployee listing employees
EmployeeID
LastName
FirstName

TblCompany listing the companies
CompanyID
CompanyName

TblHealthRates
HealthID
EmployeeOnlyRate
EmployeeSpouseRate
EmployeeChildRate
EmployeeFamilyRate

TblPercentage
PercentageID
Class1Percentage
Class2Percentage
Class3Percentage

What I need is a query or table that will say "if EmployeeA, who is a Class3
employee, working for Company1 chooses EmployeeSpouse, what is the rate?"
Basically, it should return the data in EmployeeSpouseRate and
Class3Percentage where I then can do some simple math to give me the cost to
the employee.

Thanks in advance!

Thanks!
 
J

John Spencer

Is the employee information on Employee class (Class 3) and choice of
insurance type (EmployeeSpouse) stored in the employee table? Or is it
stored elsewhere?

The first thing I see is a data structure problem.

TblHealthRates and TblPercentage both have repeating fields. If they
can be restructured then you should be able to get the data using simple
joins.

tblHealthRates
HealthID
RateType (EmployeeOnly, EmployeeSpouse, etc)
Rate (Information you are storing in the rate fields)

tblPercentage
PercentageID
ClassType
ClassPercentage

So where you now have one record in tblHealthRates you would end up with
4 records. And similar for tblPercentage. Building the tables in this
manner makes life simple if someone introduces another health rate type
(EmployeeSpouseOneChild) or another Percentage Class.

I suspect that you have other problems in the data structure.

OK, now to your problem. If you cannot fix the data structure, you
might be able to get rid of the multiple IIF statements by using a
normalizing union queries against the repeating fields.

For instance (qFixHealthRates),

SELECT "EmployeeOnlyRate" as RateType , EmployeeOnly as Rate
FROM tblHealthRates
UNION ALL
SELECT "EmployeeSpouseRate" as RateType , EmployeeSpouseRate as Rate
FROM tblHealthRates
UNION ALL
SELECT "EmployeeChildRate" as RateType , EmployeeChildRate as Rate
FROM tblHealthRates
UNION ALL
SELECT "EmployeeFamilyRate" as RateType , EmployeeFamilyRate as Rate
FROM tblHealthRates

And a similar query for tblPercentage (qFixPercentages)

Then your query would be something like the following

SELECT C.CompanyName,
E.EmployeeID, E.LastName, E.FirstName
, P.Percentage, H.Rate
, P.Percentage * H.Rate as EmployeePays
FROM ((tblEmployee as E INNER JOIN tblCompany as C
ON E.CompanyID = C.CompanyID)
INNER JOIN qFixPercentage as P
ON E.ClassID = P.PercentageType)
Inner Join qFixHealthRates as H
ON E.HealthInsuranceType = H.RateType
WHERE E.EmployeeId = 9999
AND C.CompanyID = 1234


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

bhipwell via AccessMonster.com

Thanks John,

It was the TblHealthRates and TblPercentages I was struggling with. With the
table structure, I then could make a table:

TblEmployeeHealthRate

Whereby I would have the following fields

TblEmployeeHealthRatesID
TblEmployeeID
TblHealthRateID

I would do the same for TblEmployees and TblPercentage. Now, to get to the
final question (what is the health rate and percentage for the employee), how
would I do that?

Would I create the TblEmployeePercentage and then combine that table with
TblEmployeeHealthRates? Or, is it possible to make a table like:

TblEmployeeHealthRatePercentages
TblEmployeeID
TblHealthRateID
TblPercentageID

Thanks again!
 
Top