Crosstab Query Questions

[unclefink]

I've got a crosstab query set up as followed:
Month: Row Heading
Shift: Column Header
EmpIDCount: value

I have two questions regarding this same query which hopefully would apply to all future crosstab queries I build.

Question 1: My shifts read as followed:
Days, Swings, Mids

In my query, they are listing in alphabetical order: Days, Mids, Swings, i'd like it to switch mids and swings so it lists Days, Swings, Mids.

Any suggestions?

Question 2: When I run the query to show results, its only showing me data for the months in which there is data present. Is there a way to make it show all months, Even if there is no data related to that month.

 

[plog]

1. You can only control the order of columns by hacking the way it works. A cross-tab will always sort columns ascending as strings--so you need to make your Column Header values sort the way you want by exploiting that. I suggest by prefixing your values with a letter that identifies what sort order they should fall into. That means 'Days' -> 'a_Days', 'Swings' -> 'b_Swings' and 'Mids'->'c_Mids'. You can use nested IIF statements to achieve this, or a mapping table.

2. In a query, you can't create rows of data for which their are none in the underlying data source. So, you need to make your query generate at least one record for every month you want to report on. This means you need a datasource that has that data. Let's call it ReportingMonths (this could be a table you specifically create and populate for this purpose, or it could be a query based on something that will have every month you want to report on). You bring ReportingMonths into your query, and LEFT JOIN your existing data source to it (that means you show all from ReportingMonths). Then you use the ReportingMonths month field instead of the one you currently are using for your Row Heading.

Lastly a question of my own--why this cross-tab with the promise of more? Cross-tabs are kind of a hacky work around. What are you doing with these things? They can make life difficult down the line if you try and build reports on them. What's the end game with your cross-tab?

 

[unclefink]

I'm extremely new at using crosstab queries so i'm probably either taking unnecessary steps or doing this the hard way all-together. As you speculated, i'm trying to set up a query for reporting purposes and by the way it sounds, crosstab is not the best way.

I used the month as a generic as it was probably the easier way of explaining what I was trying to do. I've actually got a long list of tasks that need to be checked by three different shifts which (each shift) consists of a number of employees. I'm trying to create a query to generate a report to count how many times a specific task was completed by a specific shift.

Sorry for all the confusion, i'm having a hard time just explaining what i'm trying to do as it is. Mentally, I can picture it.

 

[plog]

I'm trying to create a query to generate a report to count how many times a specific task was completed by a specific shift.

I would look into a traditional report. Then if that will absolutely not work and I needed a cross-tab like report, I would generate a regular query to produce the data I want and export it to Excel and use a pivot table.

Again, look into a regular report first.

 

[CJ_London]

In my query, they are listing in alphabetical order: Days, Mids, Swings, i'd like it to switch mids and swings so it lists Days, Swings, Mids.

Any suggestions?

yes - if you look at the query properties, you can set the column headings - just set them in the order you want