automatically choosing the right exchange server

[lonesoac0]

Hello all,



I am writing an access application to help with account creation at my work. I am having an issue in getting the exchange server auto selection to work. How I have things setup is that I have most of my code in the After Insert. So after the basic information is input more information is generated based off of the original information and placed into a separate table. We place our user mailboxes on servers with the most space. An example that we have are servers named Atlanta01, Atlanta02, Atlanta03, Knoxville01, Knoxville02, and Knoxville03. So the first thing that I must determine is to select the proper location pool like Knoxville. Then out of that pool I need to select the server with the highest available space. Any assistance would be greatly appreciated.

[lonesoac0]

So, I had a guy look at my situation and he wrote me some code and said buh-bye before I could get it working. Anyone know how to decifer this?

select substring(name,1,6) name, max(avaiblenewmailboxspace) avaiblenewmailboxspace from dailyreport
group by name

[pbaldy]

The substring function is the SQL Server equivalent of the Mid() function in Access. In this usage, it will return the first 6 characters in the Name field. Basically that's SQL that will return the largest value in the avaiblenewmailboxspace field for each unique instance of the first 6 characters in the name field. The intent appears to be ignoring the numeric digits at the end, but guessing that there will be at least 6 non-numeric characters and that the first 6 characters won't duplicate (like Atlanta and Atlantic would).