Return One Record Per Individual

[Bill B.]

I have a Union Query that returns these results:

Department # Department Name IOD Paid WC Paid
106011 HRR THERESE SMITH 67.67
109021 Plng KENTON JONES
5566.86
109021 Plng KENTON JONES 159.63
109031 Plng GARY BROWN 611.36

What I need is this:

Department # Department Name IOD Paid WC Paid
106011 HRR THERESE SMITH 67.67
109021 Plng KENTON JONES 159.63
5566.86
109031 Plng GARY BROWN 611.36

What options do I have available to me. Thanks!

Bill B.

 

[Jerry Whittle]

Which WC Paid do you need? Largest, Smallest, Newest, Oldest?

 

[Bill B.]

Jerry,

It doesn't matter whether I have the largest, smallest, oldest when it comes
to WC Paid. For example, Kenton Jones is one of approx 50 who have both a WC
payment and an IOD payment. What I'd like to have returned is one record for
Kenton Jones and the others which has the fields Department #, Department,
Name, IOD Paid and WC Paid. Is this possible?

Bill B.

 

[Jerry Whittle]

This assumes that there are no more than 2 records for each of the Department
#, Department, Name combinations. Change the word 'Payments' to the name of
the Union Query.

SELECT [Department #],
[Department],
[Name],
Max([IOD Paid]) AS IOD_Paid,
Max([WC Paid]) AS WC_Paid
FROM Payments
GROUP BY [Department #],
[Department],
[Name] ;

 

[Bill B.]

Jerry,

Thanks a lot. I am getting the results I need.

Bill B.

This assumes that there are no more than 2 records for each of the Department
#, Department, Name combinations. Change the word 'Payments' to the name of
the Union Query.

SELECT [Department #],
[Department],
[Name],
Max([IOD Paid]) AS IOD_Paid,
Max([WC Paid]) AS WC_Paid
FROM Payments
GROUP BY [Department #],
[Department],
[Name] ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

It doesn't matter whether I have the largest, smallest, oldest when it comes
to WC Paid. For example, Kenton Jones is one of approx 50 who have both a WC
payment and an IOD payment. What I'd like to have returned is one record for
Kenton Jones and the others which has the fields Department #, Department,
Name, IOD Paid and WC Paid. Is this possible?

Bill B.