Same information in multiple fields (1 Viewer)

[Val]

I have 4 Endorsement fields in the primary table because a policy can have up to 4 Endorsements on it. On the Form there are 4 Endorsement fields and the user selects from a drop down box the endorsements that apply. The drop down box for each of the fields is the same. Currently the fields in the table store only the Endorsement number and this is what prints on the report. My problem is now I have to print both the number and the description for each of the fields. Endorsement Description is not a field in the primary table. I created a separate table with the Endorsement numbers and the Descriptions in it but I am at a loss as to how to use it for all 4 fields.

Any suggestions?

TIA
Val

 

[The_Doc_Man]

Well, technically you started on the wrong foot by replicating related data into a single row. This table is not properly normalized. So this design error is leading you to more trouble than you might otherwise have encountered.

You SHOULD have built a policy table with policy number, issuer agent, holder, other personal data. THEN a separate table of endorsements, with a one (policy) to many (endorsement) relationship. Maybe only with policy number and endorsement number for that table.

Then the issue would have been to use the Endorsement table to drive the report, using policy number to link back to get data for the policy table. Or a JOIN between Endorsement and policy with GROUP BY on policy. And a second join between Endorsement and Explanation (or whatever you call it) to define what that endorsement really means.

But you have what you have, so I'll take a shot at how you do it now until you can fix it later.

Your problem as I understand it is to link to the same explanation table four times. OK, open a new query in design view. Add the policy table. Now add the explanation table four (4) times. The second, third, and fourth times, the name will change slightly - like Explanation, Explanation2, Explanation3, etc etc

OK, IN THE QUERY DESIGN VIEW, create a temporary relation by doing a click-drag of the first endorsement field to the first instance of the explanation table's corresponding endorsement number field. Do the same thing for each of the other policy endorsement fields. Each of these is a TEMPORARY relationship. But you can do the one-to-many thing on it.

Note the following problems: If a policy has no endorsements, you are going to have trouble UNLESS you have taken steps ahead of time to cover this case. Perhaps by adding a record for the "blank" endorsement. But this will imply other special-case situations on every form and report that has to deal with the blank endorsement. Note the cases where you have 1, 2, or 3 (but not 4) endorsements. In your scheme, each of those has the same problem on a different scale. Oh, while we are at it, you have another problem - the 5-endorsement case.

IF you retrofit to a separate endorsement list, the problem with blank endorsements doesn't go away entirely - the policy could STILL have no endorsements. But if you planned ahead of time for a zero-endorsements case, that is the only special case you would have to handle.

 

[Val]

Thanks for your reply Doc_Man. I realize that the data is not properly normalized, but I inherited this database after it was in existance for a while and at the time it seemed easier to leave it alone then to start over. (Now I'm not as sure!)

I tried what you suggested and while it does technically work, the query takes way too long to run. I was hoping there was a way to use something similar to the vlookup in Excel. I tried fooling around with the Dlookup function and have been unsuccessful. I think my next option is to forego using the table to translate the descriptions and write a module with a case else statement.

Thanks again for your help.

 

[The_Doc_Man]

"I tried what you suggested and while it does technically work, the query takes way too long to run."

Unfortunately, this is one of the many down sides of having to fake out denormalized tables. The more participants of the JOIN, the longer it takes to assemble the result set. Which is why a retrofit might be in order. It should run at least 2 or 3 times faster because of having fewer tables to be joined.

DLookUp would work but still has to open more recordsets to do it. And that is part of your problem - the number of open (but hidden) recordsets needed to drive the query.