Ask a new question

Crosstab query to display all records according to another table field

Dear MVP's

Thank you for your previous posts about crosstab queries and keeping all rows headings according to another table.

Can i simply take a copy of another crosstab query, and change the sql

I wish to display all names.

This is where i got to..

TRANSFORM First[(Dashboard View Start].Dashboard) AS FirstofDashboard

SELECT [Dashboard View Start].[Full Name]

FROM  [Dashboard View Start].[Job Order]

GROUP BY [Dashboard View Start].[Full Name]

PIVOT  [Dashboard View Start].[Job Order] In ("7-10 AM", "8-11 AM", "9 AM-12 PM");

Result should be

                Time 1,  Time 2, Time 3, m...

Name 1       1                          3

Name 2                                                       no data exists for intersection, but wish to have all names according to a customer table field.

Name 3       22           34

n...

Any help would be appreciated ( i believe it can e done by left join, or other union joins)

Thank you kindly

Simon

Answer
Answer
I'm afraid I don't have a solution for that. Sorry!
---
Best wishes, HansV
https://www.eileenslounge.com

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

I assume that you have a table or query that lists the unique full names.

Create a new query.

Add the table or query that lists the full names, and the crosstab query.

Join them on the Full Name field.

Double-click the join line, and select the option to show all records from the table or query that lists the full names.

Add the Full Name field from the table or query that lists the full names to the query grid, plus the 7-10 AM etc. fields from the crosstab query.

---
Best wishes, HansV
https://www.eileenslounge.com

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated January 18, 2024 Views 1,860 Applies to: