Merge/Filter 3 matching fields (1 Viewer)

I’m trying to create a report summarizing payroll for our drivers. The problem I have is each trip can have multiple drivers (1-3), so being an access novice, I created Driver1, Driver2 and Driver3 (lookup fields within the “trip table”). Now I’m trying to merge all matching drivers (1-3) for totals on a report. i.e. Linda Smith pay total as driver1, driver2 and driver3 roles combine. The two images will explain better (report is what’s desired, it’s from a different database so numbers do not match but layout 1=driver1 2=driver2...)

create a union query:

select driver1lu as driver, driver1lupay as pay from yourTable
union
select driver2lu as driver, driver2lupay as pay from yourtable where not (driver2lu is null)
union
select driver3lu as driver, driver3lupay as pay from yourtable where not (driver3lu is null);

you would be better to normalise your data going forward (and do away with the lookups in your table whilst you are at it).

what you can do in the short term is create a union query to mimic normalised data - something along the lines of

arnelgp said:

create a union query:

select driver1lu as driver, driver1lupay as pay from yourTable
union
select driver2lu as driver, driver2lupay as pay from yourtable where not (driver2lu is null)
union
select driver3lu as driver, driver3lupay as pay from yourtable where not (driver3lu is null);

Click to expand...

Problem solved! Thank You arnelgp! Been racking my brain for hours, your help is greatly appreciated. Got the results I need in the query will finish report tomorrow.

Problem solved!

Click to expand...

Maybe for the moment.
You should really take CJ's advice and normalize your data and get rid of the table level lookup fields. You'll be glad you did, in a week or two, when you're looking for another work around.