Hello,
I am using a dynamic crosstab report to track performance statistics for my company and I have hit a problem.
I would like the option to track stats daily (for the last 7 complete days), weekly (for the last 6 weeks) and monthly (for the last 6 complete months).
Daily and monthly are not causing me a problem - I have used the following code to construct the query: - strXT = "TRANSFORM Sum(Query2.STAT) AS SumOfSTAT " & _
-
"SELECT Query2.Site, Query2.OM, Query2.TM, Query2.Name " & _
-
"FROM Query2 " & _
-
"GROUP BY Query2.Site, Query2.OM, Query2.TM, Query2.Name " & _
-
strPivot & ";"
And the following to create the "strPivot" string:
Daily: - strPivot = "PIVOT [DATE]"
Monthly: - If Format(strStopDate, "MMM") = "Jan" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Aug"", ""Sep"", ""Oct"", ""Nov"", ""Dec"", ""Jan"")"
-
End If
-
If Format(strStopDate, "MMM") = "Feb" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Sep"", ""Oct"", ""Nov"", ""Dec"", ""Jan"", ""Feb"")"
-
End If
-
If Format(strStopDate, "MMM") = "Mar" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Oct"", ""Nov"", ""Dec"", ""Jan"", ""Feb"", ""Mar"")"
-
End If
-
If Format(strStopDate, "MMM") = "Apr" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Nov"", ""Dec"", ""Jan"", ""Feb"", ""Mar"", ""Apr"")"
-
End If
-
If Format(strStopDate, "MMM") = "May" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Dec"", ""Jan"", ""Feb"", ""Mar"", ""Apr"", ""May"")"
-
End If
-
If Format(strStopDate, "MMM") = "Jun" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jan"", ""Feb"", ""Mar"", ""Apr"", ""May"", ""Jun"")"
-
End If
-
If Format(strStopDate, "MMM") = "Jul" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Feb"", ""Mar"", ""Apr"", ""May"", ""Jun"", ""Jul"")"
-
End If
-
If Format(strStopDate, "MMM") = "Aug" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Mar"", ""Apr"", ""May"", ""Jun"", ""Jul"", ""Aug"")"
-
End If
-
If Format(strStopDate, "MMM") = "Sep" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Apr"", ""May"", ""Jun"", ""Jul"", ""Aug"", ""Sep"")"
-
End If
-
If Format(strStopDate, "MMM") = "Oct" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""May"", ""Jun"", ""Jul"", ""Aug"", ""Sep"", ""Oct"")"
-
End If
-
If Format(strStopDate, "MMM") = "Nov" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jun"", ""Jul"", ""Aug"", ""Sep"", ""Oct"", ""Nov"")"
-
End If
-
If Format(strStopDate, "MMM") = "Dec" Then
-
strPivot = "PIVOT Format([DATE],""MMM"") IN (""Jul"", ""Aug"", ""Sep"", ""Oct"", ""Nov"", ""Dec"")"
-
End If
I am now stuck with how to create the pivot for a dynamic weekly range. Ideally I would like the user to select an end date (any date up to and including yesterday) and the report would give a total for each of the last 6 weeks - preferably the week could run from any day, but if I have to use Mon-Sun that would also be acceptable. Can anyone help me with this? I've googled like there's no tomorrow and I'm not having any luck!
I can supply any more information/code if required.
Many thanks,
Olly
11 6155
Just a little bump! Can anyone help me with this? Please....?
What about [DATE] BETWEEN (EndDate - 42) AND EndDate
Thanks Chip, but that would give me a daily total for each day in the 6 week date range (if used in the pivot). What I'm after is a weekly total for each of 6 weeks.
Any other ideas?
Many thanks
Still at a loss with this one.... anyone?
So I'm guessing there's no way to do what I want to do in this case :(
Think I may have to give up on this idea unless anyone wants to chime in?
Hi Olly. You can calculate a weekly interval in the base query on which you are totalling - for example by using DateDiff("w", [your transaction date], date()).
If you put a where clause of <=6 on this you will restrict the query to the last 6 complete weeks. You can then pivot the data on the number of weeks - 0 is the current week (which is generally incomplete of course), 1 the previous week and so on, totalling the transactions for each week as appropriate.
I leave it for you try this out on your own data and to do some form of week-beginning calculation for your pivot headers. I've tried this approach on a simple transaction count basis (producing the number of transactions received in each week) and it works fine for me (without fancy weekdate headers).
-Stewart
... and further to the above here is a function which will return the start date of the current week. It can be used to provide custom date headers for use with your datediff-based calculations: - Public Function fStartofWeek(somedate, Optional StartDayNo = 2)
-
'Returns the date of the first day of the current week
-
'Default is to use the system-defined start day no of 2
-
'(corresponding to Monday). Range of 1 to 7 for this value.
-
'
-
Dim intDayOfWeek As Integer, intAdjustDays As Integer
-
If Not IsNull(somedate) Then
-
intDayOfWeek = Weekday(somedate)
-
intAdjustDays = intDayOfWeek - StartDayNo
-
If intAdjustDays < 0 Then
-
intAdjustDays = intAdjustDays + 7
-
End If
-
fStartofWeek = CDate(somedate - intAdjustDays)
-
End If
-
End Function
An example query based on a test table (no relation to your own), which uses a custom format for the date to order the crosstab correctly: - TRANSFORM Count(tblTemp.CallTime) AS CountOfCallTime
-
SELECT "Test" AS TestGroup
-
FROM tblTemp
-
WHERE (((DateDiff("w",fStartOfWeek([CallDate]),Date()))<=6))
-
GROUP BY "Test"
-
PIVOT Format(fStartofWeek([CallDate]),"yyyy-mm-dd");
And the result of that query: - TestGroup 2008-12-22 2008-12-29 2009-01-05 2009-01-12 2009-01-19 2009-01-26 2009-02-02 2009-02-09
-
Test 78 219 227 221 206 232 187 85
-Stewart
Stewart, thank you so much!
As I'm sure you can tell, I'd pretty much given up hope of this working!
Haven't tried it out yet, but it looks very promising - I'll let you know how I get on.
Thanks again,
Olly
@Stewart Ross Inverness
Hi Stewart,
I've tried this and I have a new problem...
I should have mentioned that my initial query is actually a SQL pass-through to an Oracle db, so (as I now realise) DateDiff won't work.
Do you know how to write this: DateDiff("w", [your transaction date], date()).
In a syntax Oracle will accept?
Many thanks.
Hi Olly. I can only suggest that you feed the result of the pass-through query to an Access query (using the pass-through to provide a view of the Oracle data) and perform the calculations in Access - once the data has been obtained from the Oracle back-end DB Access should be able to process it as normal.
The original Oracle query would not be able to handle the date function I have suggested either I guess...
-Stewart
Good idea. Thanks very much for your help.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: SharkSYA |
last post by:
After canvassing ideas it appears that the way I need to do it is not
possible.There are 126 rooms, more to be added, and it needs to print a
report or reports showing 76 days of bookings.
There...
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: Richard Hollenbeck |
last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank
you. I try to ask relevant questions so that they don't just benefit me,
but also benefit the group. I'm currently...
|
by: deejayquai |
last post by:
Hi
I've created a crosstab query and displayed it as a sub-report in my
main report. This is fine until the data changes and the column names
become incorrect. I know I have to create a...
|
by: deejayquai |
last post by:
Hi
I'm trying to produce a report based on a dynamic crosstab. Ultimately
i'd like the report to actually become a sub report within a student
end of year record of achievement. The dynamic...
|
by: deejayquai |
last post by:
I've worked through the MS KB example and it doesn't really help me as
I missing the basics of what the code is doing. I've posted a couple
of times here in thsi group but with no success. Could...
|
by: Brad |
last post by:
Thanks for taking the time to read my question.
I have a table of data that has Date, Data and Category. I need to
show, in
a report, each Categories Data by Date. The Date has to be it's own...
|
by: salad |
last post by:
Operating in A97.
I didn't receive much of a response conserning Pivot tables in Access.
Pivot tables are nice, but a CrossTab will work for me too.
Using a Pivot table, one is actually...
|
by: ollyb303 |
last post by:
Hi,
I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor.
I have one crosstab query (Query1) which...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |