More Efficient: Multiple DLOOKUPs or Multiple Left Joins?

[JoeM]

I am working with some data from a very old system (that uses Clipper, if anyone can remember back that far), where the data tables aren't exactly normalized.


I am using Access to combine and export the data to Excel. I am trying to figure out the most efficient way of doing it. Here is what I have:

Table1: Plan_Listing
Fields: Employer_ID, PlanID_1, PlanID_2, ... ,PlanID_20
So there is just one record in this table with 20 PlanID fields.

Table2: Employee_Listing
Fields: Employer_ID, Employee_ID
This table has one record per employee.

Table3: Employee_Benefits
Fields: Employer_ID, Employee_ID, PlanID, Plan_Cost
This table has a record for each employee/plan combination.

And here is what my resulting query needs to look like:
Employee ID, PlanID_1_Cost, PlanID_2_Cost, ..., PlanID_20_Cost
So this query would have one record per employee, with fields for all 20 Plan Cost values.

So, there I two ways I can go about this:

1. Create a query joining Table1 and Table2 and doing 20 DLOOKUP calculations per employee to return the Cost values.

2. Create a query that first joins Table1 with Table2, and then do 20 more Left Joins to 20 copies of Table3 to return the Cost value for each plan type.

Doing either of these is not a problem, I know how to do them both. I was just wondering if one is better or more efficient than the other before I go through this whole exercise.
Which is better, to do 20 DLOOKUPs in a query, or create 20 LEFT JOINs to 20 copies of the same table?

Thanks

[rpeare]

Personally I do not use domain functions (Dsum, dcount, dlookup) if I can possibly avoid it. I was working on an example of something earlier today and putting in a d function on a report took my report running from about 2 seconds to about a minute and a half on a very small database and dataset, not typical results but still, domain functions are incredibly inefficient.

You have another option that you didn't mention

You can create a union query of your plans then link the union query to the plan employee_benefits table then make a crosstab query to take it back to the format you want

You'd have to have something like

SELECT EMPLOYERID, PLANID_1 FROM Plan_Listing
UNION ALL
SELECT EMPLOYERID, PLANID_2 FROM Plan_Listing
UNION ALL
SELECT EMPLOYERID, PLANID_1 FROM Plan_Listing
UNION ALL
etc...

once you look at that query you'll see how easy it is to link to your employee benefits then create a crosstab to finish it off

[JoeM]

Thanks for suggestion. Maybe I am wrong, but I thought that with a Cross-Tab query, you cannot control the order of the fields that it places the details into. The 20 Plan ID fields need to be in a specific order.

I was talking with another person in the office, and am trying another option. Writing the results to a pre-defined table, blending Action Queries, VBA, and Recordsets. Here is the approach I am going to try.

1. I first created the shell of the table that has Employer ID, Employee ID, and the 20 Cost fields for the 20 Plan IDs.
2. Run an Append Query to write all the Employer IDs and Employee IDs to the table.
3. Use a Function in VBA that opens my Plan Listing table, loops through all 20 fields to build dynamic SQL code to create an Update Query to update the Cost field, run the Update Query, and continue on to next Plan ID.

This will all be part of a procedure that will first delete out the previous values in my built table and runs steps 2 and 3 above each time.

I'll let you know how it works out!

[rpeare]

You can control them but it requires some effort for instance

SELECT EMPLOYERID, PLANID_1, PLANID_1_COST AS ColumnText FROM Plan_Listing
UNION ALL
SELECT EMPLOYERID, PLANID_2, PLANID_2_COST AS ColumnText FROM Plan_Listing
UNION ALL
SELECT EMPLOYERID, PLANID_1, PLANID_3_COST AS ColumnText FROM Plan_Listing
UNION ALL

You're basically building your column labels in the union query

[Dal Jeanis]

So, what you are trying to do is, for each employee planid record, figure out which of the 20 employer planids it matches, and get the data into that column? How about a query like this:

SELECT
   TEB.Employer_ID, 
   TEB.Employee_ID, 
   SUM(IIF (TEB.PlanID = TPL.PlanID_1,TEB.Plan_Cost,0)) AS PlanID_1_Cost,
   SUM(IIF (TEB.PlanID = TPL.PlanID_2,TEB.Plan_Cost,0)) AS PlanID_2_Cost,
   SUM(IIF (TEB.PlanID = TPL.PlanID_3,TEB.Plan_Cost,0)) AS PlanID_3_Cost,
   etc for all 20
FROM Plan_Listing AS TPL INNER JOIN Employee_Benefits AS TEB
ON TPL.Employer_ID = TEB.Employer_ID
GROUP BY TEB.Employer_ID, TEB.Employee_ID

Note that I didn't see anything in Table 2 that had value, because the key of table 1 was already in table 3. If the Employer_ID is not on Table 3, then the bind would need to include another INNER JOIN to get the Employer_ID for each Employee_ID.

[JoeM]

Dal,

Eureka! I cannot believe I did not think of that! I used that method a few times a few years ago, but didn't think of it here. Thank you!

I actually got the method I proposed in post #3 to work, but I actually like the suggestion you proposed better. It is dynamic, efficient, quick, and does not depend on any Action Queries being run and storing the results to a new table.

Thanks for all the help and suggestions!

[Dal Jeanis]

You're welcome. I actually started out writing that query without the SUMs, then having a second query to sum them, and eventually I realized it could be coded as a single query.