Crosstab Queries (1 Viewer)

Dreamweaver

Well-known member
Local time
Today, 15:50
Joined
Nov 28, 2005
Messages
2,466
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

Super Moderator
Staff member
Local time
Today, 11:50
Joined
Jan 23, 2006
Messages
15,364
I don't have any advice Mick except -- see if the Similar Threads offer anything.
Good luck.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:50
Joined
Aug 30, 2003
Messages
36,118
My first thought is using fixed labels on the report and not displaying the actual field names.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:50
Joined
Aug 30, 2003
Messages
36,118
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

Well-known member
Local time
Today, 15:50
Joined
Nov 28, 2005
Messages
2,466
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

Well-known member
Local time
Today, 15:50
Joined
Nov 28, 2005
Messages
2,466
Does this help?
It has been ages since I had to do one. :)

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
Code:
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.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:50
Joined
Sep 21, 2011
Messages
14,044
Here is one I found
Code:
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

Enthusiastic Amateur
Local time
Today, 15:50
Joined
Sep 21, 2011
Messages
14,044
Mick,
Here is s a start, though you would need to take years into account. Perhaps create a function?
Code:
SELECT tblBTCalls.Caller, tblBTCalls.CalledTime, Format([CalledTime],"ww") AS Expr1, Format(Date(),"ww") AS Expr2, [expr2]-[expr1] AS Expr3
FROM tblBTCalls;
 

Dreamweaver

Well-known member
Local time
Today, 15:50
Joined
Nov 28, 2005
Messages
2,466
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

Well-known member
Local time
Today, 15:50
Joined
Nov 28, 2005
Messages
2,466
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


SQL:
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
SQL:
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");
 
Last edited:

Users who are viewing this thread

Top Bottom