Can you help me with a troublesome query?

I'd like some help formulating a query for the following situation.

The problem I actually have to solve is somewhat complicated, so I've 
extracted the essence of the problem into the simplified situation described 
below. While I know I could do this with an ugly cursor solution, I'm pretty 
sure that performance would be terrible as the table grows to a large size, 
which it will in the real problem I have to solve. So I'd like to hear your 
best ideas on how to solve this in a way that will scale to as high as a 
million records or more.

Consider this table:

ID Site Sensor  Sample1  Sample2  LastUpdated

1   1     1       1        1       1/1/2010
2   2     1       2        2       1/1/2010
3   3     1       3        3       1/1/2010
4   2     1       4        4       1/2/2010
5   3     1       5        5       1/3/2010
6   3     1       6        6       1/4/2010
7   2     1       7        7       1/4/2010

Imagine that these sensors send in their data on an irregular schedule, 
depending on when something that they're sensing changes significantly. All 
three of them in this table send in an initial report as of 1/1, site 2 
happens to send in an update on 1/2, site 3 sends in updates on 1/3 and 1/4, 
and site 2 sends in another update on 1/4 as well. When they send in a 
report, it includes current values of all the sensor's data, not necessarily 
just the ones that have changed.

Reports come in at morning midnight so, for instance, the record with ID 4 
appears in the database at 1/2/2010 12:00:00 AM. To make it easy to 
recognize the data, I've artifically made the values match the record keys.

So here's the problem:

I want to take a look at all the sensors as of some arbitrary date, and I 
want to see the current status of all of them as of that date.

If I take a report of the sensor status as of 1/1, I'd get:

1   1     1       1        1       1/1/2010
2   2     1       2        2       1/1/2010
3   3     1       3        3       1/1/2010

If I take a report of the sensor status as of 1/3, I'd get:

1   1     1      1         1       1/1/2010
4   2     1      4         4       1/2/2010
5   3     1      5         5       1/3/2010

If I take a report of sensor status as of 1/4, I'd get:

1   1     1      1         1       1/1/2010
6   3     1      6         6       1/4/2010
7   2     1      7         7       1/4/2010

In essence, I want the most recent whole record from any sensor whose last 
update is <= the requested date.


Here's a table create script:

CREATE TABLE [dbo].[TestTable](
 [ID] [int] NOT NULL,
 [Site] [int] NOT NULL,
 [Sensor] [int] NOT NULL,
 [Sample1] [int] NOT NULL,
 [Sample2] [int] NOT NULL,
 [LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]

And here's a csv that matches the data above:

1,1,1,1,1,2010-01-01 00:00:00.000
2,2,1,2,2,2010-01-01 00:00:00.000
3,3,1,3,3,2010-01-01 00:00:00.000
4,2,1,4,4,2010-01-02 00:00:00.000
5,3,1,5,5,2010-01-03 00:00:00.000
6,3,1,6,6,2010-01-04 00:00:00.000
7,2,1,7,7,2010-01-04 00:00:00.000


Any suggestions would be welcomed.

Thanks,
Tom Dacon
Dacon Software Consulting


0
Tom
5/9/2010 5:07:27 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
525 Views

Similar Articles

[PageSpeed] 39

CREATE TABLE [dbo].[TestTable](
 [ID] [int] NOT NULL,
 [Site] [int] NOT NULL,
 [Sensor] [int] NOT NULL,
 [Sample1] [int] NOT NULL,
 [Sample2] [int] NOT NULL,
 [LastUpdated] [datetime] NOT NULL
) ON [PRIMARY];

Insert dbo.TestTable(ID, Site, Sensor, Sample1, Sample2, LastUpdated)
Select 1,   1,     1,       1,        1,       '1/1/2010'
Union All Select 2,   2,     1,       2,        2,       '1/1/2010'
Union All Select 3,   3,     1,       3,        3,       '1/1/2010'
Union All Select 4,   2,     1,       4,        4,       '1/2/2010'
Union All Select 5,   3,     1,       5,        5,       '1/3/2010'
Union All Select 6,   3,     1,       6,        6,       '1/4/2010'
Union All Select 7,   2,     1,       7,        7,       '1/4/2010';

-- Jan 1
With cte As
(Select ID, Site, Sensor, Sample1, Sample2, LastUpdated,
  Row_Number() Over (Partition By Site, Sensor Order By LastUpdated Desc) As 
rn
From dbo.TestTable
Where LastUpdated <= '20100101')
Select ID, Site, Sensor, Sample1, Sample2, LastUpdated
From cte
Where rn = 1;

-- Jan 3
With cte As
(Select ID, Site, Sensor, Sample1, Sample2, LastUpdated,
  Row_Number() Over (Partition By Site, Sensor Order By LastUpdated Desc) As 
rn
From dbo.TestTable
Where LastUpdated <= '20100103')
Select ID, Site, Sensor, Sample1, Sample2, LastUpdated
From cte
Where rn = 1;

-- Jan 4
With cte As
(Select ID, Site, Sensor, Sample1, Sample2, LastUpdated,
  Row_Number() Over (Partition By Site, Sensor Order By LastUpdated Desc) As 
rn
From dbo.TestTable
Where LastUpdated <= '20100104')
Select ID, Site, Sensor, Sample1, Sample2, LastUpdated
From cte
Where rn = 1;
go

Drop Table TestTable;

Tom
"Tom Dacon" <tdacon@community.nospam> wrote in message 
news:2AD0512E-85D5-4F82-B0E7-2DE4595FB55E@microsoft.com...
> I'd like some help formulating a query for the following situation.
>
> The problem I actually have to solve is somewhat complicated, so I've 
> extracted the essence of the problem into the simplified situation 
> described below. While I know I could do this with an ugly cursor 
> solution, I'm pretty sure that performance would be terrible as the table 
> grows to a large size, which it will in the real problem I have to solve. 
> So I'd like to hear your best ideas on how to solve this in a way that 
> will scale to as high as a million records or more.
>
> Consider this table:
>
> ID Site Sensor  Sample1  Sample2  LastUpdated
>
> 1   1     1       1        1       1/1/2010
> 2   2     1       2        2       1/1/2010
> 3   3     1       3        3       1/1/2010
> 4   2     1       4        4       1/2/2010
> 5   3     1       5        5       1/3/2010
> 6   3     1       6        6       1/4/2010
> 7   2     1       7        7       1/4/2010
>
> Imagine that these sensors send in their data on an irregular schedule, 
> depending on when something that they're sensing changes significantly. 
> All three of them in this table send in an initial report as of 1/1, site 
> 2 happens to send in an update on 1/2, site 3 sends in updates on 1/3 and 
> 1/4, and site 2 sends in another update on 1/4 as well. When they send in 
> a report, it includes current values of all the sensor's data, not 
> necessarily just the ones that have changed.
>
> Reports come in at morning midnight so, for instance, the record with ID 4 
> appears in the database at 1/2/2010 12:00:00 AM. To make it easy to 
> recognize the data, I've artifically made the values match the record 
> keys.
>
> So here's the problem:
>
> I want to take a look at all the sensors as of some arbitrary date, and I 
> want to see the current status of all of them as of that date.
>
> If I take a report of the sensor status as of 1/1, I'd get:
>
> 1   1     1       1        1       1/1/2010
> 2   2     1       2        2       1/1/2010
> 3   3     1       3        3       1/1/2010
>
> If I take a report of the sensor status as of 1/3, I'd get:
>
> 1   1     1      1         1       1/1/2010
> 4   2     1      4         4       1/2/2010
> 5   3     1      5         5       1/3/2010
>
> If I take a report of sensor status as of 1/4, I'd get:
>
> 1   1     1      1         1       1/1/2010
> 6   3     1      6         6       1/4/2010
> 7   2     1      7         7       1/4/2010
>
> In essence, I want the most recent whole record from any sensor whose last 
> update is <= the requested date.
>
>
> Here's a table create script:
>
> CREATE TABLE [dbo].[TestTable](
> [ID] [int] NOT NULL,
> [Site] [int] NOT NULL,
> [Sensor] [int] NOT NULL,
> [Sample1] [int] NOT NULL,
> [Sample2] [int] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> ) ON [PRIMARY]
>
> And here's a csv that matches the data above:
>
> 1,1,1,1,1,2010-01-01 00:00:00.000
> 2,2,1,2,2,2010-01-01 00:00:00.000
> 3,3,1,3,3,2010-01-01 00:00:00.000
> 4,2,1,4,4,2010-01-02 00:00:00.000
> 5,3,1,5,5,2010-01-03 00:00:00.000
> 6,3,1,6,6,2010-01-04 00:00:00.000
> 7,2,1,7,7,2010-01-04 00:00:00.000
>
>
> Any suggestions would be welcomed.
>
> Thanks,
> Tom Dacon
> Dacon Software Consulting
>
> 

0
Tom
5/9/2010 6:08:24 PM
From the narrative and sample data, is this the actual key?  We know
that RDBMS does not have magical universal "id" attributes for keys,
or anything else. Do you have the 2008 version yet so you can use DATE
and some of the ANSI Standard stuff?

CREATE TABLE Something_Tests
(site_nbr INTEGER NOT NULL,
 reading_date DATE DEFAULT CURRENT_DATE NOT NULL),
 PRIMARY KEY (sample_id, reading_date),
 sensor_nbr INTEGER NOT NULL,
 sample1_reading INTEGER NOT NULL,
 sample2_reading INTEGER NOT NULL);

Or is the key actually (sample_id, sensor_nbr, reading_date)?

>> In essence, I want the most recent whole record [sic] from any sensor wh=
ose last update is <=3D the requested date .. I want to take a look at all =
the sensors as of some arbitrary date, and I want to see the current status=
 of all of them as of that date. <<

The minute you say =93record=94 I am 90% certain you are still stuck in a
file system mindset, complete with sequential ordering. This is not
how we design history tables in SQL. Time is a continuum, so we use
pairs of temporal points. Try this skeleton:

CREATE TABLE Something_Test_History
(site_nbr INTEGER NOT NULL,
 sensor_nbr INTEGER NOT NULL,
 test_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
 test_end_date DATE, -- null means current readings
 CHECK(test_start_date < test_end_date),
 PRIMARY KEY (site_nbr, sensor_nbr, test_start_date),
 sample1_reading INTEGER NOT NULL,
 sample2_reading INTEGER NOT NULL);
);

We can add some more DDL to assure that there are no gaps and always
one current reading per (site_nbr, sensor_nbr), if you need real data
integrity.

You use a "@my_date BETWEEN test_start_date AND COALESCE
(test_end_date, CURRENT_DATE)" predicate to get the appropriate
readings.  It is also a good idea to have a VIEW with the current
data:

CREATE VIEW Current_Something_Tests (..)
AS
SELECT ..
  FROM Something_Test_History
 WHERE test_end_date IS NULL;

Now your only problem is to write a stored procedure that will update
the table and insert a new row.  You can do this with a single MERGE
statement.

Finally, download the Rick Snodgrass book on Temporal Queries in SQL
from the University of Arizona website (it is free).  Or buy a copy of
Tom Johnston's new book.

0
CELKO
5/9/2010 6:38:49 PM
Thank you, Tom - that will be immediately useful. I intuited that there 
would be some way to do it, but wasn't aware of partitioning. This will 
prompt me to do some reading and add that sort of technique to my toolkit.

Tom


0
Tom
5/9/2010 8:06:44 PM
Thank you  for your suggestions, --CELKO--. I have no control over the 
design of the table, so I must seek a solution that works within that 
constraint. However I will investigate the technique you describe, as an 
academic exercise. I have made a few comments inline.

Tom


"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:86cfa933-ef33-47d1-9368-9ce9c39ef910@r11g2000yqa.googlegroups.com...
From the narrative and sample data, is this the actual key?  We know
that RDBMS does not have magical universal "id" attributes for keys,
or anything else. Do you have the 2008 version yet so you can use DATE
and some of the ANSI Standard stuff?

TOM: The actual table in the real problem has an auto-incrementing column 
named ID. It is irrelevant to the simplified problem statement and should 
not have appeared, since it did nothing but prompt your complaint. I'm 
currently on SQL Server 2005.


CREATE TABLE Something_Tests
(site_nbr INTEGER NOT NULL,
 reading_date DATE DEFAULT CURRENT_DATE NOT NULL),
 PRIMARY KEY (sample_id, reading_date),
 sensor_nbr INTEGER NOT NULL,
 sample1_reading INTEGER NOT NULL,
 sample2_reading INTEGER NOT NULL);

Or is the key actually (sample_id, sensor_nbr, reading_date)?

>> In essence, I want the most recent whole record [sic] from any sensor 
>> whose last update is <= the requested date .. I want to take a look at 
>> all the sensors as of some arbitrary date, and I want to see the current 
>> status of all of them as of that date. <<

The minute you say �record� I am 90% certain you are still stuck in a
file system mindset, complete with sequential ordering. This is not
how we design history tables in SQL. Time is a continuum, so we use
pairs of temporal points. Try this skeleton:

TOM: I should have used the term "row". My intention was to emphasize that I 
wasn't looking to percolate individual sample values up through the data to 
the report date. I don't quite know what you mean by file system mindset; 
all my database programming has been with relational databases such as SQL 
Server and I'm quite comfortable with their set-oriented nature.


CREATE TABLE Something_Test_History
(site_nbr INTEGER NOT NULL,
 sensor_nbr INTEGER NOT NULL,
 test_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
 test_end_date DATE, -- null means current readings
 CHECK(test_start_date < test_end_date),
 PRIMARY KEY (site_nbr, sensor_nbr, test_start_date),
 sample1_reading INTEGER NOT NULL,
 sample2_reading INTEGER NOT NULL);
);

We can add some more DDL to assure that there are no gaps and always
one current reading per (site_nbr, sensor_nbr), if you need real data
integrity.

You use a "@my_date BETWEEN test_start_date AND COALESCE
(test_end_date, CURRENT_DATE)" predicate to get the appropriate
readings.  It is also a good idea to have a VIEW with the current
data:

CREATE VIEW Current_Something_Tests (..)
AS
SELECT ..
  FROM Something_Test_History
 WHERE test_end_date IS NULL;

Now your only problem is to write a stored procedure that will update
the table and insert a new row.  You can do this with a single MERGE
statement.

Finally, download the Rick Snodgrass book on Temporal Queries in SQL
from the University of Arizona website (it is free).  Or buy a copy of
Tom Johnston's new book.

TOM: Thank you for those pointers. As a starter, I look forward to reading 
Rick's book.




0
Tom
5/9/2010 8:27:36 PM
Reply:

Similar Artilces:

Can outlook have two completely seperate users from same computer?
In Outlook I would like to have to completely seperate users so that each cannot access the others account information and also so that emails sent to my account cannot be opened through her account.... almost I guess as i had two different outlooks on my computer thank you In news:7A4A32AD-E398-4945-BA92-6B9D53F1ADD1@microsoft.com, Hayley <Hayley@discussions.microsoft.com> typed: > In Outlook I would like to have to completely seperate users so that > each cannot access the others account information and also so that > emails sent to my account cannot be opened through he...

HELP!! #2
Does anyone know how to Synch AIM mail to outlook? ...

users can delete public folder calendar entries without permission
why would a user be able to delete a calender entry in exchange 2003 (Active Directory) public folder with permissions on that folder set to none in the delete section? any help would be greatly appreciated. shrp On Mon, 15 Mar 2010 17:23:15 -0700 (PDT), shrpshtr <shrpshtr@gmail.com> wrote: >why would a user be able to delete a calender entry in exchange 2003 >(Active Directory) public folder with permissions on that folder set >to none in the delete section? any help would be greatly appreciated. The short answer is that they wouldn't be able to do that. ...

Can i see my archive emails in the same folders as they were befor
I don't want to see my emails in a separate location. I want to be able to archive my emails but still be displayed [with a different color tint perhaps, indication that they are archived] in the same location. Imagine that I have the following structure: Customers -> Germany -->DHL -->ZDF -> France -->France Telecom -->Canal+ If i am looking for an email i don't want to look in two locations. [current and archive folder]. Is it possible ? "Johnny" <Johnny@discussions.microsoft.com> wrote in message news:644549CE-D381-4331-...

Crosstab query totals
I have Query1 that returns 2 fields: CatID Step1Decision CatID can be 01 thru 29 Step1Decision can be N,X,P,D,R Query1: SELECT qryGrievances.CatID, qryGrievances.Step1Decision FROM qryGrievances, qryStartEnd Query2 uses Query1 joined to qryCategories to return all the categories and their description plus those in Query1 Query2: SELECT qryCategories.CatID, qryCategories.CatDesc, Query1.Step1Decision FROM qryCategories LEFT JOIN Query1 ON qryCategories.CatID = Query1.CatID Query3 uses Query2 in a crosstab so the Step1Decision (N,X,P,D,R) become column headings and cate...

How can I draw a Parabola in Excel? like y = x^2 + 3
In a1:a10 enter numbers such as 1,2,3.... In b1 type =a1^2+3 copy this down to B10 select a1:b10; click chart wizard; select XY chart done now play with values in A to get what you need best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Max" <Max@discussions.microsoft.com> wrote in message news:F72AB641-F882-4DAD-B0C3-322297A124FC@microsoft.com... > ...

money 2007 vs. providian ? please help
money wont retrieve info from providian. if i look up providian in the ms money compatibility link as posted by others I get 'direct services' what gives any help? In microsoft.public.money, newby wrote: >money wont retrieve info from providian. > >if i look up providian in the ms money compatibility link as posted by >others I get 'direct services' I don't think so. It looks like "third party". > >what gives > >any help? ...

Can't give access to new Test Company on GP10
I created an additional company on GP10 which I called TEST. Through SQL Server, I restored a backup file of our production database to test. Then I went into GP > User Access Setup, and tried to assign access to the TEST company to users, but it would not allow it. The message read, "The user could not be added to one or more databases." I created the new company as sa, and sa does have access to the TEST company. I would appreciate any help with this. Thanks! Have you tried going into SQL server and removing all the permissions from the Test database before going into...

Restarting Record Count in a Query
Hello, I have a query that returns these results for me: Badge No DepLastName DepFirstName 11111 Smith Mary 11111 Smith Joe 22222 Brown Bill 22222 Brown Mary 22222 Brown Jack I need to count the dependents that are linked to each Badge No so my results look like this Badge No Count DepLastName DepFirstName 11111 1 Smith Mary 11111 2 Smith Joe 22222 1 Brown Bill 22222 2 ...

Bar Graph Help
I'm trying to create a graph similar to the bar graph shown in the middle of the dashboard in the link below labeled as "YTD % of target", but don't know how to get the overlapping bars in excel. Can anyone advise? Thanks! http://www.bonavistasystems.com/Download2/Airline%20Dashboard2.png Here's one way to do it... Add the YTD % series to your bar graph, and then format the series so that it is on the secondary axis. XL will probably create a secondary X-axis, but that will cause a distortion of the data. We need them to be plotted on the same x-ax...

How can a set an alarm in Excel?
I don't know if this is possible, but I figured I would ask: I have a spreadsheet that I work with that shows the schedules of my students; each different place my student goes has its own row, so a student may have many rows if they have many activities. currently, i auto filter the day column to the current day so i can see what students have activities on that day, and when/where they are. i was wondering if i could set an alarm on the column labeled 'start time' that would pop a window up when any value in that cell matched the system time... is this possible?? thanks, -...

Error can't Find File
Hi, I have a macro that opens a file and pulls any outstanding tasks from the previous day. I have a problem when it is a new month because the previous month's file is called MyFileMarch2010. The macro now looks for MyFileApril2010. How do I capture the error and look for the previous month's file if it is the first business day of the month? Any help or suggestions is appreciated. -- Thank you Dave As ALWAYS, post YOUR code for comments if day(date)=1 then do this else do that end if -- Don Guillett Microsoft MVP Excel SalesAid Software dguill...

How to convert Null values to zero when create an average query
This is my crosstab query Items Worked April 2010 March 2010 Average Reports 2 4 3 Tables 5 5 Files 2 2 2 As you can see, for "Tables" the query is giving me an average of "5" instead "2.5" because is not counting the null value date. How can I fix that? the SQL query so far is this: TRANSFORM Avg([Integrate Query].T...

Active Directory Backup Queries
I'm investigating the Active directory Backup functions and am somewhat confused. I have written a test program which I run on my machine (meteor). I have 2 queries: 1) If I call DsIsNTDSOnline(), passing in either of our live servers (dabs and galaxy) it succeeds and says that Active Directory is online. If I pass in our test server (dstest), it fails with RPC_S_INVALID_BINDING. MSDN for DsIsNTDSOnline() says that "The server must be the same computer that this function is called from". Surely it should return RPC_S_INVALID_BINDING for all 3 of the above cases since my machine...

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

Can't paste text to a document because of macros
When I open a document in Word 07, and try to copy text to it it, I get a message that macros are disabled and to go to help for instructions. Instructions say to close the doc. and reopen with macros enabled. BUT it doesn't say HOW to do that. And, why is this happening all of a sudden and everytime I open a doc.? If this happens with all documents in Word, it's probably being caused by poorly designed code in an add-in (or in the Normal template). Did you install something recently? -- Stefan Blom Microsoft Word MVP "leslie7" <leslie7@discus...

Help Downloading OS to Target
Hi all. Kind of new to CE. I have managed to build a CEPC OS and have tested it on MS Virtual Machine. Problem I am having is trying to download it to the CEPC. From everything I can find, I need to have a 1.44 boot disk. Neither of my machines have a floppy drive. Ultimately, an ISO that can be burned to CD would be great! Any help would be greatly appreciated. Thank you. That depends on the media that your BIOS supports for booting -- Bruce Eitman (eMVP) Senior Engineer Bruce.Eitman AT Eurotech DOT com My BLOG http://geekswithblogs.net/bruceeitman Eurotech Inc....

Can you offer a solution
I need to use an emailed form that can be populated and then the inf extracted into a particular format on one overall .csv file. Can macro type add in be used for the extraction?Any input as to the bes and easiest method of doing this would. be great. Sorry I am new a this. many thank -- Message posted from http://www.ExcelForum.com ...

OWA can be use and sometimes could not
Hi to proffesionals I have Exchange 2003 on Win 2003 Active Directory Domain. Some users can use OWA sometimes, and other users could not use OWA partly time. Why does this happen? Regards, B.Majidi In news:u2YBFh8NHHA.320@TK2MSFTNGP06.phx.gbl, Babak Majidi <b_majidi@yahoo.co.uk> typed: > Hi to proffesionals > I have Exchange 2003 on Win 2003 Active Directory Domain. > Some users can use OWA sometimes, and other users could not use OWA > partly time. > Why does this happen? > > Regards, > B.Majidi It's impossible to say with so little information - you h...

how can i hide my dialog when the dialog initilizes
i want to hide my dialog when the dialog initilizes.i add this line in the function Mydialog::InitDialog() ,ShowWindow(SW_HIDE) but it cause a exceptaion.what should i do thanks I have found a way . Like this: if (m_bFlg) { ShowWindow(SW_HIDE); m_bFlg = false; } add it into OnPaint(). May be there someother way, let's find it. "cyperus" <anonymous@discussions.microsoft.com> wrote in message news:B5FCB642-9078-441A-AF18-53091688E8FA@microsoft.com... > i want to hide my dialog when the dialog initilizes.i add this line in the function Mydialog::InitDialog(...

Can I Stop Users Attaching Files
Hi all Users are circumventing our documentation control by attaching drawing image files to emails. These files are in a folder to which they have only read-only access. Is it possible to stop users attaching the contents of certain folders or certain file types to emails? TIA Phil Sorry, forgot the important stuff in my frustration: Win2003 Server Exchange 2003 Outlook 2003 clients Standard MS client server active directory controlled network Phil most AV solutions have the ability to block sending/receiving of certain file types... -- Susan Conkey [MVP] "TheScullster...

Runtime error 429: Activex Component Can't create object
Dear helpers, Kindly guide me to resolve my vista related problem. I am running .exe file on Vista ultimate but it giving run time error "429: Activex Component Can't create object". The exe file works well on XP. May be it's a scripting error .... kindly advise what to do ro resolve it. Regards, Hina Naz -- Hina Naz ------------------------------------------------------------------------ Hina Naz's Profile: http://forums.techarena.in/members/226902.htm View this thread: http://forums.techarena.in/vista-help/1342465.htm http://forums.techarena.i...

how can i copy formula?
hi, please help! my problem was i create a formula that gets the total sum from another workbook. i want to copy the formula and past to another cell but the only thing must change is the name of the workbook. example. i want to paste the formula of A1 to B1 and so on which autoamtically link to another workbook. Workbook1. sheet1 A1=sum of workbook2 B1=sum of workbook3 C1=sum of workbook4 Hi Into A1 enter the formula like ="_=SUM('C:\My Documents\[Workbook" & COLUMN()+1 & ".xls]Sheet1'!A1:A100)" Copy the formula to right. Select all formulas, copy ...

can you add a day counter for a colum and row
trying to add a day counter so I can see how long an item has been in stock. It would need to be able to use 1 coloum and each row in that colum. Hi Depends how you have your data set up, but assuming date into stock is column A =IF(A1="","",TODAY()-A1) Format>Cell>Number>General Copy down as required -- Regards Roger Govier "poohgld" <poohgld@discussions.microsoft.com> wrote in message news:31454DD6-A5C5-40E3-A0E8-9F3827E73551@microsoft.com... > trying to add a day counter so I can see how long an item has been in > stock. > It wo...

how can I clear an apostrophy in front of number in excel
I know how to change formats - however I have a formatted cell for a 100 + rows that appear to have an apostrophy as a lead. While I don't see it, I am concluding that because changing formats to general or text is not removing the green box in corner and is not allowing me to do a vlookup using this column of info. try copying an unused empty cell. Select your data & edit/paste special, click Values & Add. "KCESS" <KCESS@discussions.microsoft.com> wrote in message news:B7DF4106-9C51-4FEF-9C9A-A4E95BB1FBE8@microsoft.com... > I know how to change formats - h...