Crosstab headings 1

[Andrea001]

I have many crosstab queries with different criteria however they all have the same date column headings. I'm working on a rolling year so the mmm-yy format is what I'm using. The problem is every time I update the data I have to manually go into each crosstab and change the properties for the column headings. Is there any way to automate this process? Any help would be appreciated.
Thanks,
Andrea

 

[GoDawgs]

It sounds like you are right-clicking on the column and setting the format...I would have the column header set as something like this instead of doing that:

format([Datefield],"mmm-yy&quot

Hopefully that helps...but I may have misunderstood the question.

Kevin

 

[Andrea001]

Thanks Kevin,

I don't think it's the format I'm having trouble with. Here's the SQL for the query ...

TRANSFORM Sum(CurrentDataQuery.QTY) AS SumOfQTY
SELECT CurrentDataQuery.[REFDR NAME], Sum(CurrentDataQuery.QTY) AS [Total Of QTY]
FROM CurrentDataQuery
WHERE (((CurrentDataQuery.SiteID)=1))
GROUP BY CurrentDataQuery.[REFDR NAME], CurrentDataQuery.SiteID
PIVOT Format([DATE],"mmm-yy&quot In ("Oct-02","Nov-02","Dec-02","Jan-03","Feb-03","Mar-03","Apr-03","May-03","Jun-03","Jul-03","Aug-03","Sep-03"

It's the last section that I would like to update for all of my crosstabs with a macro or something so that I'm not manually going into each query and changing the headings so that they all begin with Nov-02 and end with Oct-02.

 

[GoDawgs]

Oh, I gotcha, I was a bit confused. Hmmm...if this change is occuring often and to multiple queries then I think I'd set up a table that is just a list of everything you have in the "In" statement. Then you could just check against that...and more importantly you would only have to update this table whenever changes need to be made. Hope that helps.

Kevin

 

[Andrea001]

I do update the table every month just to contain the date range I'm looking for. Unfortunately the In statement of my queries does not automatically update because I'm not using the standard year but starting in the middle of one year and going into the middle of the next, it just leaves the first column (Oct-02) blank and does not show the new records in the query for the new month (Oct-03). This seems like it should be easy ... I just can't seem to fix it!
Andrea

 

[GoDawgs]

Set up a table that lists out all 12 of the months that show up in the IN statement...so it would look like this:

MonthName

Oct-02
Nov-02
Dec-02
Jan-03
Feb-03
Mar-03
Apr-03
May-03
Jun-03
Jul-03
Aug-03
Sep-03

Then you could either link directly to this (using the format function first) or could put a query of this table in the IN statement (something like this "...IN (Select * From ThisTable)&quot. Hope that helps.

 

[dhookom]

Use "relative" column headings so they never change. Assuming a form and text box for the ending date of your report Forms!frmDate!txtEndDate. You set up your column headings expression to
ColHead: "Mth" & DateDiff("M",[Date],Forms!frmDate!txtEndDate)
Then set your column headings property to:
"Mth0","Mth1","Mth2",..."Mth11"
Mth0 values are from the date on the form and Mth11 is 11 months previous.
Then in the report, use text boxes as column labels. Use control sources of:
=DateAdd("m",0,Forms!frmDate!txtEndDate)
=DateAdd("m",-1,Forms!frmDate!txtEndDate)
=DateAdd("m",-2,Forms!frmDate!txtEndDate)
etc.


Duane
MS Access MVP

 

[MichaelRed]

A different approach? Generate the list of 'dates' (programatically) in another recordset and outter join this to the base query on the date field. Use the added recordset field in place of your current date field in the transform (XTab) query. The new recordset may be generated from many processes, however I would sugest a relatively static table with a simple paraeter query to select the date 'range' from between the current month and hte current month + 11, so there is a simgle parameter prompt for each use.

I generated a trivial (an incomplete) procedure to add (fully formed) month dates to a table:

Public Function basMkMnths(Nmnths As Integer) As Boolean

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Dim CurDt As Date
    Dim Idx As Integer
    Dim strSql As String

    Set dbs = CurrentDb

    strSql = "Select Max(MnthDt) as Mnth from tblMnths;"
    Set rst = dbs.OpenRecordset(strSql)
    CurDt = rst!Mnth
    
    Set rst = dbs.OpenRecordset("tblMnths", dbOpenDynaset)

    Idx = 1
    While Idx <= Nmnths
        With rst
            .AddNew
                CurDt = DateAdd(&quot;m&quot;, 1, CurDt)
                rst!MnthDt = CurDt
            .Update
        End With

        Idx = Idx + 1
    Wend

End Function

I also generated a query:

SELECT Format([MnthDt],"mmm-yy") AS MyMnth
FROM tblMnths
WHERE (((tblMnths.MnthDt) Between [MyDate] And DateAdd("m",11,[MyDate])))
WITH OWNERACCESS OPTION;
[code]

which -with the single parameter prompt, returns the list of months-years.

Including the query as the outter join instead of your subquery, and using it as the Pivioy field should let you enter the current month's date (first of the month) as the single simple parameter for your XTabs.  The only 'additional' effort woulf be to apply the procedure to the table periodically, so that it always contains a sufficient range of months.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Andrea001]

Thank you all for your help!! I just love this site. I ended up using a table and linking to it like Kevin suggested. Works great, now I just need to modify the table rather than all the queries!
Thanks again,
Andrea