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