Problem With Crosstab Query

Question

I'm trying to create a report each month of players who have signed up to play on specific dates during the month. The report should show the dates as column headers with the list of players below. The column header should be multiple lines with the date of the event as the first line, location as the second line, and start time as the third line. I created code to put all this info in a field called GridHeading with appropriate carriage returns and line feeds for each date in the table of scheduled games.

I created a report that looked great for the first time last month (November), but when I tried to run it for December it didn't work. The crosstab query had specific dates and when the report ran, it was using the previous months dates as the column headings and row information. Is there a way to make the report correct for each upcoming month?

Answer 1

The column header should be multiple lines with the date of the event as the first line, location as the second line, and start time as the third line？？

can you share some source data and expected query result？

Answer 2

I would suggest you create a report that starts on a Monday and ends after 10 dates (5 weeks of Mondays and Fridays). Create a form [FormDateSelect] with a combo box [cboGame] that selects a unique Monday date. The Row Source would be something like:

SELECT DISTINCT [GameDate] 

FROM TsunamiSue 

WHERE Format([GameDate],"ddd")="MON" 

ORDER BY [GameDate];

Then create a crosstab query with SQL like:

PARAMETERS [Forms]![frmDateSelect]![cboGame] DateTime; 

TRANSFORM Avg(Score) AS AvgOfScore 

SELECT Player 

FROM TsunamiSue 

GROUP BY Player 

PIVOT "Day" & DateDiff("d",[Forms]![frmDateSelect]![cboGame],[GameDate]) 
In ("Day0","Day4","Day7","Day11","Day14","Day18","Day21","Day25","Day28","Day32","Day35");

Your report would be based on the crosstab with calculated column headings by adding 0,4,7,11,14,... to the date form the combo box on the form. The text boxes in the detail section would never change and always show 5 weeks.

qxtbPlayerScoresDates

| | | | | | | | | | | | | Player | Day0 | Day4 | Day7 | Day11 | Day14 | Day18 | Day21 | Day25 | Day28 | Day32 | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | A | 24 | 8 | 18 | 13 | 14 | 16 | 24 | 11 | 11 | 23 | | B | 25 | 12 | 25 | 24 | 21 | 6 | 6 | 14 | 17 | 23 | | C | 14 | 21 | 5 | 6 | 20 | 6 | 22 | 24 | 14 | 6 | | D | 13 | 17 | 20 | 7 | 23 | 7 | 5 | 20 | 12 | 15 | | E | 8 | 13 | 10 | 21 | 15 | 5 | 13 | 24 | 10 | 11 |

Answer 3

Your solution won’t work because the dates are every Monday and Friday during each month. There is no consistent formula that I know of to calculate differences between the dates to get correct column labels. In December this month dates are Dec 2, 5, 9, 12, 26, 19, 23, 26, and 30. I need 9 columns with those date headers and names below each date. Any additional advise would be appreciated. Thank you.

Answer 4

Sounds like a nice report. I have an FAQ on creating crosstab reports with dynamic months at Tek-Tips.