CODEWITHSUNDEEP
ms-accessvbams-access-2010
dmorgan20
dmorgan20

Reputation: 363

Crosstab query to only show the previous 4 weeks

I am open to suggestions on changing the way I have started to lay out my data.

What I need: I need to display a list of individuals performance week on week. To do this I have created a crosstab query with each column showing as the date (built using the query wizard).

The query actually functions fine, although I only want to show 4 columns (being 4 weeks worth of data) but really don't know where to start giving me doubt that I have taken the correct approaching using a cross tab.

because there is no conditioning its showing the full year being almost over 50 columns

Current Code:

TRANSFORM Max(tbl_Genesys_Weekly.[Field32]) AS MaxOfField32
SELECT [_tbl_Structure].[User Name]
FROM _tbl_Structure INNER JOIN tbl_Genesys_Weekly ON [_tbl_Structure].[User ID] = tbl_Genesys_Weekly.Field5
GROUP BY [_tbl_Structure].[User Name]
ORDER BY tbl_Genesys_Weekly.WC_Date
PIVOT tbl_Genesys_Weekly.WC_Date;

Any help is hugely appreciated

UPDATED CODE:

TRANSFORM Max(Format([Field32]/100,"Percent")) AS Calc
SELECT [_tbl_Structure].CSM, [_tbl_Structure].[User Name]
FROM _tbl_Structure INNER JOIN tbl_Genesys_Weekly ON [_tbl_Structure].[User ID] = tbl_Genesys_Weekly.Field5
GROUP BY [_tbl_Structure].CSM, [_tbl_Structure].[User Name]
ORDER BY tbl_Genesys_Weekly.WC_Date DESC 
PIVOT DatePart("ww", tbl_Genesys_Weekly.WC_Date)
WHERE DateDiff("ww", tbl_Genesys_Weekly.WC_Date, Date()) Between 5 and 1;

ERROR RECEIVED: Characters found after end of SQL statement

Upvotes: 0

Views: 338

Answers (1)

Gustav
Gustav

Reputation: 55806

You could use:

PIVOT DatePart("ww", tbl_Genesys_Weekly.WC_Date);

and include a where clause:

WHERE DateDiff("ww", tbl_Genesys_Weekly.WC_Date, Date()) Between 5 and 1

Note, that if you use standard ISO 8601 weeknumbers, DatePart cannot be used, and the year will have to included to obtain the correct sorting around New Year.

Upvotes: 1

Related Questions