Access – I'd like to collapse a group into results based on what's in the group

I’m working with dosimetry and receiving reports with the following fields:
WearerID BadgeType DeepDose EyeDose ExtremityDose

All wearers have a generic badge, call it BadgeType "Generic". Some wearers use secondary dosimeters just for eyes or just for deep dose that give better results and "override" their generic dosimeter. For example, if their BadgeType is "Eye," it should replace the EyeDose in the Generic badge when I report it to them. Not all wearers have this secondary badge.

I can take my results and group them by WearerID, but how do I collapse the group into reporting BadgeType Eye results for EyeDose when they exist, and Generic results when they don’t?

SELECT * FROM BadgeResults GROUP BY WearerID;

But I’ve no idea how to collapse the group into specific results based on if a BadgeType exists within that group. It’s not a Max() or Count(). It’s an IIF(BadgeType="Eye" exists in this group, use Eye row, else use Generic row).

>Solution :

You could LEFT JOIN the main BadgeResults with the "eye" one, overriding the main value with the eye one if it exists. E.g.

SELECT
  main.WearerID, 
  main.DeepDose, 
  Nz(eye.EyeDose, main.EyeDose) AS CalculatedEyeDose,
  main.ExtremityDose
FROM 
(SELECT * FROM BadgeResults WHERE BadgeType = 'Generic') AS main
LEFT JOIN
(SELECT * FROM BadgeResults WHERE BadgeType = 'Eye') AS eye
ON main.WearerID = eye.WearerID