Incremental Numbers

[dr4ke]

Dear all and HNY (it's my first post of 2013),



I currently report on 5 Mailbox sizes (below), where I have to show how many mailbox's fit into each category... easy enough!

MailboxSizes
.100 MB (104,857,600 bytes)
.1 GB (1,073,741,824 bytes)
.5 GB (5,368,709,120 bytes)
.50 GB (53,687,091,200 bytes)
.unlimited

Where things become difficult are there are mailbox's out in the estate which are 'in between' these values. For example; 1.92GB, 7GB, 2.34GB, 11GB etc. These are called 'incrementals' where somebody has a standard mailbox size, and then purchased an incremental '1GB'.

I therefore need to calculate how many '1GB incrementals' are being used; the way this is calculated in the examples given above are...

1.92GB is pushed up to the next GB (2GB) and is therefore a Standard 1GB mailbox plus 1 incremental GB.
7GB is already at a 'rounded' figure, so is calculated at a Standard 5GB mailbox, plus 2 incremental GB.
2.34GB is also pushed up to the next GB (3GB) and is therefore a Standard 1GB mailbox plus 2 incremental GB.
11GB is already at a 'rounded figure', so is calculated at a Standard 5GB mailbox, plus 6 incremental GB.

Incremental differences are always values of 1GB, so you could never have an 11GB mailbox as a Standard 5GB plus an incremental 5GB and an incremental 1GB... they are only incrementals of 1GB.. Always!

This, i'm sure you can imagine, is an EXTREMELY manual task.. Is there a way to build this into access to automate the process?

Any help appreciated...

[dr4ke]

To expand on what i'm after, i've just ran a simple query which outputs everything that is not a 'Standard Mailbox' Size (below) and provided what I would like as an output on each..

Is this possible (or in the words of Barney Stinson, How I Met Your Mother, Posimpable)?

prohibitsendreceivequota	Standard Mailbox Size	Number Of 1GB Incrementals
.1.098 GB (1,178,599,424 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.1.488 GB (1,598,029,824 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.1.098 GB (1,178,599,424 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.1.098 GB (1,178,599,424 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.1.488 GB (1,598,029,824 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.1.488 GB (1,598,029,824 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.2 GB (2,147,483,648 bytes)	1GB	1
.6 GB (6,442,450,944 bytes)	5GB	1
.7 GB (7,516,192,768 bytes)	5GB	2
.1.952 GB (2,096,103,424 bytes)	1GB	1
.3 GB (3,221,225,472 bytes)	1GB	2
.2 GB (2,147,483,648 bytes)	1GB	1
.6 GB (6,442,450,944 bytes)	5GB	1

[ssanfu]

Yes it is possible. I have a query that will return the standard size (1 GB or 5 GB) and the incremental GB.

But first, how are you getting the mailbox size into a table?

[dr4ke]

Its imported through the Excel import option. There a number of different columns imported, but this is the one i need manipulated. Does that answer what you needed?

[dr4ke]

For anybody who wishes to know I resolved this in the following way...

1) First I changed the sizes into workable numbers (e.g. .2 GB (2,147,483,648 bytes) becomes just '2')

 Mid([Allmailboxstats.prohibitsendreceivequota],2,InStrRev([allmailboxstats.prohibitsendreceivequota],"G")-3) AS SizeOfMailbox

2) Then I rounded this up to the next full number (it is always rounded up so I added the '+0.49')

 Round(SizeOfMailbox+0.49,0) AS RoundedFigure

3) After this, I worked out the Standard Mailbox size...

 IIf((RoundedFigure>0.99) And [RoundedFigure]<5,"1",IIf((RoundedFigure>4.99) And [RoundedFigure]<50,"5",IIf((RoundedFigure>49.99),"50","Error"))) AS StandardMBXSize

4) And then the number of 1GB Incrementals

 RoundedFigure-StandardMBXSize AS Numberof1GBIncrementals

Hopefully this helps somebody, if anybody has any questions or improvements; would happily have that discussion.

[ssanfu]

My apologies..
I have a query that took your example and does the conversion. I posted it but I don't see it now.

I will repost it tonight when I get home.

[ssanfu]

Here is the dB I made and the query:

[dr4ke]

Thanks SSanfu, unfortunately the table format you have used isn't the same that is imported into my DataBase but thank you very much for the help.

It seems as though my method works, but I do appreciate your assistance.