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?


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 SheehySystem Security ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hey John,
Can I get some sample data from the table?
John SheehySystem Security ManagerAuthor 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?




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.

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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySystem Security ManagerAuthor 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 SheehySystem Security ManagerAuthor Commented:
Awesome answer and I thought it was a bit hard but once again EE came through.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.