Unable to calculate multiple fields from multiple tables. Help!

[Tonij (with a J)]

Hi all,

I have an Access 2003 database that has been a work in progress for a
while, it's basically a
system inventory and issue tracking system. Recently I have added a
seperate area for keeping

track of total disk space allocation and usage. For the most part I
have the data keyed in but

am pulling my hair out trying to get some calculations.

The main table is very simple, it consists of these fields:

MD - Meta device
MDSize - the size of the meta device

There are only two MDs so far, D60 and D61. Volumes are built on top
of these devices and are

also numbered, i.e. 70, 71, 72, etc. The volumes themselves are in
another table with these

fields:

MD - Meta device upon which this volume is built (this links to MD in
the first table)
Size - size of this volume

So I have MD D60 which houses 70, 71, 71, etc and MD D61 which houses
80, 81, 82, etc.

What I am trying to do is create a form that will keep a running total
of how much is in use and

how much is available. The easy part is the total, that is defined
within "MDSize" of the first

table, that is a static number that will not change. I have done
simple calulations before

within forms using expressions, but this one is throwing me for a
loop. Mainly because I have

to seperate the two MDs, and further break them down by the volumes
built on those MDs.

This is what I envision:

MD D60: Total = 125 Total available = ?
MD D61: Total = 125 Total available = ?

The total available has to be the sum of Size in the second table, but
not the total for ALL mds,

it has to be broken out by each md, i.e.

D60: Total used = 90
D61: Total used = 50

It's easy enough to do a sum on a field, but for the life of me I
cannot get the syntax right to

sum a field based on a given criteria, in this case twice: once for
D60 and once for D61

Then to make things more complicated, once I have obtained this sum, I
need to subtract it from

the MDSize in the first table, again having two seperate calculations.

If this makes any sense to anyone that can offer assistnace, please
let me know! I realize it may

be hard to understand based on my rambling but I will try to answer
any specific questions.

Thanks in advance!

[EyeHaveNoName]

Ok, looking back at this post it looks like a complicated mess! I
managed to get some more work done and I am close but not quite
there. This expression:

=IIf([MD]="D60",Sum([Size]))

*Almost* gives what I need: it searches the [MD] field for the value
"D60" and returns the sum.

Now I just need it to return only the sum of the field [MD] that
contains D60, as it is now it returns ALL of the values...

How can I isolate it so I see the sum of [Size] only where [MD]
equals D60?

[paii, Ron]

"EyeHaveNoName" <Ey***********@gmail.comwrote in message
news:11**********************@h2g2000hsg.googlegro ups.com...

Ok, looking back at this post it looks like a complicated mess! I
managed to get some more work done and I am close but not quite
there. This expression:

=IIf([MD]="D60",Sum([Size]))

*Almost* gives what I need: it searches the [MD] field for the value
"D60" and returns the sum.

Now I just need it to return only the sum of the field [MD] that
contains D60, as it is now it returns ALL of the values...

How can I isolate it so I see the sum of [Size] only where [MD]
equals D60?

=Sum(IIf([MD]="D60",[Size],0)

[EyeHaveNoName]

On May 18, 9:06 am, "paii, Ron" <[e-mail]:

How can I isolate it so I see the sum of [Size] only where [MD]
equals D60?

=Sum(IIf([MD]="D60",[Size],0)

Thank you! That is perfect.