How do I approach this?

G

Grant Reid

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

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
 
C

count

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 said:
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

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
Click to expand...
 
E

Ed

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 said:
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

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
Click to expand...
 
S

SkipVought

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

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
Click to expand...
120 58
61 51.52 2004/04/05 14:47

tempdb data and log
Click to expand...
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
Click to expand...
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
Click to expand...
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

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


.
Click to expand...
 
G

Grant Reid

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.

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
 
S

SkipVought

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find then highlight in yellow 6
Autofil on variable rows, delete extract and show remaining rows 3
Too negative XIRR? 3
Break Calculation Macro 1
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
I need to help with Excel (beginner) -- year row and month column 2
Count the highest number of days without “errors” 2
Calculation of Overlap Outage Hours 17

Top