Query different data from the same field, and group by common field 2

[d22]

I have a swim team table I am building. I have 3 fields, swimmer_name, Event# and Heat_Lane. I would like to have a separate column for each unique Event# and Heat_lane( the 2 are associated to each other. I can get the data to return, but I would like to group by the swimmer name. Here is an example of my query:
SELECT meet_event_table.SwrName, IIf(meet_event_table.[Event_Num]="#23",meet_event_table.[Heat_Lane],Null) AS free, IIf(meet_event_table.[Event_Num]="#47",meet_event_table.[Heat_Lane],Null) AS Breast
FROM meet_event_table INNER JOIN swimmers ON (meet_event_table.SwrName=swimmers.Name) AND (meet_event_table.SwimrNbr=swimmers.Number)
WHERE swimmers.Age<=6 And swimmers.Sex="F" And meet_event_table.Event_Num="#23" Or meet_event_table.Event_Num="#47";

Right now I get
Swimmer Name Free Breast
Timmy Brown 1/4
Timmy Brown 2/4

I would like to get
Swimmer Name Free Breast
Timmy Brown 1/4 2/4

Thank you in advance for any assistance you can give.
D.

 

[lameid]

It sounds like what you really want is a crosstab query and not using the IIF function in this case.

Check it out in help and post back if you still need help or get it working.

 

[d22]

I don't think a crosstab will work, because it is a non-calculating field. The example 1/4 is the heat number/lane number. Thanks.

 

[dhookom]

Your crosstab value doesn't have to be numeric. You can use First or Last or Min or Max with a text field.

Duane
Hook'D on Access
MS Access MVP

 

[d22]

That did work on the non-calculating field. Do you know how I could have event 1 - 11 all group up in the same field? Meaning if the event number = 1 - 11, then put the heat_lane value in the first available column. Thanks.

 

[lameid]

Post your new SQL...

If I follow correctly, it seems like are pivoting of Event_Num and want to pivot on Heat_Lane. Chances are swaping your pivot and transform fields is what you are after.

 

[d22]

That worked. Thank you all.