Update table with data from rows with previous dates from specifie

I need to update a table where if a row contains a specified stat value I 
need to change a date value to the date value of a row with the same subID 
but a different stat value.   My primary question would be if I need a cursor 
to perform this operation or if it can be done without a cursor (Sql Server 
2000 Tsql please).   

In the sample data below if a stat value is in ('A', 'E', 'U') I need to 
update that row - I need to update the date2 column  with a date1 value from 
the closest row with the same subID but the stat is in ('N', 'R') and the 
date1 value is earlier than the date1 value in the given row.   

In this sample row 2 has stat='U'.  I need to replace its date2 value with 
the date1 value from row 1 where the stat='N'

row 3 has stat='U'.  I need to update its date2 value with the same date1 
value from row 1 because that is the closest row that the date1 value is 
immediately  previous to row 3 date1.

row 4 has stat='A'.  I need to update its date2 with the same date1 from row 1

row 7 has stat='A'.  I need to update its date2 value with the date1 value 
from row 6 because row 6 is the closes row to row 7 where stat in ('N', 'R') 
and has a date1 that is immediately earlier than than the date1 in row 7.

note: when stat='P' - that is meaningless here

row 9 has stat='A'.  I need the date1 from row 6 since that is the closest 
row to row 9.

Also, I have thousands of these rows (hundreds of thousands).   I need to 
perform the same operation on other subID rows with the same scenario.  I 
could write up an app in C# and just loop through this table and use arrays, 
etc - maybe even some linq, but incase I could do this faster in tsql - I 
would rather go that route.  But if too complex in tsql - then I write up 
some spaghetti code to do it.

create table #tmpx(rowID int Identity(1,1), subID int, code varchar(3), stat 
varchar(1), date1 datetime, date2 datetime)

insert into #tmpX
select 123, 'abc', 'N', '6/1/07', '6/1/07' union all
select 123, 'abc', 'U', '6/19/07', '6/19/07' union all 
select 123, 'abc', 'U', '6/28/07', '6/28/07' union all
select 123, 'abc', 'A', '10/17/07', '10/17/07' union all
select 123, 'abc', 'R', '12/31/07', '12/31/07' union all
select 123, 'abc', 'R', '12/30/08', '12/30/08' union all 
select 123, 'abc', 'A', '3/9/09', '3/9/09' union all
select 123, 'abc', 'P', '4/24/09', '4/24/09' union all
select 123, 'abc', 'A', '5/11/09', '5/11/09' union all
select 123, 'abc', 'P', '6/19/09', '6/19/09' union all 
select 123, 'abc', 'R', '12/19/09', '12/19/09' 


rowID  subID       code       stat           date1        date2
1	123	abc	N	01/06/07	01/06/07
2	123	abc	U	19/06/07	19/06/07
3	123	abc	U	28/06/07	28/06/07
4	123	abc	A	17/10/07	17/10/07
5	123	abc	R	31/12/07	31/12/07
6	123	abc	R	30/12/08	30/12/08
7	123	abc	A	09/03/09	09/03/09
8	123	abc	P	24/04/09	24/04/09
9	123	abc	A	11/05/09	11/05/09
10	123	abc	P	19/06/09	19/06/09
11	123	abc	R	19/12/09	19/12/09
0
Utf
2/2/2010 10:43:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
755 Views

Similar Articles

[PageSpeed] 7

I could not understand from your explanation if the RowId can be used to find closest prior row, or the date columns 
have to be used. Here are two version for both scenarios:

UPDATE #tmpx
SET date2 = (SELECT MAX(B.date1)
               FROM #tmpx AS B
               WHERE B.subID = #tmpx.subID
                 AND B.stat IN ('N', 'R')
                 AND B.rowID < #tmpx.rowID)
WHERE stat IN ('A', 'E', 'U')
   AND EXISTS (SELECT *
               FROM #tmpx AS B
               WHERE B.subID = #tmpx.subID
                 AND B.stat IN ('N', 'R')
                 AND B.rowID < #tmpx.rowID);


UPDATE #tmpx
SET date2 = (SELECT MAX(B.date1)
               FROM #tmpx AS B
               WHERE B.subID = #tmpx.subID
                 AND B.stat IN ('N', 'R')
                 AND B.date2 < #tmpx.date1)
WHERE stat IN ('A', 'E', 'U')
   AND EXISTS (SELECT *
               FROM #tmpx AS B
               WHERE B.subID = #tmpx.subID
                 AND B.stat IN ('N', 'R')
                 AND B.date2 < #tmpx.date1);


-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
2/2/2010 11:08:04 PM
Why don't you want to use a cursor?  It can be done without a cursor by 
using some clever sql queries but chances are high that the performance will 
drop like a rock.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"Rich" <Rich@discussions.microsoft.com> wrote in message 
news:23BAF746-5EE9-4446-92F9-F83B131FCB4C@microsoft.com...
>I need to update a table where if a row contains a specified stat value I
> need to change a date value to the date value of a row with the same subID
> but a different stat value.   My primary question would be if I need a 
> cursor
> to perform this operation or if it can be done without a cursor (Sql 
> Server
> 2000 Tsql please).
>
> In the sample data below if a stat value is in ('A', 'E', 'U') I need to
> update that row - I need to update the date2 column  with a date1 value 
> from
> the closest row with the same subID but the stat is in ('N', 'R') and the
> date1 value is earlier than the date1 value in the given row.
>
> In this sample row 2 has stat='U'.  I need to replace its date2 value with
> the date1 value from row 1 where the stat='N'
>
> row 3 has stat='U'.  I need to update its date2 value with the same date1
> value from row 1 because that is the closest row that the date1 value is
> immediately  previous to row 3 date1.
>
> row 4 has stat='A'.  I need to update its date2 with the same date1 from 
> row 1
>
> row 7 has stat='A'.  I need to update its date2 value with the date1 value
> from row 6 because row 6 is the closes row to row 7 where stat in ('N', 
> 'R')
> and has a date1 that is immediately earlier than than the date1 in row 7.
>
> note: when stat='P' - that is meaningless here
>
> row 9 has stat='A'.  I need the date1 from row 6 since that is the closest
> row to row 9.
>
> Also, I have thousands of these rows (hundreds of thousands).   I need to
> perform the same operation on other subID rows with the same scenario.  I
> could write up an app in C# and just loop through this table and use 
> arrays,
> etc - maybe even some linq, but incase I could do this faster in tsql - I
> would rather go that route.  But if too complex in tsql - then I write up
> some spaghetti code to do it.
>
> create table #tmpx(rowID int Identity(1,1), subID int, code varchar(3), 
> stat
> varchar(1), date1 datetime, date2 datetime)
>
> insert into #tmpX
> select 123, 'abc', 'N', '6/1/07', '6/1/07' union all
> select 123, 'abc', 'U', '6/19/07', '6/19/07' union all
> select 123, 'abc', 'U', '6/28/07', '6/28/07' union all
> select 123, 'abc', 'A', '10/17/07', '10/17/07' union all
> select 123, 'abc', 'R', '12/31/07', '12/31/07' union all
> select 123, 'abc', 'R', '12/30/08', '12/30/08' union all
> select 123, 'abc', 'A', '3/9/09', '3/9/09' union all
> select 123, 'abc', 'P', '4/24/09', '4/24/09' union all
> select 123, 'abc', 'A', '5/11/09', '5/11/09' union all
> select 123, 'abc', 'P', '6/19/09', '6/19/09' union all
> select 123, 'abc', 'R', '12/19/09', '12/19/09'
>
>
> rowID  subID       code       stat           date1        date2
> 1 123 abc N 01/06/07 01/06/07
> 2 123 abc U 19/06/07 19/06/07
> 3 123 abc U 28/06/07 28/06/07
> 4 123 abc A 17/10/07 17/10/07
> 5 123 abc R 31/12/07 31/12/07
> 6 123 abc R 30/12/08 30/12/08
> 7 123 abc A 09/03/09 09/03/09
> 8 123 abc P 24/04/09 24/04/09
> 9 123 abc A 11/05/09 11/05/09
> 10 123 abc P 19/06/09 19/06/09
> 11 123 abc R 19/12/09 19/12/09 


0
Sylvain
2/3/2010 2:20:53 AM
Actually, in raw terms the cursor solution will be the worst one. However, 
if a table lock could cause you issues, doing the updates in smaller batches 
would be preferable.

"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
news:e2HgEeHpKHA.5224@TK2MSFTNGP05.phx.gbl...
> Why don't you want to use a cursor?  It can be done without a cursor by 
> using some clever sql queries but chances are high that the performance 
> will drop like a rock.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server 
> (French)
>
>
> "Rich" <Rich@discussions.microsoft.com> wrote in message 
> news:23BAF746-5EE9-4446-92F9-F83B131FCB4C@microsoft.com...
>>I need to update a table where if a row contains a specified stat value I
>> need to change a date value to the date value of a row with the same 
>> subID
>> but a different stat value.   My primary question would be if I need a 
>> cursor
>> to perform this operation or if it can be done without a cursor (Sql 
>> Server
>> 2000 Tsql please).
>>
>> In the sample data below if a stat value is in ('A', 'E', 'U') I need to
>> update that row - I need to update the date2 column  with a date1 value 
>> from
>> the closest row with the same subID but the stat is in ('N', 'R') and the
>> date1 value is earlier than the date1 value in the given row.
>>
>> In this sample row 2 has stat='U'.  I need to replace its date2 value 
>> with
>> the date1 value from row 1 where the stat='N'
>>
>> row 3 has stat='U'.  I need to update its date2 value with the same date1
>> value from row 1 because that is the closest row that the date1 value is
>> immediately  previous to row 3 date1.
>>
>> row 4 has stat='A'.  I need to update its date2 with the same date1 from 
>> row 1
>>
>> row 7 has stat='A'.  I need to update its date2 value with the date1 
>> value
>> from row 6 because row 6 is the closes row to row 7 where stat in ('N', 
>> 'R')
>> and has a date1 that is immediately earlier than than the date1 in row 7.
>>
>> note: when stat='P' - that is meaningless here
>>
>> row 9 has stat='A'.  I need the date1 from row 6 since that is the 
>> closest
>> row to row 9.
>>
>> Also, I have thousands of these rows (hundreds of thousands).   I need to
>> perform the same operation on other subID rows with the same scenario.  I
>> could write up an app in C# and just loop through this table and use 
>> arrays,
>> etc - maybe even some linq, but incase I could do this faster in tsql - I
>> would rather go that route.  But if too complex in tsql - then I write up
>> some spaghetti code to do it.
>>
>> create table #tmpx(rowID int Identity(1,1), subID int, code varchar(3), 
>> stat
>> varchar(1), date1 datetime, date2 datetime)
>>
>> insert into #tmpX
>> select 123, 'abc', 'N', '6/1/07', '6/1/07' union all
>> select 123, 'abc', 'U', '6/19/07', '6/19/07' union all
>> select 123, 'abc', 'U', '6/28/07', '6/28/07' union all
>> select 123, 'abc', 'A', '10/17/07', '10/17/07' union all
>> select 123, 'abc', 'R', '12/31/07', '12/31/07' union all
>> select 123, 'abc', 'R', '12/30/08', '12/30/08' union all
>> select 123, 'abc', 'A', '3/9/09', '3/9/09' union all
>> select 123, 'abc', 'P', '4/24/09', '4/24/09' union all
>> select 123, 'abc', 'A', '5/11/09', '5/11/09' union all
>> select 123, 'abc', 'P', '6/19/09', '6/19/09' union all
>> select 123, 'abc', 'R', '12/19/09', '12/19/09'
>>
>>
>> rowID  subID       code       stat           date1        date2
>> 1 123 abc N 01/06/07 01/06/07
>> 2 123 abc U 19/06/07 19/06/07
>> 3 123 abc U 28/06/07 28/06/07
>> 4 123 abc A 17/10/07 17/10/07
>> 5 123 abc R 31/12/07 31/12/07
>> 6 123 abc R 30/12/08 30/12/08
>> 7 123 abc A 09/03/09 09/03/09
>> 8 123 abc P 24/04/09 24/04/09
>> 9 123 abc A 11/05/09 11/05/09
>> 10 123 abc P 19/06/09 19/06/09
>> 11 123 abc R 19/12/09 19/12/09
>
> 


0
Jay
2/3/2010 2:35:40 AM
Hello Plamen,

I tried out your queries and they both work great!  Thank you very much for 
your help.



"Plamen Ratchev" wrote:

> I could not understand from your explanation if the RowId can be used to find closest prior row, or the date columns 
> have to be used. Here are two version for both scenarios:
> 
> UPDATE #tmpx
> SET date2 = (SELECT MAX(B.date1)
>                FROM #tmpx AS B
>                WHERE B.subID = #tmpx.subID
>                  AND B.stat IN ('N', 'R')
>                  AND B.rowID < #tmpx.rowID)
> WHERE stat IN ('A', 'E', 'U')
>    AND EXISTS (SELECT *
>                FROM #tmpx AS B
>                WHERE B.subID = #tmpx.subID
>                  AND B.stat IN ('N', 'R')
>                  AND B.rowID < #tmpx.rowID);
> 
> 
> UPDATE #tmpx
> SET date2 = (SELECT MAX(B.date1)
>                FROM #tmpx AS B
>                WHERE B.subID = #tmpx.subID
>                  AND B.stat IN ('N', 'R')
>                  AND B.date2 < #tmpx.date1)
> WHERE stat IN ('A', 'E', 'U')
>    AND EXISTS (SELECT *
>                FROM #tmpx AS B
>                WHERE B.subID = #tmpx.subID
>                  AND B.stat IN ('N', 'R')
>                  AND B.date2 < #tmpx.date1);
> 
> 
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com
> .
> 
0
Utf
2/4/2010 4:26:10 PM
Reply:

Similar Artilces:

2007 ->Money+/update issues Part 1 adj Balance
Upgrade from Money 2007 – Money+ I have been having some bad issues the past few weeks and they are apparently getting worse. It started with the update that I believe happened just before Money+ came out . After the update it added an “adj. Balance” column in the banking/account list tab. This adjusted balance is all over the place and even if I balance it out it goes out of whack right away. I use Citibank and have them do all my payments online so I don’t have to worry about adjusted balances because the bank balance is always right and I don’t write checks and use my debit card (h...

Using insert to paste a row--how done in Excel 2007
Hi, In my old version of Excel, I could copy a row or chunk of rows, move to a new spot and use the "insert row" icon to insert the rows and paste it automatically. Now in Office 2007 it just inserts a row instead of what I have copied. I want it the old way! How do I do it? -- Thanks, PTweety R-click, Insert Copied Cells. pickytweety wrote: > Hi, > In my old version of Excel, I could copy a row or chunk of rows, move to a > new spot and use the "insert row" icon to insert the rows and paste it > automatically. Now in Office 2007 it just inserts a r...

CRM 4.0 Report Deployment
Hi When I deploy a custom report (through Web UI) and run it, no data is displayed. The report header, footer, etc, display, but there is no data. To simplify the problem I created a very simple report with no parameters, which selects all from filteredsystemuser, and the problem was the same. This is in a test VPC, the whole environment is contained in the VPC. I am developing/publishing/viewing as the CRM administrator. The steps I took were: - Created a new report in Business Intelligence Development Studio using Wizard (installed in VPC) - Created a new DataSet called dsTest with a new D...

updating prices manually
Hello, Just upgraded to Money 2006 from Money 2003. I am tracking several investments for which prices cannot be updated online. In 2003, it was possible to enter the daily price manually by right-clicking on the investment name and entering the price for a specific date. I can't find this function in 2006. Does anybody know where it is, or if it still exists? Thanks. I should add that after right-clicking on the investment name, in 2003 I used to click on "Update Price", after which I could enter the price and date. "paula" wrote: > Hello, > > J...

WMP 11 error C00D1163 (after update KB971513)
Windows Media Player 11 will not play DVDs. I always get error C00D1163 "Windows Media Player cannot play this DVD because there is a problem with digital copy protection between your DVD drive, decoder, and video card. Try installing an updated driver for your video card." DVDs I have owned for years are now impossible to play (so are rentals). I am having the same problem as others in this forum, although my system configuration is different -- which indicates this is a widespread problem. It is an interesting coincidence that my problem started immediately ...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

Opening tab-delimited files in Excel... how to stop the autoformat of dates?
Hello, I'm doing bioinformatics work in which I often download tab-delimited files describing genomes and open them in Excel. Unfortunately, Excel decides that genes with names like APR1 and SEP7 are actually dates, and reformats them as such. This is a huge headache, as I often don't notice these problems until much later. Is there anyway to completely disable this date autoformatting? I've searched high and low and have yet to find a solution. Thanks! P.S. I'm using Excel 2002 -- salamander ------------------------------------------------------------------------ sa...

query with inline dummy table
Is there a way in Access to create a query that contains the table records within the query itself? I want to avoid creating a dummy table and just use values within the query definition. I was thinking about using syntax similar to the insert into statement I would use to populate the dummy table, but I'm not sure if I have a syntax problem or I'm trying to solve an impossible problem. The query I'm thinking of might look something like this: select * from values("test"); -or- select * from ( ("1/1/07","2/1/07","3/1/07","4/1/07&qu...

workaround for non normalized table
I've inherited a database that relies heavily on a non normalized table. Until I can convince the general manager that I can normalize the data without losing any records, I've got to have a workaround for certain situations. Namely, I need to ensure that data is not being badly reproduced at various stages of our operations. I would therefore like to reference the information directly from the main table in order to populate certain information in related tables. In this case, the main table uses [Order Number] as its primary key, and ties it to a bunch of information like ...

Open document from previous version
How do I open a document from a previous version of Publisher..I have 2000? Are you certain it is an earlier version? Do you have Norton? Disable "script blocking" and try again. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "Jan" <jan0704@msn.com> wrote in message news:44cc01c4a49e$a8fcbbe0$a601280a@phx.gbl... > How do I open a document from a previous version of > Publisher..I have 2000? Jan wrote: > How do I open a document from a previous version of > Publisher..I have 2000? If it ...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Error in code exportin tables to excel
I'm tryin to export a table to excel with the code: Private Sub Command4_Click() On Error GoTo Err_Command0_Click Dim stDocName As String stDocName = "F01_UT" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, F01_UT, "C:\TRY\Tbls", True Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub However, Access displays the message: Compile error : Syntax error What I'm doing wrong? If I want to export more tablas do I have to write more lines (DoCmd.TransferSpreadsheet acExport, acSpr...

Field service table structure and field definitions
The 9 SDK does not reference or define the fields or tables for the Field service component. Where can I find this information? I need to move RMA into a data warehouse and am having significant trouble identifying the data flows. Thanks, Will You may want to contact I.B.I.S. They wrote the application. -- Charles Allen, MVP "WS" wrote: > The 9 SDK does not reference or define the fields or tables for the Field > service component. Where can I find this information? I need to move RMA > into a data warehouse and am having significant trouble identifying th...

Updateing data when closing forms
I am using MS Access 2000 I have a table that has several required fields. I developed a form to add records to the table. In addition to the table fields, the form has a form-close button constructed with the button wizard. When I enter a record that does not have the required information and close the form with the “Close Window” (X) button on the tool bar, the appropriate error message appears telling me that there is missing data. (Just what I want) When I use the close button on the form, the form closes with no message and without adding the record. How do I give the button on the f...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

generate list of non-entries within dates
I use Excel (2007) to record activity and support for about 100 current online learners and I have to provide an activity summary sheet every two weeks. The list of learners is in one sheet and are marked "current", agreed break" or "completed" in an adjacent column. The total list is about 1000 and grows by about 10 each week. The activity log is another sheet of the same workbook. Assuming that all activity is logged in this way I need to generate a list (sheet) of those current learners who have not submitted any recent work and are therefore "inacti...

How do I import data from a SECURED website into Excel?
I can import data from a regular web page directly into Excel spreadsheet, but when I tried importing them from a secured site I encountered problems. Does anyone have any suggestions? ...

VBA code to hide all the tables on form open
I don't want people to use a blank mdb to import my tables. I manually hide them all. However, after running the macro to delete all records and import from .txt, the table become unhide. I do the importation on daily basis. I posted to macro newsgroup and asked way to hide table after importation action macro but got no answer. Maybe it cannot be done in macro? If so, I need VBA code to hide all the tables on form open. Thanks. Hiding your tables won't prevent people from being able to import them into a blank mdb. All they have to do is ensure that they've set the datab...

Lookup Wizard changing Data Type
I am trying to make Lookups from Table Field to Table Field and generally it seems OK. However the LookupWizard is changing the Data Type from Text to Number (I guess it's looking at the ID?) Data picked from Combo Box 'looks' OK in Table view A Query view is asking for a number but displays the Text field value when a valid ID number is added. Viewing Results in FrontPage Database Wizard, and using hand coded .asp querie, it's showing the ID field value. I can see why... Any ideas? Merci. Yes, the wizard isn't the problem it's the use of Lookup Fields in ...

retrieving data from Great Plains via eConnect
Hi, I've been working with eConnect for the last two weeks and have had success with importing a variety of transactions into Great Plains. However, I'd like to query Great Plains on demand and have data returned to me. For example, given a certain field value (like customer name = "Smith"), I'd like to have all customer records returned that match that specified value. Is there any way to do this easily with eConnect? I'm not so interested in the Transaction Requester Service because that seems to be focused more on automatically returning records that have...

time/date stamp on email
i used microsoft outlook for email. i sent an email to another outlook user and i wanted to find out if he's read it or not. is there any way to view the time/date that the person reads the email ? if you requested a read receipt and if the recipient uses a program that is capable of replying, yes. otherwise, no. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchang...

Keeping a range constant when inserting rows
Hello, I'm trying to keep a range of cells constant within a function when I insert a row (e.g. average(a1:a6) becomes average(a1:a7) but I want it to keep the a1:a6 range). Even if I use absolute cell references ($a$1:$a$6), it doesn't help. I would greatly appreciate any ideas. Thanks, Jeff Jeff, In your formula, use: =AVERAGE(INDIRECT("A1:A6")) Absolute cell references (dollar signs) do one thing only: They keep any copies you make of the cell references from changing relatively as they're copied. They still change when the cells to which they refer are m...

dynamically filtered pivot table
I'm trying to make a pivot table that will dynamically hide a section o its contents based on a boolean operator the user can set. Becaus this boolean is used in several places, I don't want to require th user to manually set the visibility parameters for the pivot table. It seems like the only way to do this effectively is to have th booleans set by a button, and have the button not only toggle th boolean, but also change the visibility in the pivot table. here's the code i've tried: Sub ToggleButton1_Click() If ToggleButton1.Caption = "Include" Then 'C...

Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records into Excel 2007 and am not having any luck. I do not find the 2007 file extension in the drop down and choosing the latest version only exports part of the table. Any thoughts on whether this is possible to do? -- Carol Hi Carol, This is even hard to do with Excel 2007. You won't be able to do it directly with Excel 2003. You could export the file as a comma separated value with a csv suffix. By default Excel usually opens csv files. However you may lose formatting and other stuff. I ju...

How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the workbook. How do I do that? There are limits to how you can combine data from different sheets. This article describes them. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Donna" <Donna@discussions.microsoft.com> wrote in message news:7DF7D186-9426-4B54-A322-FE485FD20529@microsoft.com... > I've created a graph that I want to...