Crosstab Queries

[Dreamweaver]

I have build a number of crossbab queries.
one a last 12 month units sold which is not the problem

my problem is a weekly crosstab as in image I need to enbend it into a report and form but the column heads will chenge depening on the week
I.E. Wk19 - Wk31 what I need is for it to allways show Wk1 - Wk12 I've tried everything I can think of without luck, I haven't built a lot of crosstabs so decided to add a number to the example I'm working on this being the first with a problem.

 

[jdraw]

I don't have any advice Mick except -- see if the Similar Threads offer anything.
Good luck.

 

[pbaldy]

My first thought is using fixed labels on the report and not displaying the actual field names.

 

[Dreamweaver]

Thanks @jdraw

My first thought is using fixed labels on the report and not displaying the actual field names.

Thanks @pbaldy Can you give a bit more detail as not sure how to do that thanks mick

 

[Gasman]

Does this help?
It has been ages since I had to do one.

Creating Microsoft Access Monthly Summary Crosstab Query Reports without Programming Code

Creating Microsoft Access Summary Crosstab Query Reports using a Crosstab Query and No Code showing Monthly Totals

www.fmsinc.com

 

[pbaldy]

Thanks @jdraw


Thanks @pbaldy Can you give a bit more detail as not sure how to do that thanks mick

I'm not on a computer but I use code to set report control sources dynamically. If Gasman's method doesn't work for you I can dig it up later.

 

[Dreamweaver]

I'm not on a computer but I use code to set report control sources dynamically. If Gasman's method doesn't work for you I can dig it up later.

Cool Thanks Will let you know how I get on

 

[Dreamweaver]

Does this help?
It has been ages since I had to do one.

Creating Microsoft Access Monthly Summary Crosstab Query Reports without Programming Code

Creating Microsoft Access Summary Crosstab Query Reports using a Crosstab Query and No Code showing Monthly Totals

www.fmsinc.com

Thanks I did look at that and used it for my 12 monthy report but It requires the title to be in (Wk1,Wk2,Wk3,Wk4,Wk5,Wk6,Wk7,Wk8,Wk9,Wk10,Wk11,Wk12) which as I only want to show the last 12 weeks which the start date is defined by

Between DateAdd("ww",-12,Date()) And Date()

I have a feeling I need to treat it as a math problem but my maths not upto it so regardless the start week I.E. week 1 if it's 28 it needs to be changed to 1 and 29 changed to 2 and so on for the 12 week period, I was playing with a query to do this which I would have used in the crosstab query instead of tblorders which holds the date but just gave myself a headache.

 

[Gasman]

Here is one I found

TRANSFORM Sum([SumOfCallSecs]/86400) AS Duration
SELECT qryWeekCallSummary.WeekEndDate AS Expr1
FROM qryWeekCallSummary
WHERE ((([qryWeekCallSummary].[CallSystem])='BT'))
GROUP BY qryWeekCallSummary.WeekEndDate
ORDER BY qryWeekCallSummary.WeekEndDate DESC
PIVOT qryWeekCallSummary.Caller In ("Funeral Plans","Josh Jones","Laura Evans","Michelle Lewis","Nick Rideout","Sam Overfield","Seaneen Parkhouse");

so yes, create a field to hold your weeknums

 

[Gasman]

Mick,
Here is s a start, though you would need to take years into account. Perhaps create a function?

SELECT tblBTCalls.Caller, tblBTCalls.CalledTime, Format([CalledTime],"ww") AS Expr1, Format(Date(),"ww") AS Expr2, [expr2]-[expr1] AS Expr3
FROM tblBTCalls;

 

[Dreamweaver]

Thanks @Gasman I will give it a go taking a break and gone back to adding data just to clear my head I have thought of another way it's a dirty fix but will work just have to code it that is have all 52 weeks but hide those I don't want then just need to nove the controls which is easy then have to lookup the Avg for a 12 wk period.

 

[Dreamweaver]

Thanks @Gasman that seems to have got me what I needed I only had to make a small alteration [expr2]-[expr1]+1.

many thanks mick

SELECT tblOrders.OrderID, Format([OrderDate],"ww") AS Expr1, Format(Date(),"ww") AS Expr2, [Expr2]-[Expr1]+1 AS Expr3, tblOrders.OrderDate
FROM tblOrders
WHERE (((tblOrders.OrderDate) Between DateAdd("ww",-12,Date()) And Date()))
ORDER BY tblOrders.OrderDate;

I've updated the Crosstab like below

TRANSFORM Sum(tblOrdersLineItems.Qty) AS SumOfQty
SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.ReorderLevel, tblProducts.ReorderNumber, Avg(tblOrdersLineItems.Qty) AS [Avg]
FROM QryConvertOrderDateto12weeks INNER JOIN (tblProducts INNER JOIN tblOrdersLineItems ON tblProducts.ProductID = tblOrdersLineItems.ProductID) ON QryConvertOrderDateto12weeks.OrderID = tblOrdersLineItems.OrderID
WHERE (((QryConvertOrderDateto12weeks.OrderDate) Between DateAdd("ww",-12,Date()) And Date()))
GROUP BY tblProducts.ProductID, tblProducts.ProductName, tblProducts.ReorderLevel, tblProducts.ReorderNumber
PIVOT "Wk" & [Expr3] In ("Wk1","Wk2","Wk3","Wk4","Wk5","Wk6","Wk7","Wk8","Wk9","Wk10","Wk11","Wk12");