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

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" <(E-Mail Removed)> napisa³ w wiadomo¶ci
news:%23$(E-Mail Removed)...
> 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
>
>

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,INDEX(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" <(E-Mail Removed)> wrote in message
news:%23$(E-Mail Removed)...
> 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
>
>

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
>
>
>.
>

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

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
>
>
>
>.
>