AccessXP Query Expression Question

I’m created a fairly simple database to replace an Excel spreadsheet that was being used to calculate company health insurance information. There are two tables. The first table contains the employee name field, and three “Yes/No” fields. These Yes/No fields are for the three different types of insurance, Disability, AD&D, and FAM AD&D. The second table contains the rates for the three types of insurance. In this table there are two fields, [Type] and [Rate]. Example:

TableOne
[Name] [Disability] [AD&D] [FAM AD&D]
John Doe Yes No No
Jane Doe No Yes Yes

TableTwo
[Type] [Rate]
Disability 1.50
AD&D 1.25
FAM AD&D 1.75

I want a query that will give me the person’s name and the rate they are paying for each type of insurance only if the Yes/No value is Yes from TableOne. The rates themselves will change, so I want to be able to change the rates in TableTwo, and have those changes reflected in the query. Here is how I want the query results to look:

Query
[Name] [Disability] [AD&D] [FAM AD&D]
John Doe 1.50 - -
Jane Doe - 1.25 1.75

What would the expression be that I would use in the query to return these results?

SELECT a.[Name], b.Type, b.[Rate]
FROM one as a, two as b
WHERE (a.Disability = True AND b.Type = 'Disability')
OR (a.[AD&D] = True AND b.Type = 'AD&D')
OR (a.[FAM AD&D] = True AND b.Type = 'FAM AD&D')

should give you (csv)

[Name] [Type] [Rate]
John Doe, Disability, 1.50
Jane Doe, AD&D, 1.25
Jane Doe, FAM AD&D, 1.75

Next,

SELECT a.[Name], b.Type, '-' AS Rate
FROM one as a, two as b
WHERE (a.Disability = False AND b.Type = 'Disability')
OR (a.[AD&D] = False AND b.Type = 'AD&D')
OR (a.[FAM AD&D] = False AND b.Type = 'FAM AD&D')

should give you (csv)

[Name] [Type] [Rate]
John Doe, AD&D, -
John Doe, FAM AD&D, -
Jane Doe, Disability, -

use UNION to put these two tables together:

SELECT a.[Name], b.Type, b.[Rate]
FROM TableOne as a, TableTwo as b
WHERE (a.Disability = True AND b.Type = 'Disability')
OR (a.[AD&D] = True AND b.Type = 'AD&D')
OR (a.[FAM AD&D] = True AND b.Type = 'FAM AD&D')
UNION
SELECT a.[Name], b.Type, '-' AS Rate
FROM TableOne as a, TableTwo as b
WHERE (a.Disability = False AND b.Type = 'Disability')
OR (a.[AD&D] = False AND b.Type = 'AD&D')
OR (a.[FAM AD&D] = False AND b.Type = 'FAM AD&D')

Below, we refer to this UNIONed table as "[intermediateTable]".

Now, to get it into the final format,

SELECT a.[Name], a.[Rate] as [Disability], b.[Rate] as [AD&D], c.[Rate] as [FAM AD&D]
FROM
(SELECT [Name], [Rate] FROM [intermediateTable] WHERE [Type] = 'Disability') AS a
,(SELECT [Name], [Rate] FROM [intermediateTable] WHERE [Type] = 'AD&D') AS b
,(SELECT [Name], [Rate] FROM [intermediateTable] WHERE [Type] = 'FAM AD&D') AS c
WHERE a.[Name] = b.[Name] AND b.[Name] = c.[Name]

That should get you

[Name] [Disability] [AD&D] [FAM AD&D]
John Doe 1.50 - -
Jane Doe - 1.25 1.75

I know this is kinda complicated, but I can't see another way to get from what you've given me to what you want.

For the record, what you've got there is not a relational database- which is why it is so hard.

A more correct way to organize the data is:
(* means primary key, (fk) means foriegn key)

Employees: empID*, empName
InsuranceTypes: Type*, price
employee_Insurance: {empID (fk), Type (fk)}*

I can create any new tables that I would need. How would you recommend that I setup a relational database in order to make this easier? Here is a screen shot of what I am trying to get. The last table in here is how I want the Query to look. I hope this helps.

I already outlined the tables I think you should use in my last post. There are 3 tables: Employee, Insurance, and EmployeeInsurance (which handles the many-many relationship between the two). A * indicates the primary key of the table, a (fk) means that column is a foriegn key. In EmployeeInsurance, you have a composite primary key composed of both columns.

This is better design and lets you do a lot more with less difficulty. However in order to 'flip' the orientation of the table as you want to, you still need to do a tricky query (as outlined below).

Of course it would be easier if you could use Case statements and the like (conditional logic in the select statement), that would make it considerably easier. However as far as I know that's not possible in Access.