Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I approach this?
Hi
I hope someone can assist in getting my head around this problem. On worksheet "Server 2 Database Space Summary" I have a QueryTable (lets call this Q1) containing the following data DB MB ALLOCATED MB USED MB FREE % FREE DB CREATED BIDemo data only 20 12 7 37.44 2004/04/21 09:30 BIDemo log only 20 0 19 99.75 2004/04/21 09:30 master data and log 6 4 1 23.14 2004/04/05 14:46 model data and log 2 1 0 40.42 2004/04/05 14:46 sybmgmtdb data only 45 10 34 76.71 2004/04/06 14:13 sybmgmtdb log only 5 0 4 99.57 2004/04/06 14:13 sybsystemdb data and log 2 1 0 35.05 2004/04/05 14:46 sybsystemprocs data and log 120 58 61 51.52 2004/04/05 14:47 tempdb data and log 3 1 1 59.17 2004/05/04 16:22 Q1 will be refreshed every day. As you can see the data represents data and log usage for a particular data server, and the possibility exists that databases may be added or deleted. On worksheet "Server 2 Projection Seed Values" I have QueryTable (lets call this Q2) containing the following data (a subset of Q2's data) DB MB USED DB CREATED BIDemo data only 12 2004/04/21 09:30 BIDemo log only 0 2004/04/21 09:30 Master data and log 4 2004/04/05 14:46 model data and log 1 2004/04/05 14:46 Sybmgmtdb data only 10 2004/04/06 14:13 Sybmgmtdb log only 0 2004/04/06 14:13 Sybsystemdb data and log 1 2004/04/05 14:46 Sybsystemprocs data and log 58 2004/04/05 14:47 Tempdb data and log 1 2004/05/04 16:22 Q2 will be populated only once and will be used as a baseline for making database growth projections. What I need to accomplish here is; [a] If an entry exists in Q1 that does not exist in Q2, it must be added to Q2 [b] If an entry exists in Q2 that does not exist in Q1, it must be deleted from Q2 Hope someone can help. Kind Regards - Grant |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I approach this?
Hi Grant,
From what you said Q2 is a phantom - it really is a subset of Q1. Try hiding (or narrowing) columns 2, 3 & 4 in Q1 and see if this will do you. Hth Paul Użytkownik "Grant Reid" napisał w wiadomo¶ci ... Hi I hope someone can assist in getting my head around this problem. On worksheet "Server 2 Database Space Summary" I have a QueryTable (lets call this Q1) containing the following data DB MB ALLOCATED MB USED MB FREE % FREE DB CREATED BIDemo data only 20 12 7 37.44 2004/04/21 09:30 BIDemo log only 20 0 19 99.75 2004/04/21 09:30 master data and log 6 4 1 23.14 2004/04/05 14:46 model data and log 2 1 0 40.42 2004/04/05 14:46 sybmgmtdb data only 45 10 34 76.71 2004/04/06 14:13 sybmgmtdb log only 5 0 4 99.57 2004/04/06 14:13 sybsystemdb data and log 2 1 0 35.05 2004/04/05 14:46 sybsystemprocs data and log 120 58 61 51.52 2004/04/05 14:47 tempdb data and log 3 1 1 59.17 2004/05/04 16:22 Q1 will be refreshed every day. As you can see the data represents data and log usage for a particular data server, and the possibility exists that databases may be added or deleted. On worksheet "Server 2 Projection Seed Values" I have QueryTable (lets call this Q2) containing the following data (a subset of Q2's data) DB MB USED DB CREATED BIDemo data only 12 2004/04/21 09:30 BIDemo log only 0 2004/04/21 09:30 Master data and log 4 2004/04/05 14:46 model data and log 1 2004/04/05 14:46 Sybmgmtdb data only 10 2004/04/06 14:13 Sybmgmtdb log only 0 2004/04/06 14:13 Sybsystemdb data and log 1 2004/04/05 14:46 Sybsystemprocs data and log 58 2004/04/05 14:47 Tempdb data and log 1 2004/05/04 16:22 Q2 will be populated only once and will be used as a baseline for making database growth projections. What I need to accomplish here is; [a] If an entry exists in Q1 that does not exist in Q2, it must be added to Q2 [b] If an entry exists in Q2 that does not exist in Q1, it must be deleted from Q2 Hope someone can help. Kind Regards - Grant |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I approach this?
Hi, Grant. I do something of the same sort - I have a large workbook I
update every week, and have to take notes that match a particular record in the current book and move them into the correct position on the update. I create a new workbook and copy my current data onto Sheet2 (renamed "TIRs" for my formula) and my new data onto Sheet1. My notes are in columns V-AA. I use VBA to set a formula in the columns on Sheet1 (new data) where the note is to appear: ActiveCell.Formula = "=IF(ISERROR(MATCH($C2,TIRs!$C$2:$C$15000,0)),0,IN DEX(TIRs!X$2:X$15000,MATCH ($C2,TIRs!$C$2:$C$15000,0)))" and then run it down the whole column: ' Finds the last entry in Col A for the fill-down wb3.Activate wb3.Sheets(1).Select LastRow = wb3.Sheets(1).Range("A65536").End(xlUp).Row ' Runs the formulas in the last 5 columns down to the bottom With wb3.Sheets("Sheet1") .Range("V2:V" & LastRow).FillDown .Range("W2:W" & LastRow).FillDown .Range("X2:X" & LastRow).FillDown .Range("Y2:Y" & LastRow).FillDown .Range("Z2:Z" & LastRow).FillDown .Range("AA2:AA" & LastRow).FillDown .Range("AB2:AB" & LastRow).FillDown End With Cells.Select Calculate The formula compares my report number in Col. C on both sheets; if the number is the same, then the note on Sheet2 is pulled forward onto Sheet1. I hope there's something here you can use. Ed "Grant Reid" wrote in message ... Hi I hope someone can assist in getting my head around this problem. On worksheet "Server 2 Database Space Summary" I have a QueryTable (lets call this Q1) containing the following data DB MB ALLOCATED MB USED MB FREE % FREE DB CREATED BIDemo data only 20 12 7 37.44 2004/04/21 09:30 BIDemo log only 20 0 19 99.75 2004/04/21 09:30 master data and log 6 4 1 23.14 2004/04/05 14:46 model data and log 2 1 0 40.42 2004/04/05 14:46 sybmgmtdb data only 45 10 34 76.71 2004/04/06 14:13 sybmgmtdb log only 5 0 4 99.57 2004/04/06 14:13 sybsystemdb data and log 2 1 0 35.05 2004/04/05 14:46 sybsystemprocs data and log 120 58 61 51.52 2004/04/05 14:47 tempdb data and log 3 1 1 59.17 2004/05/04 16:22 Q1 will be refreshed every day. As you can see the data represents data and log usage for a particular data server, and the possibility exists that databases may be added or deleted. On worksheet "Server 2 Projection Seed Values" I have QueryTable (lets call this Q2) containing the following data (a subset of Q2's data) DB MB USED DB CREATED BIDemo data only 12 2004/04/21 09:30 BIDemo log only 0 2004/04/21 09:30 Master data and log 4 2004/04/05 14:46 model data and log 1 2004/04/05 14:46 Sybmgmtdb data only 10 2004/04/06 14:13 Sybmgmtdb log only 0 2004/04/06 14:13 Sybsystemdb data and log 1 2004/04/05 14:46 Sybsystemprocs data and log 58 2004/04/05 14:47 Tempdb data and log 1 2004/05/04 16:22 Q2 will be populated only once and will be used as a baseline for making database growth projections. What I need to accomplish here is; [a] If an entry exists in Q1 that does not exist in Q2, it must be added to Q2 [b] If an entry exists in Q2 that does not exist in Q1, it must be deleted from Q2 Hope someone can help. Kind Regards - Grant |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I approach this?
You stated...
"Q2 will be populated only once and will be used as a baseline for making database growth projections." Then you stated... "[a] If an entry exists in Q1 that does not exist in Q2, it must be added to Q2 [b] If an entry exists in Q2 that does not exist in Q1, it must be deleted from Q2" This seems to be contradictory since you stated that "Q2 will be populated only once". Could you please explain? -----Original Message----- Hi I hope someone can assist in getting my head around this problem. On worksheet "Server 2 Database Space Summary" I have a QueryTable (lets call this Q1) containing the following data DB MB ALLOCATED MB USED MB FREE % FREE DB CREATED BIDemo data only 20 12 7 37.44 2004/04/21 09:30 BIDemo log only 20 0 19 99.75 2004/04/21 09:30 master data and log 6 4 1 23.14 2004/04/05 14:46 model data and log 2 1 0 40.42 2004/04/05 14:46 sybmgmtdb data only 45 10 34 76.71 2004/04/06 14:13 sybmgmtdb log only 5 0 4 99.57 2004/04/06 14:13 sybsystemdb data and log 2 1 0 35.05 2004/04/05 14:46 sybsystemprocs data and log 120 58 61 51.52 2004/04/05 14:47 tempdb data and log 3 1 1 59.17 2004/05/04 16:22 Q1 will be refreshed every day. As you can see the data represents data and log usage for a particular data server, and the possibility exists that databases may be added or deleted. On worksheet "Server 2 Projection Seed Values" I have QueryTable (lets call this Q2) containing the following data (a subset of Q2's data) DB MB USED DB CREATED BIDemo data only 12 2004/04/21 09:30 BIDemo log only 0 2004/04/21 09:30 Master data and log 4 2004/04/05 14:46 model data and log 1 2004/04/05 14:46 Sybmgmtdb data only 10 2004/04/06 14:13 Sybmgmtdb log only 0 2004/04/06 14:13 Sybsystemdb data and log 1 2004/04/05 14:46 Sybsystemprocs data and log 58 2004/04/05 14:47 Tempdb data and log 1 2004/05/04 16:22 Q2 will be populated only once and will be used as a baseline for making database growth projections. What I need to accomplish here is; [a] If an entry exists in Q1 that does not exist in Q2, it must be added to Q2 [b] If an entry exists in Q2 that does not exist in Q1, it must be deleted from Q2 Hope someone can help. Kind Regards - Grant . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I approach this?
Hi
OK, perhaps I wasn't very clear. I'll try again ;-) The workbook is intended to be used by DBA's when they visit clients for DBA services. It hooks into their data servers retrieving data from the system tables. One of the requirements from our clients is that we provide them with projected growth of their databases so that pro-active measures can be taken. The only way this can be done is to have a baseline/initial measurement in Q2 that you can compare the more recent data in Q1 against. Q2 contains the baseline/initial values. I will only ever query Q2 once. Q1 will query the data server on a regular basis. Over time new databases will be added and some will be dropped. This will be reflected in Q1. So...... [a] If a database is dropped it will not show up in Q1. It is no longer required in Q2 for projection purposes and should be programatically removed from Q2. [b] If a new database is added it will show up in Q1. It does not exist in Q2 and therefore it must be programatically added to Q2. Hope this clears thing up a little. Regards - Grant |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I approach this?
I understand now.
I would use Data/Get External Data - Excel Files to query Q1 & q2 in various ways to find In Q1 and not in Q2 and In Q2 and Not in Q1 and use those results to update Q2. Select Q1.DB From Q1 Where Q1.DB Not In (Select Q2.DB From Q2) Select Q2.DB From Q2 Where Q2.DB Not In (Select Q1.DB From Q1) SkipVought -----Original Message----- Hi OK, perhaps I wasn't very clear. I'll try again ;-) The workbook is intended to be used by DBA's when they visit clients for DBA services. It hooks into their data servers retrieving data from the system tables. One of the requirements from our clients is that we provide them with projected growth of their databases so that pro-active measures can be taken. The only way this can be done is to have a baseline/initial measurement in Q2 that you can compare the more recent data in Q1 against. Q2 contains the baseline/initial values. I will only ever query Q2 once. Q1 will query the data server on a regular basis. Over time new databases will be added and some will be dropped. This will be reflected in Q1. So...... [a] If a database is dropped it will not show up in Q1. It is no longer required in Q2 for projection purposes and should be programatically removed from Q2. [b] If a new database is added it will show up in Q1. It does not exist in Q2 and therefore it must be programatically added to Q2. Hope this clears thing up a little. Regards - Grant . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What approach should I adopt ..... | Excel Discussion (Misc queries) | |||
Approach to Excel | Excel Discussion (Misc queries) | |||
What is the right approach? | Excel Worksheet Functions | |||
How to approach this? | Excel Discussion (Misc queries) | |||
Need new approach on forms I set up | Excel Programming |