asked on

Problem with this SQL of a crosstab Query

Below is the SQL for my crosstab query. Now the query does work but it only shows one record per person. So if a person is assigned two PQSs it will only show the first one they were assigned and not any others. How do I get it to show the others?

Thanks
John

TRANSFORM Max(IIf([Status]=4,"Q",IIf([Status]=3,"I/Q",IIf([Status]=2,"I/P",IIf([Status]=1,"A"))))) AS Progress
SELECT tblCrew.Rank_Rate & " " & [Lname] & "," & [FName] & " " & [MName] AS Name, tblAssignedPQS.[NAVEDTRA Number]
FROM tblCrew INNER JOIN tblAssignedPQS ON tblCrew.[Personnel ID Code]=tblAssignedPQS.[Personnel ID Code]
WHERE (((tblAssignedPQS.[Watchstation Number]) Between "301*" And "320*") And ((tblAssignedPQS.[NAVEDTRA Number])=Forms!frmPQSBuildChart!SelectWatchstation))
GROUP BY tblCrew.Rank_Rate & " " & [Lname] & "," & [FName] & " " & [MName], tblAssignedPQS.[NAVEDTRA Number]
PIVOT tblAssignedPQS.[Watchstation Number] In ("301","302","303","304","305","306","307","308","309","310","311","312","313","314","315","316","317","318","319","320");

Open in new window

RDWaibel

Hey John,
Can I get some sample data from the table?

John Sheehy

ASKER

Attached is the database I am using stripped down for simplicty.

You will need to open the form up and select in the NAVEDTRA Number the 3rd one. 43462-1A.

Then open the query PQS - Generic Chart Crosstab.

If you look at the table tblAssignedPQS, you will see I am assigned two PQSs but only one shows in the query.

Any thoughts?

Thanks

John

db1.mdb

ASKER CERTIFIED SOLUTION

lapatiya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Sheehy

ASKER

You were right, made the changes suggested and all though it caused a headache with some users, it works the way they wanted it to work. So all is good now. Thank you very much

John

John Sheehy

ASKER

Awesome answer and I thought it was a bit hard but once again EE came through.