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

John SheehySecurity AnalystAsked:
Who is Participating?
 
lapatiyaCommented:
John,

Fist off all, I already got the solution for you.

Problem is;
Criteria of the "Wachingstation number" ( Between "301*" And "320*" ) in the "PQS - Generic Chart Crosstab". Because in your "tblPQSWatchstations", data type of "Watchstation Number" is TEXT, then again you are trying to validate your workstations number from 301 to 320, using the BETWEEN clause, assuming the data type of your "Watchstations Number" as NUMBER field. So it's not correct according to the database theories.

Solution;
Change the criteria of the "Wachingstation number" ( Between "301*" And "320*" ) in the "PQS - Generic Chart Crosstab" to Between "301" to "302" without the stars according to the current tables design you have. But my strong recommendation for you is to keep numeric data types in numeric and string data types in string.

Good Luck!
   
0
 
RDWaibelCommented:
Hey John,
Can I get some sample data from the table?
0
 
John SheehySecurity AnalystAuthor Commented:
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
0
 
John SheehySecurity AnalystAuthor Commented:
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
0
 
John SheehySecurity AnalystAuthor Commented:
Awesome answer and I thought it was a bit hard but once again EE came through.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.