Solved

Crosstabs, sort dynamic columns by descending column totals, show blank columns

Posted on 2006-06-16
25
335 Views
Last Modified: 2012-06-27
Hello all, I am trying to create a crosstab for a report.  This is the SQL I am using for my working crosstab...

TRANSFORM Count(x.SKU_NBR) AS CountOfSKU_NBR
SELECT PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR AS Week
FROM PRTHD_FSCL_YR_WK, x
WHERE (((x.TIMESTAMP) Between [WK_BGN_DT] And [WK_END_DT]))
GROUP BY PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR
PIVOT x.PROCESSING_FLG In (0,1,2,3,4,5,6,7,8,9,10,11,88,99);

You will notice that I have a custom header to show the type codes of even the blank headers, however this will become inacurate if another typecode is added, can this be done in a way so as to account for all type codes (incluuding blank ones) on the fly?

The other slightly less important , however probably more difficult question is whether or not I can sort the columns by a sum of their totals in descending order from left to right.

Currently I copy the cross tabs into excel where I match the processing flag to the statement it represents, calculate the totals for each column and add percentages to the ends of the rows between proc type 99 devided by the sum of all the columns on each row, however I wouldnt mind putting all this in a report so that I could just open the report and have the data how I need it.

Thanks so much,

Chris
0
Comment
Question by:orbitus
  • 16
  • 9
25 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16929849
Hello orbitus

You really want a Pivot table. Access' cross-tabs are not up to something like that. To get this done in Access, you could perhaps...

Create a report with, say, 20 columns. Use a fiest query on your x table to get a list of codes and fill in the title labels with that information. You could remove the In() option of the PIVOT clause to get all columns with values, and assign the proper control sources to each column, according to the first query.

Meanwhile, you could also have yet another query showing only the totals, in descending order. This would give you the desired sort order of the columns, and your could move around the label contents and the control sources to get that order.

You would need a test to see if you have more than the maximum of 20 columns and either transform the last one into "others" or refuse to print.

All this would be done during the report's open event.

As you see, this is rather complex. Tell me if you want to go that way...

(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 16950641
lol yeah that is a bit complex, I am willing to try it though, I would just need some help. any assistance would be greatly appreciated.

Chris
0
 
LVL 58

Expert Comment

by:harfang
ID: 16955298
orbitus, let's get started then.

You need three queries: the data, the totals, and the column headers.

1) Data. This is your current query, with this change to the PIVOT clause:

PIVOT 'F' + x.PROCESSING_FLG;

This will generate columns like F0, F1, ... F99

2) Make a simple total query showing the total for each "processing flag", in descending order of the total.
You will need this one to assign query columns to report columns.

3) You probably a table that translate the "processing flags" into something humanly readable. Create a query that translates "F0" to "apples", "F1" to "oranges" an "F99" to "unknown" or the like.
This will provide the column labels.

Try to get these working (post them here if needed) and we can then start on the VB to build the report.

Cheers!

(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 16958780
Just to be clear, I assume that the

PIVOT 'F' + x.PROCESSING_FLG;

should be

PIVOT 'F' & x.PROCESSING_FLG;

as the first option gives an error, sound right?
0
 
LVL 4

Author Comment

by:orbitus
ID: 16959292
ok I believe I have finsihed what you have asked here are the queries that were assigned (hehe)...

TRANSFORM Count(x.SKU_NBR) AS CountOfSKU_NBR
SELECT PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR AS Week
FROM PRTHD_FSCL_YR_WK, x
WHERE (((x.TIMESTAMP) Between [WK_BGN_DT] And [WK_END_DT]))
GROUP BY PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR
PIVOT 'F' & x.PROCESSING_FLG;

SELECT Sum(1) AS Tickets, "F" & [PROCESSING_FLG] AS PrcFlg
FROM x
GROUP BY "F" & [PROCESSING_FLG]
ORDER BY Count(x.SKU_NBR) DESC;

SELECT "F" & [PROC_FLAG] AS Expr1, STORE_PROC_FLAG.TYPE_DESC
FROM STORE_PROC_FLAG;

This is very exciting to see how this will all work. (I am such a dork! haha)

Thanks again for your assistance,

Chris
0
 
LVL 58

Expert Comment

by:harfang
ID: 16960938
Ok, that looks about right. We can now start on the report. Let me get back to this later, though, this needs some detailed explanations.
(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 16960968
I understand, take your time. Thank you soo much!

Chris
0
 
LVL 4

Author Comment

by:orbitus
ID: 16960977
I am quite a bit better in VB than I am in Access if that helps.

Chris
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 16976982
Chris,

Sorry for the delay. Your queries should in fact return totally compatible data. I tried to ajust them to do that, but I cannot test them here of course:

qryTestData (almost unchanged):
TRANSFORM Count(*)
SELECT PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR AS Week
FROM PRTHD_FSCL_YR_WK, x
WHERE x.TIMESTAMP Between [WK_BGN_DT] And [WK_END_DT]
GROUP BY PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR
PIVOT 'F' & x.PROCESSING_FLG;

qryTestTotals (compatible FROM and WHERE clauses):
SELECT 'F & PROCESSING_FLG AS PrcFlg, Count(*) As Tickets
FROM PRTHD_FSCL_YR_WK, x
WHERE x.TIMESTAMP Between WK_BGN_DT And WK_END_DT
GROUP BY PROCESSING_FLG
ORDER BY Count(*) DESC;

qryTestLabels:
SELECT 'F' & PROC_FLAG AS Key, TYPE_DESC As Label
FROM STORE_PROC_FLAG;


Now, let's create the report.

It should have a report header, or page header if you want to repeat the column labels on each page, to hold the labels. The detail section will display the rows. For this reason, the report is  based on qryTestData. The fist field in the detail section will show the week (and you can sort by week as well, naturally). The remaining controls will be initially unbound, and bound only through code.

The header will have labels "lblProcFlag1", "lblProcFlag2", ... "lblProcFlag20" (for twenty columnsm). The detail section will need textboxes "txtData1", "txtData2", ... "txtData20". If you need totals, you will need again 20 textboxes for each footer (report footer and group footer if you use them), for example "txtSum1" to "txtSum20".

The following Open event procedure will then set up the  columns in the correct order. This uses only one set of totals, in the report footer.

---------------------------------------------------------------------------------->8---
Option Compare Database
Option Explicit

Const MAXCOLS = 20

Private Sub Report_Open(Cancel As Integer)

    Dim recLabels As DAO.Recordset
    Dim recTotals As DAO.Recordset
    Dim intCol As Integer
   
    With CurrentDb
        Set recLabels = .OpenRecordset("qryTestLabels", dbOpenSnapshot)
        Set recTotals = .OpenRecordset("qryTestTotals", dbOpenSnapshot)
    End With
   
    ' PLACE COLUMNS IN DESIRED ORDER
    Do Until recTotals.EOF
        intCol = intCol + 1
        If intCol > MAXCOLS Then Exit Do
        Me("txtData" & intCol).ControlSource = recTotals!PrcFlg
        Me("txtSum" & intCol).ControlSource = "=Sum(" & recTotals!PrcFlg & ")"
        With recLabels
            .FindFirst "Key='" & recTotals!PrcFlg & "'"
            If .NoMatch Then
                Me("lblColumn" & intCol).caption = "Column " & intCol
            Else
                Me("lblColumn" & intCol).caption = !Label
            End If
        End With
        recTotals.MoveNext
    Loop
   
    ' CHECK FOR TOO MANY COLUMNS!
    If Not recTotals.EOF Then
        ' we have too many columns...
        With Me("txtData" & MAXCOLS)
            .ControlSource = "=Nz(" & .ControlSource & ")"
        End With
        Me("lblColumn" & MAXCOLS).caption = "OTHERS"
        Do Until recTotals.EOF
            ' create total column
            With Me("txtData" & MAXCOLS)
                .ControlSource = .ControlSource _
                    & "+Nz(" & recTotals!PrcFlg & ")"
            End With
            recTotals.MoveNext
        Loop
        ' make sure last column's total is OK
        Me("txtSum" & MAXCOLS).ControlSource _
            = "=Sum(" & Mid(Me("txtData" & MAXCOLS).ControlSource, 2) & ")"
    End If

End Sub
---------------------------------------------------------------------------------->8---

Of course, recTotals!Tickets already has the total computed. You could hence just put that into the report footer's controls (text box or label's caption). However, I felt it best to have the report redo the computation, in a manner of double check. If the totals match those of your query, you will have more confidence in the results.

I hope this will work, good luck!
(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 16983252
hmm, the only problem I seem to have is when I try to set qryTestData as the query for the report, as soon as I do that it holds and stays there.  Now, normally the crosstab takes a very long time to pull up the data but not this long, I gave up after about 15 minutes assuming something went wrong, any ideas?

Chris
0
 
LVL 58

Expert Comment

by:harfang
ID: 16984454
Well, the report attempts to gather at least the column names and the record count. If it's too complex, Access is known to hang...

We will have to do one of two things: optimize your query or use a temp table. The query does look odd, but since you said it was working, I didn't mention it.

Could you give the table structure of PRTHD_FSCL_YR_WK and "x", with the relevant fields, keys and indexes? And also explain the idea of this query between two unrelated tables?

(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 16992403
Table x
----------------------------------------------------------------------------------------------------------------------------
Name                                                  Type                        Size
         RCDR                                                  Text                                     3

                  AllowZeroLength:           False
                  Attributes:                Variable Length
                  CollatingOrder:            General

                  DataUpdatable:             False
                  GUID:                      {guid {87D32F42-3BEE-4FDB-BC74-93F9B38EA32C}}
                  OrdinalPosition:           1

                  Required:                  False
                  SourceField:               RCDR
                  SourceTable:               x


         TICKET                                                Text                                    12
                  AllowZeroLength:           False
                  Attributes:                Variable Length

                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {3DD405E4-5279-4AA4-8613-621C096B35BF}}
                  OrdinalPosition:           2

                  Required:                  False
                  SourceField:               TICKET
                  SourceTable:               x


         CreatedDate                                           Date/Time                                8
                  AllowZeroLength:           False

                  Attributes:                Fixed Size
                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {3891C334-0FC0-4A2B-BB33-06C8D4D0AB2A}}

                  OrdinalPosition:           3
                  Required:                  False
                  SourceField:               CreatedDate

                  SourceTable:               x

         SKU_NBR                                               Long Integer                             4
                  AllowZeroLength:           False

                  Attributes:                Fixed Size
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {AECA7A88-B946-4749-A539-573CA94C3FB3}}
                  OrdinalPosition:           4
                  Required:                  False
                  SourceField:               SKU_NBR

                  SourceTable:               x

         QTY                                                   Integer                                  

                  AllowZeroLength:           False
                  Attributes:                Fixed Size

                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {AB05730B-70EF-4F44-A5AB-AB75BF91CEA7}}
                  OrdinalPosition:           5

                  Required:                  False
                  SourceField:               QTY
                  SourceTable:               x


         STORE                                                 Integer                                  2
                  AllowZeroLength:           False
                  Attributes:                Fixed Size

                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {D302A983-DC15-4997-B395-A27CA7692C06}}

                  OrdinalPosition:           6
                  Required:                  False
                  SourceField:               STORE
                  SourceTable:               x


         SKU_STAT_CD                                           Integer                                  2
                  AllowZeroLength:           False

                  Attributes:                Fixed Size
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {BD971E8F-8470-4C76-8DEA-FC9AB870488E}}
                  OrdinalPosition:           7
                  Required:                  False
                  SourceField:               SKU_STAT_CD

                  SourceTable:               x

         VENDOR                                                Text                                    12

                  AllowZeroLength:           False
                  Attributes:                Variable Length
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {992B9E6C-FD61-4E5E-9B55-0789C3476B24}}
                  OrdinalPosition:           8
                  Required:                  False

                  SourceField:               VENDOR
                  SourceTable:               x

         ORD_CD                                                Text                                    36

                  AllowZeroLength:           False
                  Attributes:                Variable Length
                  CollatingOrder:            General

                  DataUpdatable:             False
                  GUID:                      {guid {5FCFC287-6B12-4C4D-A78B-D51EB6BCB253}}
                  OrdinalPosition:           9

                  Required:                  False
                  SourceField:               ORD_CD
                  SourceTable:               x


         PRIORITYFLG                                           Text                                    36

                  AllowZeroLength:           False
                  Attributes:                Variable Length
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {373E3CCB-A3B9-4929-9948-51C3943C16AD}}
                  OrdinalPosition:           10
                  Required:                  False

                  SourceField:               PRIORITYFLG
                  SourceTable:               x

         PROCESSING_FLG                                        Integer                                  2

                  AllowZeroLength:           False
                  Attributes:                Fixed Size
                  CollatingOrder:            General

                  DataUpdatable:             False
                  GUID:                      {guid {000C4ED5-117F-4305-A0FC-7AAA1FA83266}}
                  OrdinalPosition:           11

                  Required:                  False
                  SourceField:               PROCESSING_FLG
                  SourceTable:               x


         TIMESTAMP                                             Date/Time                                8
                  AllowZeroLength:           False
                  Attributes:                Fixed Size

                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {A849DBA5-D2C2-4118-B599-73B113DD5D2D}}
                  OrdinalPosition:           12

                  Required:                  False
                  SourceField:               TIMESTAMP
                  SourceTable:               x


         TYPE_DESC                                             Text                                   108
                  AllowZeroLength:           False

                  Attributes:                Variable Length
                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {20F2468D-65C1-4A91-82C9-D09F983F36E8}}

                  OrdinalPosition:           13
                  Required:                  False
                  SourceField:               TYPE_DESC

                  SourceTable:               x

         Proc Flag                                             Text                                   108
                  AllowZeroLength:           False

                  Attributes:                Variable Length
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {3C35D1D0-1378-4116-9224-08553874C2AB}}
                  OrdinalPosition:           14
                  Required:                  False

                  SourceField:               Proc Flag
                  SourceTable:               x

         Ticketnum                                             Double                                   8

                  AllowZeroLength:           False
                  Attributes:                Variable Length
                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {7FD66BCB-0356-4169-BC7E-2F874BDB7803}}

                  OrdinalPosition:           15
                  Required:                  False
                  SourceField:               Ticketnum

                  SourceTable:               x

User Permissions

         admin                      Delete, Read Permissions, Set Permissions, Change Owner, Read
                                    Definition, Write Definition, Read Data, Insert Data, Update Data,

Group Permissions

         Admins                     Delete, Read Permissions, Set Permissions, Change Owner, Read
                                    Definition, Write Definition, Read Data, Insert Data, Update Data,
         Users                      Delete, Read Permissions, Set Permissions, Change Owner, Read
                                    Definition, Write Definition, Read Data, Insert Data, Update Data,
-----------------------------------------------------------------------------------------------------------------------
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 4

Author Comment

by:orbitus
ID: 16992426

Table: FSCL_YR_WK                                                                                    Page: 1
----------------------------------------------------------------------------------------------------------------
Columns

         Name                                                  Type                        Size
         FSCL_YR                                               Integer                                  2

                  AllowZeroLength:           False
                  Attributes:                Fixed Size
                  CollatingOrder:            General

                  DataUpdatable:             False
                  GUID:                      {guid {AA33B103-C78C-4D09-997D-F0734F4A8C45}}
                  OrdinalPosition:           1

                  Required:                  False
                  SourceField:               FSCL_YR
                  SourceTable:               FSCL_YR_WK


         FSCL_YR_WK_NBR                                        Integer                                  2
                  AllowZeroLength:           False
                  Attributes:                Fixed Size

                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {48C975AB-5486-4549-952F-DB7D0B4509BB}}
                  OrdinalPosition:           2

                  Required:                  False
                  SourceField:               FSCL_YR_WK_NBR
                  SourceTable:               FSCL_YR_WK


         FSCL_PRD_NBR                                          Integer                                  2
                  AllowZeroLength:           False

                  Attributes:                Fixed Size
                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {E383994C-FE66-4E12-A935-ADB7C45922B2}}

                  OrdinalPosition:           3
                  Required:                  False
                  SourceField:               FSCL_PRD_NBR

                  SourceTable:               FSCL_YR_WK

         WK_BGN_DT                                             Date/Time                                8
                  AllowZeroLength:           False

                  Attributes:                Fixed Size
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {F7D2A067-931A-4ACD-9983-DABEC44EB407}}
                  OrdinalPosition:           4
                  Required:                  False
                  SourceField:               WK_BGN_DT

                  SourceTable:               FSCL_YR_WK

         WK_END_DT                                             Date/Time                                8

                  AllowZeroLength:           False
                  Attributes:                Fixed Size

                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {B2D871E1-6965-4A01-8947-50CFF0AF90ED}}
                  OrdinalPosition:           5

                  Required:                  False
                  SourceField:               WK_END_DT
                  SourceTable:               FSCL_YR_WK


         FSCL_MTH_WK_NBR                                       Integer                                  2
                  AllowZeroLength:           False
                  Attributes:                Fixed Size

                  CollatingOrder:            General
                  DataUpdatable:             False
                  GUID:                      {guid {BC6FAC8D-6998-40E8-9761-B6253B1F0389}}

                  OrdinalPosition:           6
                  Required:                  False
                  SourceField:               FSCL_MTH_WK_NBR
                  SourceTable:               FSCL_YR_WK


         FSCL_YR_WK_KEY_VAL                                    Text                                     6
                  AllowZeroLength:           False

                  Attributes:                Variable Length
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {303CEAAF-E4E0-400A-825C-E01475ED6177}}
                  OrdinalPosition:           7
                  Required:                  False
                  SourceField:               FSCL_YR_WK_KEY_VAL

                  SourceTable:               FSCL_YR_WK

         FSCL_PRD_KEY_VAL                                      Text                                     6

                  AllowZeroLength:           False
                  Attributes:                Variable Length
                  CollatingOrder:            General
                  DataUpdatable:             False

                  GUID:                      {guid {DDB9CD7B-8787-4B49-8D56-CDCE3B01EB30}}
                  OrdinalPosition:           8
                  Required:                  False

                  SourceField:               FSCL_PRD_KEY_VAL
                  SourceTable:               FSCL_YR_WK





User Permissions

         admin                      Delete, Read Permissions, Set Permissions, Change Owner, Read
                                    Definition, Write Definition, Read Data, Insert Data, Update Data,




Group Permissions

         Admins                     Delete, Read Permissions, Set Permissions, Change Owner, Read
                                    Definition, Write Definition, Read Data, Insert Data, Update Data,
         Users                      Delete, Read Permissions, Set Permissions, Change Owner, Read


Table: FSCL_YR_WK                                                                                    Page: 3

                                    Definition, Write Definition, Read Data, Insert Data, Update Data,
                                    Delete Data
---------------------------------------------------------------------------------------------------------------------------
0
 
LVL 4

Author Comment

by:orbitus
ID: 16992456
The reason for the unrelated table FSCL_YR_WK, I was given a query by a senior analyst as to how to link the FSCL_YR_WK table to the crosstab that I was working to get the actual company fiscal week.

Chris
0
 
LVL 58

Expert Comment

by:harfang
ID: 16993926
LOL, Chris. I was expecting something like this:

         Table x
       --------------------
         RCDR, Text(3)
         TICKET, Text(12)
         CreatedDate, Date/Time
         SKU_NBR, Long
         QTY, Integer
         STORE , Integer
         SKU_STAT_CD, Integer
         VENDOR, Text(12)
         ORD_CD, Text(36)
         PRIORITYFLG, Text(36)
         PROCESSING_FLG, Integer
         TIMESTAMP, Date/Time
         TYPE_DESC, Text(108)
         Proc Flag, Text(108)
         Ticketnum, Double

         Table: FSCL_YR_WK
       ----------------------------
         FSCL_YR, Integer
         FSCL_YR_WK_NBR, Integer
         FSCL_PRD_NBR, Integer
         WK_BGN_DT, Date/Time
         WK_END_DT, Date/Time
         FSCL_MTH_WK_NBR, Integer
         FSCL_YR_WK_KEY_VAL, Text(6)
         FSCL_PRD_KEY_VAL, Text(6)

And the comment from your last post, naturally.

This is going to be easy. The senior analyst gave you a solution probably often used on older large databases. In Access, it will be much easier to create either a function or a simple expression to calculate the fiscal week number.

To that effect, can you tell me what you know about the definition of fiscal weeks you are using, and perhaps some sample data from FSCL_YR_WK. Not the entire table, but some records, possibly the last two of the previous fiscal year and the first two of this one...

Mathematically, TIMESTAMP \ 7, or perhaps (TIMESTAMP + n) \ 7 will give a week number, sufficient for the purpose of the cross-tab query. You would need to convert that self-made week number to your fiscal week, but that can be done later. This is just one example, not necessarily the best solution.

Cheers!
(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 16995841
FSCL_YR      FSCL_YR_WK_NBR      FSCL_PRD_NBR      WK_BGN_DT      WK_END_DT      FSCL_MTH_WK_NBR      FSCL_YR_WK_KEY_VAL      FSCL_PRD_KEY_VAL
2005      51      12      01/16/2006      01/22/2006      4      200551      200512
2005      52      12      01/23/2006      01/29/2006      5      200552      200512
2006      1      1      01/30/2006      02/05/2006      1      200601      200601
2006      2      1      02/06/2006      02/12/2006      2      200602      200601
0
 
LVL 58

Expert Comment

by:harfang
ID: 16996522
Chris,

I understand your senior analyst much better now. Your company is using non-standard fiscal periods. The only thing I could guess from your data is that the fiscal week is Monday-Sunday, european fashion, and that the fiscal year starts *around* the 1st of Feburay. The problem is with the *around*.

The second problem is that the 53th week fraction is appended to the next year. I would need a very precise rule to create an expression for that, and it's not going to be easy afterall.

I was thinking about something like this:


Function FiscalWeek(pvarDate)

    Const cintStartMonth As Integer = 2
    Const cintWeekStarts = vbMonday
   
    Dim datYearStart As Date
   
    If Not IsDate(pvarDate) Then
        FiscalWeek = Null
        Exit Function
    End If
   
    datYearStart = DateAdd("m" _
        , -((Month(pvarDate) + 12 - cintStartMonth) Mod 12) _
        , pvarDate - Day(pvarDate) + 1)
    FiscalWeek = DateDiff("ww" _
        , datYearStart _
        , pvarDate _
        , cintWeekStarts _
        ) + 1

End Function


As it stands, we might need your table after all. In which case, the function would be:


Private Function FSCL_YR_WK(LookupDate, pstrField As String)

    Static srecWeeks As DAO.Recordset

    FSCL_YR_WK = Null
    If Not IsDate(LookupDate) Then Exit Function
    If srecWeeks Is Nothing Then
        Set srecWeeks = CurrentDb.OpenRecordset("FSCL_YR_WK", dbOpenTable)
        srecWeeks.Index = "WK_BGN_DT"
    End If
    With srecWeeks
        .Seek "<=", LookupDate
        If .NoMatch Then Exit Function
        If LookupDate > !WK_END_DT Then Exit Function
        FSCL_YR_WK = .Fields(pstrField)
    End With
   
End Function

Public Function FiscalWeek(LookupDate)
    FiscalWeek = FSCL_YR_WK(LookupDate, "FSCL_YR_WK_NBR")
End Function


Won't be as fast, and I don't know how it will react with our crosstab, but it's worth a try. Else, find the exact rule for calculating the fiscal week number...

Cheers!
(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 17000301
WOW THAT FUNCTION IS SOO COOL!!  It made the crosstab work so much faster its not even funny!

I even tried it with the report and it came up and worked, although the totals at the bottom all gave #Error but the rest of it worked like a charm.

Chris
0
 
LVL 4

Author Comment

by:orbitus
ID: 17000309
oh by the way I used the second set of functions as I wanted to be sure to use the official company fiscal tables.

Chris
0
 
LVL 4

Author Comment

by:orbitus
ID: 17000382
Also though , the blank columns are not showing up(by blank I mean the ones that have no data available), or is that a lot more difficult?

Chris
0
 
LVL 4

Author Comment

by:orbitus
ID: 17000437
With the error I mentioned previously, I have the total in the report footer and getting a sum error.

Chris
0
 
LVL 4

Author Comment

by:orbitus
ID: 17001640
Sorry for all the responces as each one sends and email, actually I just notices I have the sums in the page footer. I got it working fine in the report footer.
0
 
LVL 58

Expert Comment

by:harfang
ID: 17001692
orbitus,

I'm glad the function works in your setup and that you like it. It's an important technique for "range lookups", as this need is not integrated in the SQL language per se.

I suspect the reason why the sums do not work is because the report regenerates a total query based on the record source of the report. In other words, this is a total query based on a cross-tab, which is rather nonsensical. If you go back to the code in {http:#16976982}, you can make this change:

        Me("txtData" & intCol).ControlSource = recTotals!PrcFlg   ' <--- unchanged
        Me("txtSum" & intCol).Value = recTotals!Tickets   ' <--- use that total instead

If your queries (qryTestData and qryTestTotals) are well synchronized, this will produce the desired output.

As for the missing columns, you probably mean columns for processing flags not yet used. You can add them, but it's again rather complex.

For a cross-tab, you can use PIVOT <expr> IN('Col1','Col2',...,'ColN') to hard-code all the columns you want, but the goal was to have dynamic columns.

You can also use the table where the processing flags are defined and use a LEFT JOIN to make sure you have them all, but the WHERE clause then interferes. For example if the flag X is used in records later filtered out by the WHERE, that column will still be missing. This means you need a subquery for the WHERE clause so that the LEFT JOIN from the main query will work as expected...

In your case, I beleive your best bet is to add them through code, using the recordset recLabels, possibly in conjuction with an array or a collection to find the missing ones.

Good luck!
(°v°)
0
 
LVL 4

Author Comment

by:orbitus
ID: 17001737
That's cool, really its not worth going through all the trouble trying to get blank fields in there.  I cant thank you enough for all your help.  You are truly amazing!

Thanks,

Chris
0
 
LVL 58

Expert Comment

by:harfang
ID: 17001897
Thank you for your kind words, and success with your project!
(°v°)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now