Limiting Number of Columns Showing in Pivot Table

[lemo]

hello.
hope all are doing well, i haven't been here for a while.

question -

is it possible to limit the number of values that pivot table is displaying? i have years in columns, from 2002 to current, but every new year my table expands to the right. instead, whenever we roll into a new year, i'd like it to take away the oldest one, so the width always stays the same. sort of like showing top 10 years.

using excel 2007.

thanks in advance,
l

 

[Trevor G]

How to get the Pivot Source does it come from an Excel Table?

 

[lemo]

the source is external data, from Access query.

 

[Trevor G]

If you have permission then set criteria in the Query and the date field to have something like this, which will restrict to the last 10 years only.

Year(DateAdd("yyyy",-10,Date()))

 

[lemo]

aha, i think i see where you are going with this.
except.. can this be part of the SQL 'WHERE' clause?..
nevermind, just checked, it can! (it looked like it belongs in VBA realm, which i am not too good at on the Access side)

however - there is one issue. actually two issues.

in my Excel file, i summarize data by both Calendar Year and Fiscal Year (starts on July 1st). correct me if i am wrong, but there is no way i can make this work with one query. if i restrict calendar year, my fiscal year will be half a year behind, and vice versa.. but i can live with two separate queries, so this is still a viable solution.

the bigger problem is that i just tried to use your formula for the fiscal year, and the query takes prohibitively long time to run (it's perfectly fine if applied to calendar year).
i calculate both calendar and fiscal years with the following formulas, respectively (don't ask about 'Date' variable, i know, but can't change it now) -
Year([Date]) AS [Year]
IIf(Month([Date])>=7,Year([Date])+1,Year([Date])) AS FiscYr

any idea what's happening here?

thanks much for your help.
len

 

[Trevor G]

How long does it take to work? I would use Date rather than Now personally as Now indicates you are using time in the date as well and sometimes will give you the wrong amount as data.

 

[lemo]

a few minutes. maybe 3-5. the bar goes quickly almost to the end, but then gets stuck there, with not even a millimeter to go.
i used Date(), not Now, i was just complaining that one of my variable names is 'Date', which is a big no-no, according to this forum.

 

[Trevor G]

In your code do you have any applications commands to stop things like warnings and updating so it runs quicker.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

'then you code
'then put these back on

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

 

[lemo]

i don't have any code in the excel file. nor in access where the data are pulled from. (ok, i do have some code in the excel file, but it's exclusively for formatting, just a couple of lines).
it's the access query that's too slow.
interestingly, i tried to run it today, and it seems a little better, finished under a minute. it's still unacceptable, because i don't want end users to wait that long, but maybe this kind of behavior indicates something?

 

[Trevor G]

Are you able to upload an extract of both spreadsheet and database to see if I can help further.

 

[lemo]

probably not, excel file is rather big, over 7 megs, and forget about the database. i am attaching the pic of the file, and access query code is below, not sure it will help you, but just in case.
thanks for your time and effort, i really appreciate.
l

SELECT A1.Boro, A1.District, A1.PropNum, A1.[Prop ID], StrConv([A1].[Prop Name],3) AS [Prop Name], StrConv([A1].[Site Name],3) AS [Site Name], A1.ZipCode, A1.Acres, Insp1.Date, Insp1.[Inspection Year], Insp1.Season, Insp1.Round, Insp1.Inspector, Insp1.[Overall Condition], Insp1.Cleanliness, IIf([Insp1]![Overall Condition]="a",1,IIf([Insp1]![Overall Condition]="u",0,Null)) AS OC, IIf([Insp1]![Cleanliness]="a",1,IIf([Insp1]![Cleanliness]="u",0,Null)) AS C, Insp1.[Unacceptable Features], A1.Category, A1.[Sub-Category], IIf(([A1]![district] & [A1]![propnum] & [A1]![category])=([tempLPName_Dist]![district1] & [tempLPName_Dist]![propnum1] & [tempLPName_Dist]![category1]),StrConv([tempLPName_Dist]![PropName1],3),IIf([A1]![category]="Large Park",StrConv([A1]![prop name],3),IIf([A1]![Site Name]=[A1]![Prop Name],StrConv([A1]![Prop Name],3) & " (" & [A1]![Prop ID] & ")",StrConv([A1]![Site Name],3) & " (" & StrConv([A1]![Prop Name],3) & ", " & [A1]![Prop ID] & ")"))) AS [SP, GS Name, ID], Year([Date]) AS [Year], IIf(Month([Date])>=7,Year([Date])+1,Year([Date])) AS [Fisc Yr], [Inspection Year] & "-" & [Season] AS [Yr-Seas], Format([Date],"mmm") AS Months, [borough] & [district] AS BoDi, Insp1.[Inspection ID], A1.Rated

FROM (A1 INNER JOIN Insp1 ON A1.[Prop ID]=Insp1.[Prop ID]) LEFT JOIN tempLPName_Dist ON (A1.PropNum=tempLPName_Dist.PropNum1) AND (A1.Boro=tempLPName_Dist.Boro1) AND (A1.District=tempLPName_Dist.District1) AND (A1.Category=tempLPName_Dist.Category1)

WHERE (((Insp1.Date) Between #7/1/2002# And (Date()-1)) AND ((Insp1.Round)<>0) AND ((A1.Category)="greenstreet" Or (A1.Category)="large park" Or (A1.Category)="small park") AND ((Insp1.[Mock Inspection?])=0) AND ((Insp1.InspectionType)="pip"))

ORDER BY A1.Boro, A1.District, A1.PropNum, A1.[Prop ID], Insp1.Date;