Nested Query Question

[mindbender]

Hopefully I can explain this ok



My data looks like this

ID rep name opty name role SE name
1 dave opty1 VP
1 bill opty1 SE
2 carl opty2 CIO
2 wayne opty2 SE
3 joe opty3 CEO

what I woudl like to do is this......if the ID is duplciated copy the rep name field to the SE name field where the role = SE else null

like this

ID rep name opty name role SE name
1 dave opty1 VP bill
1 bill opty1 SE bill
2 carl opty2 CIO wayne
2 wayne opty2 SE wayne
3 joe opty3 CEO

[June7]

Will there be only 2 records per ID?

One approach requires a unique ID field (autonumber would serve) to create nested subquery.

Another approach uses DLookup.

[mindbender]

Thanks I am trying DLookup now