Automate Query Creation

[dr4ke]

Hi, I currently have a list of 159 servers which we will call 'Server' and then their respective number (E.g. Server1, Server2, Server3 --> Server159). On the same table I have the software that is installed on the server (e.g. below). What I would like to do is split these servers, and their software packages, into different queries (so i'd essentially have 159 queries); however I, for obvious reasons, don't want to manually create that many queries. Is there a way to automate the creation of these queries, based on the 'Server Name' column?

Server Name	Software Name
Server1	Software1
Server1	Software2
Server2	Software1
Server2	Software2
Server2	Software3

[pbaldy]

Generally you'd have 1 parameter query, not 159 fixed queries. I'd create a form for the user to enter the desired server, and have your query get its criteria from there.

[dr4ke]

That is one of the things I considered however was really hoping to do certain calculations on them individually to then report on them (E.G. First & Last queries), which would mean that I'd have to run the parameter query 159 times, which if I were to do this every month ... would be a huge amout of time.

[pbaldy]

I'm not clear on why you'd have to run it 159 times, but it would be relatively simple to have code loop through and do that. Maybe if you clarified your needs we could suggest a better solution.

[dr4ke]

It would be useful if you could help me develop such a code, as i would be able to use this across a few of my databases which are currently in production. If you'd be willing to help me with that i'd be really greatful?

[orange]

Others are here and will offer help, as Paul has, but you'll have to tell us what exactly you are tryiing to d so we can all understand.

[pbaldy]

Develop what code? Looping code is fairly simple:

http://www.granite.ab.ca/access/email/recordsetloop.htm

[dr4ke]

I've tried to explain as best I can...

I currently have two peices of code:

 SELECT First(HighLevelCapacity.Used) AS StartDateUsed, Last(HighLevelCapacity.Used) AS EndDateUsed, First(HighLevelCapacity.Total) AS StartDateTotal, Last(HighLevelCapacity.Total) AS EndDateTotal, StartDateTotal-StartDateUsed AS StartDateFree, EndDateTotal-EndDateUsed AS EndDateFree, (StartDateUsed/StartDateTotal)*100 AS StartDatePercentage, (EndDateUsed/EndDateTotal)*100 AS EndDatePercentage, StartDateUsed-EndDateUsed AS UsedDiff, StartDateTotal-EndDateTotal AS TotalDiff, EndDateFree-StartDateFree AS FreeDiff, StartDatePercentage-EndDatePercentage AS PercentageDiff, First(HighLevelCapacity.Date) AS StartDate, Last(HighLevelCapacity.Date) AS EndDate
FROM HighLevelCapacity
WHERE (((HighLevelCapacity.SANName)="Server1") AND ((HighLevelCapacity.AggregateName)="Aggregate1")) AND ((HighLevelCapacity.Date) Between (Forms!ReportDataRangeSelect!SelectStart) And (Forms!ReportDataRangeSelect!SelectEnd));

 SELECT TOP 1 HighLevelCapacity.SANName, HighLevelCapacity.AggregateName, Int(Round(([Query1].StartDateUsed)/1024^2,1)) AS FirstUsedGB, Int(Round(([Query1].EndDateUsed)/1024^2,1)) AS LastUsedGB, Int(Round(([Query1].StartDateTotal)/1024^2,1)) AS FirstTotalGB, Int(Round(([Query1].EndDateTotal)/1024^2,1)) AS LastTotalGB, Int(Round(([Query1].StartDateFree)/1024^2,1)) AS FirstFreeGB, Int(Round(([Query1].EndDateFree)/1024^2,1)) AS LastFreeGB, Int(Round([Query1].StartDatePercentage,1)) AS FirstPercentage, Int(Round([Query1].EndDatePercentage,1)) AS LastPercentage, FirstUsedGB-LastUsedGB AS UsedDiff, FirstTotalGB-LastTotalGB AS TotalDiff, FirstFreeGB-LastFreeGB AS FreeDiff, FirstPercentage-LastPercentage AS PercentageDiff, DateDiff("d",(Forms![ReportDataRangeSelect]![SelectStart]),(Forms![ReportDataRangeSelect]![SelectEnd])) AS DateDiff, Int(Round((UsedDiff)/(DateDiff),1)) AS DailyIncreaseGB, IIf([DailyIncreaseGB]=0,'No Change',IIf([DailyIncreaseGB]>0,'Decrease',INT(Round(LastFreeGB/IIf(DailyIncreaseGB=0,'Null',DailyIncreaseGB),1)))) AS TimeLeft
FROM Query1, HighLevelCapacity
WHERE (((HighLevelCapacity.SANName)="Server1") AND ((HighLevelCapacity.AggregateName)="Aggregate1"));

Now the code is written, I just need to replicate it for the relevant servers. So the code will stay exactly the same, and only the text in Red will change (depending on what server/aggregate combination it is, and what the name of the first query is saved as). The combinations will be...

Server1 - Aggregate1
Server1 - Aggregate2
Server1 - Aggregate3
Server1 - Aggregate4
Server2 - Aggregate1
Server2 - Aggregate2

And so on and so forth.... For each combination, it will need the above two queries; and obviously the query1 name in the code will have to be changed every time. Doing this for 4 servers, actually only means about 30 queries, so isn't that time constrainting, but I soon have to do it with a further 45 different combinations. So if I can learn how to do it on this, i'm hoping to be able to adapt it to that afterwards.

Hope that explains things a little better?

Pbaldy - apologies, the link doesnt make much sense to me.

Thanks

[dr4ke]

I've just amended the second code as below. I removed all the '[Query1].' parameters, so that only the WHERE and FROM clauses need to be amended. I'm aware that I'd need to make sure that the columns in the Table and the queries do not have the same headings, as this could cause confusion in the below...

 SELECT TOP 1 HighLevelCapacity.SANName, HighLevelCapacity.AggregateName, Int(Round((StartDateUsed)/1024^2,1)) AS FirstUsedGB, Int(Round((EndDateUsed)/1024^2,1)) AS LastUsedGB, Int(Round((StartDateTotal)/1024^2,1)) AS FirstTotalGB, Int(Round((EndDateTotal)/1024^2,1)) AS LastTotalGB, Int(Round((StartDateFree)/1024^2,1)) AS FirstFreeGB, Int(Round((EndDateFree)/1024^2,1)) AS LastFreeGB, Int(Round(StartDatePercentage,1)) AS FirstPercentage, Int(Round(EndDatePercentage,1)) AS LastPercentage, FirstUsedGB-LastUsedGB AS UsedDiff, FirstTotalGB-LastTotalGB AS TotalDiff, FirstFreeGB-LastFreeGB AS FreeDiff, FirstPercentage-LastPercentage AS PercentageDiff, DateDiff("d",(Forms![ReportDataRangeSelect]![SelectStart]),(Forms![ReportDataRangeSelect]![SelectEnd])) AS DateDiff, Int(Round((UsedDiff)/(DateDiff),1)) AS DailyIncreaseGB, IIf([DailyIncreaseGB]=0,'No Change',IIf([DailyIncreaseGB]>0,'Decrease',INT(Round(LastFreeGB/IIf(DailyIncreaseGB=0,'Null',DailyIncreaseGB),1)))) AS TimeLeft
FROM Query1, HighLevelCapacity
WHERE (((HighLevelCapacity.SANName)="Server1") AND ((HighLevelCapacity.AggregateName)="Aggregate1"));