Grouping In Pivot Tables #3

Hi

I have a pivot table linked direct to an access query. In the pivot table I 
have grouped the date field by year & month and set the start date as 1 Apr 
2005 and end date as 31 Mar 2006. Some of the months have no data so I set 
the field settings to 'include items with no data' however, this then shows 
blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them out 
it also hides Jan, Feb & Mar 2006.

How can I get it to only show from Apr 05 to Mar 06 and include fields where 
there was no data between those dates?

Many Thanks

GLS
-- 
GLS
0
GLS (4)
4/12/2006 2:31:02 PM
excel 39879 articles. 2 followers. Follow

5 Replies
487 Views

Similar Articles

[PageSpeed] 55

Uncheck "include items with no data", the blank data will still be there in 
the included months


-- 

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"GLS" <GLS@hotmail.com> wrote in message 
news:72E250BB-E84E-4CE6-AF8F-F2E22F86FD62@microsoft.com...
> Hi
>
> I have a pivot table linked direct to an access query. In the pivot table 
> I
> have grouped the date field by year & month and set the start date as 1 
> Apr
> 2005 and end date as 31 Mar 2006. Some of the months have no data so I set
> the field settings to 'include items with no data' however, this then 
> shows
> blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them 
> out
> it also hides Jan, Feb & Mar 2006.
>
> How can I get it to only show from Apr 05 to Mar 06 and include fields 
> where
> there was no data between those dates?
>
> Many Thanks
>
> GLS
> -- 
> GLS 


0
Peo
4/12/2006 4:05:45 PM
Thanks Peo, but that is how I originally had it when it wouldnt show blank 
fields.  Could it be because I link to the query using the 'External Data 
Source' function rather than copying the data into Excell?  I do this so I 
can simply refersh the pivot each month to upate it.

Thanks
-- 
GLS


"Peo Sjoblom" wrote:

> Uncheck "include items with no data", the blank data will still be there in 
> the included months
> 
> 
> -- 
> 
> Regards,
> 
> Peo Sjoblom
> 
> http://nwexcelsolutions.com
> 
> 
> 
> "GLS" <GLS@hotmail.com> wrote in message 
> news:72E250BB-E84E-4CE6-AF8F-F2E22F86FD62@microsoft.com...
> > Hi
> >
> > I have a pivot table linked direct to an access query. In the pivot table 
> > I
> > have grouped the date field by year & month and set the start date as 1 
> > Apr
> > 2005 and end date as 31 Mar 2006. Some of the months have no data so I set
> > the field settings to 'include items with no data' however, this then 
> > shows
> > blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them 
> > out
> > it also hides Jan, Feb & Mar 2006.
> >
> > How can I get it to only show from Apr 05 to Mar 06 and include fields 
> > where
> > there was no data between those dates?
> >
> > Many Thanks
> >
> > GLS
> > -- 
> > GLS 
> 
> 
> 
0
GLS (4)
4/13/2006 8:53:02 AM
Hi there,

in similar cases, i could create a new field (in the mdb file already), 
called YePe (Year & period), for 200501-200504 - 200505 - ....200604, then 
you can de-select those periods that you don't need.

Any good?
Regards,
ANdras
(Hungary)

"GLS" wrote:

> Thanks Peo, but that is how I originally had it when it wouldnt show blank 
> fields.  Could it be because I link to the query using the 'External Data 
> Source' function rather than copying the data into Excell?  I do this so I 
> can simply refersh the pivot each month to upate it.
> 
> Thanks
> -- 
> GLS
> 
> 
> "Peo Sjoblom" wrote:
> 
> > Uncheck "include items with no data", the blank data will still be there in 
> > the included months
> > 
> > 
> > -- 
> > 
> > Regards,
> > 
> > Peo Sjoblom
> > 
> > http://nwexcelsolutions.com
> > 
> > 
> > 
> > "GLS" <GLS@hotmail.com> wrote in message 
> > news:72E250BB-E84E-4CE6-AF8F-F2E22F86FD62@microsoft.com...
> > > Hi
> > >
> > > I have a pivot table linked direct to an access query. In the pivot table 
> > > I
> > > have grouped the date field by year & month and set the start date as 1 
> > > Apr
> > > 2005 and end date as 31 Mar 2006. Some of the months have no data so I set
> > > the field settings to 'include items with no data' however, this then 
> > > shows
> > > blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them 
> > > out
> > > it also hides Jan, Feb & Mar 2006.
> > >
> > > How can I get it to only show from Apr 05 to Mar 06 and include fields 
> > > where
> > > there was no data between those dates?
> > >
> > > Many Thanks
> > >
> > > GLS
> > > -- 
> > > GLS 
> > 
> > 
> > 
0
ANdras (21)
4/13/2006 7:28:02 PM
Thanks ANdras

As I'll be re-creating the tables for the new financial year I will 
incorporate that into ithe query.
-- 
GLS


"ANdras" wrote:

> Hi there,
> 
> in similar cases, i could create a new field (in the mdb file already), 
> called YePe (Year & period), for 200501-200504 - 200505 - ....200604, then 
> you can de-select those periods that you don't need.
> 
> Any good?
> Regards,
> ANdras
> (Hungary)
> 
> "GLS" wrote:
> 
> > Thanks Peo, but that is how I originally had it when it wouldnt show blank 
> > fields.  Could it be because I link to the query using the 'External Data 
> > Source' function rather than copying the data into Excell?  I do this so I 
> > can simply refersh the pivot each month to upate it.
> > 
> > Thanks
> > -- 
> > GLS
> > 
> > 
> > "Peo Sjoblom" wrote:
> > 
> > > Uncheck "include items with no data", the blank data will still be there in 
> > > the included months
> > > 
> > > 
> > > -- 
> > > 
> > > Regards,
> > > 
> > > Peo Sjoblom
> > > 
> > > http://nwexcelsolutions.com
> > > 
> > > 
> > > 
> > > "GLS" <GLS@hotmail.com> wrote in message 
> > > news:72E250BB-E84E-4CE6-AF8F-F2E22F86FD62@microsoft.com...
> > > > Hi
> > > >
> > > > I have a pivot table linked direct to an access query. In the pivot table 
> > > > I
> > > > have grouped the date field by year & month and set the start date as 1 
> > > > Apr
> > > > 2005 and end date as 31 Mar 2006. Some of the months have no data so I set
> > > > the field settings to 'include items with no data' however, this then 
> > > > shows
> > > > blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them 
> > > > out
> > > > it also hides Jan, Feb & Mar 2006.
> > > >
> > > > How can I get it to only show from Apr 05 to Mar 06 and include fields 
> > > > where
> > > > there was no data between those dates?
> > > >
> > > > Many Thanks
> > > >
> > > > GLS
> > > > -- 
> > > > GLS 
> > > 
> > > 
> > > 
0
GLS (4)
4/13/2006 8:12:02 PM
Hi GLS,

i assume, from access & pivot point, you could add this calculating field at 
any time, with a function
like YePe: iif([month]>9;[year]&[month];[year]&"0"&[month]) then add to the 
pivot 

Best regards,
ANdras

"GLS" wrote:

> Thanks ANdras
> 
> As I'll be re-creating the tables for the new financial year I will 
> incorporate that into ithe query.
> -- 
> GLS
> 
> 
> "ANdras" wrote:
> 
> > Hi there,
> > 
> > in similar cases, i could create a new field (in the mdb file already), 
> > called YePe (Year & period), for 200501-200504 - 200505 - ....200604, then 
> > you can de-select those periods that you don't need.
> > 
> > Any good?
> > Regards,
> > ANdras
> > (Hungary)
> > 
> > "GLS" wrote:
> > 
> > > Thanks Peo, but that is how I originally had it when it wouldnt show blank 
> > > fields.  Could it be because I link to the query using the 'External Data 
> > > Source' function rather than copying the data into Excell?  I do this so I 
> > > can simply refersh the pivot each month to upate it.
> > > 
> > > Thanks
> > > -- 
> > > GLS
> > > 
> > > 
> > > "Peo Sjoblom" wrote:
> > > 
> > > > Uncheck "include items with no data", the blank data will still be there in 
> > > > the included months
> > > > 
> > > > 
> > > > -- 
> > > > 
> > > > Regards,
> > > > 
> > > > Peo Sjoblom
> > > > 
> > > > http://nwexcelsolutions.com
> > > > 
> > > > 
> > > > 
> > > > "GLS" <GLS@hotmail.com> wrote in message 
> > > > news:72E250BB-E84E-4CE6-AF8F-F2E22F86FD62@microsoft.com...
> > > > > Hi
> > > > >
> > > > > I have a pivot table linked direct to an access query. In the pivot table 
> > > > > I
> > > > > have grouped the date field by year & month and set the start date as 1 
> > > > > Apr
> > > > > 2005 and end date as 31 Mar 2006. Some of the months have no data so I set
> > > > > the field settings to 'include items with no data' however, this then 
> > > > > shows
> > > > > blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them 
> > > > > out
> > > > > it also hides Jan, Feb & Mar 2006.
> > > > >
> > > > > How can I get it to only show from Apr 05 to Mar 06 and include fields 
> > > > > where
> > > > > there was no data between those dates?
> > > > >
> > > > > Many Thanks
> > > > >
> > > > > GLS
> > > > > -- 
> > > > > GLS 
> > > > 
> > > > 
> > > > 
0
ANdras (21)
4/13/2006 8:18:02 PM
Reply:

Similar Artilces:

Outlook Rules Wizard #3
Have a question regarding e-mail receipts. I have applied rules wizard to screen unwanted e-mails. I have entered the word, such as "SPAM", to be screened. If found in the subject or body of the e-mail, it is to be deleted from the ISP server before it is ever downloaded to my computer. Occassionally I will get the message, "You don't have appropriate permission to perform this operation", and the message will get through. I will then go in rules wizard and my rule box will be unchecked. I will have to re-check it to re- activate the rule. How can I make...

MATCH formula #3
Thanks. I'll try out those suggestions. De -- da ----------------------------------------------------------------------- dah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=649 View this thread: http://www.excelforum.com/showthread.php?threadid=27191 ...

ADC ... and Distribution Groups/PF's
Hi Everyone, Ok, I'm done with Step 4 and I'm a bit confused. Now I see the Exchange related info on the users. I don't see any Distribution Groups nor Public Folder information in AD. When would I start seeing them? Have you created Public folder agreement. "Jan K. van Dalen" wrote: > Hi Everyone, > > Ok, I'm done with Step 4 and I'm a bit confused. > > Now I see the Exchange related info on the users. > > I don't see any Distribution Groups nor Public Folder information in AD. > When would I start seeing them? > &g...

Pivot Table Is Not in Order
I have been given a pivot table that already had six years of data in it, and was told to add four more years data to it. I went into the source document and added everything that needed to be added in the correct places, and then refreshed the pivot table. The pivot table added all of the new data correctly except for one thing - it isn't in order. The data for years 1997 - 2003 are fine, but then the years 2004 - 2007 show up out of order (2005, 2006, 2004, 2007). Being a beginner at pivot tables still, I just can't figure this out. Has anyone ever had this problem before and c...

Creating a registration table list based on an existing table
Hi, I have an access database that contains transactional tables which I cannot alter. There is a table called invoice. This has ClientId, ClientName and ItemNumber. The ClientID is a ten digit number while the ClientName is a business name. There are no extra fields that I can use in it. I want to create a form that will enable the staff to keep track of registration. A registration is initiated when an invoice on a client is created with the itemnumber being College. Right now, people are keeping track of attendees using a spreadsheet but I want to create a table that contain...

help populating appointment times in table
I will try to explain as simply as I can. I have a database that keeps client records and creates appointment dates and times after update of a provider name combobox by running a macro with several queries. Now here is where I don't know what I am doing. I have it working perfectly providing someone has not used the 9:00 spot in the appointment book because I have it defaulting to 9am. How do I add code to have the default change to a different time for any record where the 9:00 spot is already used? I am not very good with the code writing though I can manage to add a snippet in...

Internet Explorer 6.0 Sp1 Component Update 3.0 for Windows 98
For those of you that want to use various Win-2K updates from Microsoft and apply them to your Win-98 system, this update package has been designed to bring together all those various updates and install them automatically. MD InternetExplorer 6.0sp1 Component Update 3.0 *Windows Script Update 5.6 *971961 - Unofficial JScript Security Update *944338 - Unofficial Windows Script Security Update *973354 - Unofficial Outloook Express Cumulative Security Update *976325 - Unofficial Internet Explorer Cumulative Security Update (with uninstall) *905495 - Unofficial Security Update (MSI...

How do I Look up an Array/Table name
I have an Excel spreadsheet that uses a LOOKUP (array) function. I have since made added rows to the array, or range of data, that is referenced by the LOOKUP function. So now I need to redefine the range but I can't remember how to find the range that is named in the formula. How do I find this range so I can change it? insert --> name --> define "dnamertz" <dnamertz@discussions.microsoft.com> ���g��l��s�D:9F494493-4666-4937-9006-CD3975FCC10B@microsoft.com... >I have an Excel spreadsheet that uses a LOOKUP (array) function. I have > since made added ...

POP 3 CONNECTOR #2
Hi Exchange 5.5, which was setup years back with POP3 connector cuz there was no internet on site. Now I want to setup MX record pointing to this server. Do I simply stop running/uninstall POP3 Connector service and make sure SMTP is running? Or there is more than that? TIA In news:102F1BFB-EEAD-48B4-A5A1-2E03A867B255@microsoft.com, Kelly <Kelly@discussions.microsoft.com> typed: > Hi > > Exchange 5.5, which was setup years back with POP3 connector cuz > there was no internet on site. Now I want to setup MX record pointing > to this server. Do I simply stop running/...

Exporting all forms record either in a table or Excel 2007.( Any m
Dear All, i have a form that contains 347 records.I want a macro that copy all the field from each record and generate a table in access and save all the fields in a row. i.e Record 1 all fields should be saved in Table first row then record 2 in second row an so on till All records saved. Moreover Is there any alternative option that either all records fields exported to excel file. Please help Rgds, Aligahk06 aligahk06 wrote: >Dear All, >i have a form that contains 347 records.I want a macro that copy all the >field from each record and generate a table...

3 format cell tabs hidden in excel dialog box
When I right click on the cell and go to format cells instead of seeing 6 tabs as usual I see three tabs and the other three are on a lower level and covered by the information in the dialog box. I can only see the very top edge of the tabs. Any ideas what could be causing this? If you're using Excel 97, and making copies of copies of worksheets, you may hit a limit, because of the way the sheets are being numbered in the background. If that's the case, try copying one of the first sheets, instead of the last one. pjbjamison wrote: > When I right click on the cell and g...

Rules Error #3
I keep getting this dialog box: Rules in Error Rule: Server-Requested Client Action Error: MoveCopy, Unable to create dest msg. I've checked my rules, and they all work. This only comes up when starting Outlook. What's going on here? ...

Excel database update #3
Hi, Have an excel template that should send data into a excel database. When I run a marco, the database does not automatically update, but if i do it step by step it updates. ("MatTer1.xls") when saved should update the database, by creating a new record in the code hereunder ActiveWindow.WindowState = xlMinimized Windows("CHAVES.xls").Activate ActiveWindow.WindowState = xlMaximized Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.WindowState = xlMinimized ...

Any grouping in the address book?
No. Contacts can be group in 3 ways: 1. Categories 2. Separate Contact Subfolders 3. Distribution Lists -- Russ Valentine [MVP-Outlook] "Leon Chan" <anonymous@discussions.microsoft.com> wrote in message news:35c701c52208$fdf016a0$a501280a@phx.gbl... > ...

Loading of data in a custom table
I have created a custom table for SIC (standard industrial codes). I am using this table via a 'lookup' for the account and contact entities. I want to load all SIC codes into the table. Can I load the codes into the table using a straight SQL statement, or do I really need to write a program using the SDK. This data will be loaded on a one-time basis and does not change? Will doing a load directly using SQL cause a problem later on? I know that Microsoft would say to only utilize the SDK to load the data, but why? The reason why you should not directly update the da...

Wrong amortization table displayed for mortgage.
I have two mortgages and I noticed that only one amortization table is displayed in Money 2003. Regardless of the 'account' selected only one mortgage amortization table is displayed, always the same one. I noticed this before but let it go. Since I refinanced there are now 3 mortgages in Money, yet they all display the same amortization table (for mortgage #2). I would like to see the amortization table on the new (or old) loan on my residence.... but it is NOWHERE to be found. You're talking in the reports? Have you checked Customize for an account choice? "Larr...

In HQ make works sheets formulas go cross tables..
It would be great to have more functionality in the HQ worksheet formula's if you add a supplier and they have a lot of items, but the costs are the same, a lot of time could be saved if you could copy the cost from the standard cost. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in...

Subtotal percents in pivot table
I have a pivot table based on a list of data that has the following columns: Budget Category, Line Item, Budget, YTD Spent, Balance, and % Spent. Budget Category and Line Item are my row items. Sums of Budget, YTD Spent, Balance, and % Spent are my data items. The % Spent is correct for each line item but then those are all subtotaled which gives an inaccurate %. Here is what I am getting: Budget Category Line Item Budget YTD Spent Balance % Spent Personnel Jim $30,000 $2,000 $28,000 6.67% Bob ...

Upgrading MS CRM 3.0 to MS CRM 4.0
We have an MS CRM 3.0 setup and running well for quite long time. We would like to upgrade it to MS CRM 4.0. Current setup is, 1. Lot of reports added & customized for user needs 2. Lot of entities added & customized for user needs 3. Lot of forms added & customized for user needs 4. Lot of custom developed pages added (developed in ASP.NET 1.1). Of course it's running on separate site but integrated inside the MS CRM 3.0 (so typically used MS CRM 3.0 service calls) 5. Lot of Stored Procedure's & View's added 6. Customized the settings based on user needs My do...

Dynamics Utilities Fails when upgrading to GP 9 Service Pack 3
We have a client that is attempting to load GP9 service pack 3. After the install is completed, the machine was rebooted and the dynamics utilites was ran. While utilities is running (the window states "Server Installation Progress"), an error message pops up stating "File Not Found C:\Program Files\Microsoft Dynamics\GP\SQL\Upgrade\2277\9.0.282\Driver.ini" After this error utilities does not complete and the install fails. I have looked at the directory on this machine and a 9.0.282 folder does not exist. Why is the upgrade looking for it and how can we proceed ...

How do I stop my Pivot tables from spelling out (blank)?
Whenever I have blank cells in the source data for my pivot tables in Excel, instead of the table returning a blank value, it spells out "(blank)". Is there any way to return a blank value, other than through changing all of the blank cells in the source data to " "? How about just changing the (blank) to a space bar--just type a space bar right in that cell in the pivottable itself. Debra Dalgleish has a pivottable addin that does this and lots more: http://www.contextures.com/xlPivotAddIn.html PW wrote: > > Whenever I have blank cells in the source data ...

record not deleting from linked table...but it looks deleted.
Hi folks, I have an odd situation here. I have a table linked from my backend to the frontend that I have displayed through a subform. You can add/edit records no problem but when you delete the record it deletes it as requested and the record is gone on the subform. However if you go look in the backend (or even through the linked table), the record is still there. Anyone ever see this before? Thanks, Alan PS: This is Access 2002. ...

Multiple Possibilitiy formula (IF or Table or Array)
Using XL 2000 work 2003 home Need easiest way to formula"ize" the following: Column (A) is the linked-cell value in an XL dropdown box. Column (B) is the value that column (A) needs to be converted to to upload to MS Access. Description (A) (B) Strongly Agree 2 4 Agree 3 3 Disagree 4 2 Strongly Disagree 5 1 No knowledge 6 0 (No Answer) 7 0 Therefore, the selection of "Strongly Agree" causes the linked dropdown box to equal 2 in column (A). I need the (B) value to equal 4 ...

Updating external data tables
Hello, Using WinXP and Xl-2007; I've tried to format a table where there is an external link (from text) but it seems Excel does not allow that. Is there a way to refresh the data in a defined table with an external data link? I'd prefer not to use a macro but will try it if it's the only way. Thank you for any advice. David K. ...

Is it possible to chart series data from multiple rows in a table?
I am trying to chart a series of data from multiple rows - ref the following table: YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 1992 164278 164864 164221 165084 165920 166544 167284 168184 170158 170859 171531 173598 1993 175683 174121 172829 176710 178999 178772 180987 180996 182227 183233 185266 186538 1994 185948 188562 191756 192275 191300 193606 194229 196568 197876 200281 200301 201139 Can this be done without reformating the table? If so how? Thanks in advance for your help! Rule of thumb: If you spend five minutes fixing up your data, you will save five hours of frustr...