Adding lines in value field MS Query

[Slade2000]

Hi

How do i add lines in the value field with the criteria area in MS query. There are only 8 lines avalable

 

[Hermanito]

I guess you can't... I just checked and it seems a hard limit.

I must say though that I don't understand why you would need that many, I have never used more than 3 I think. Maybe there is an easier way to do things than what you are trying to do right now?

Also, I think if you edit the SQL manually, you can do pretty much anything, but often you cannot show it in the normal Access-style view anymore afterwards, you'll get some error like 'query cannot be shown in the query editor'.

 

[Slade2000]

My database is for alerts from servers and software and have more that 1300 servers in my alert space for different departments and i query the servers from each department for reporting so i must enter each one of them to get them seperated in my stats.

 

[Hermanito]

So you connect to 1300 servers to query data, and gather it all in one Excel 'database'?

I still don't understand why you would need more criteria values then... even if you could add more than 8, you would never be able to use 1300. There is also a length-limit for an SQL statement and I'm sure 1300 conditions would violate that...

 

[Slade2000]

How we do it is. We have one SQL database. Each server has an app the monitor the server. If an alert comes up(This can be, space, cpu, memory, application error, failed backup and so on) it sends a message to the database with diffrent fields of information for instance, Server name, summery, app, contact. Now all the servers are for different departments that generate their own alerts. I know that server a-z are for department red and server a1-za for department blue and so on. Not i want to query the sql database, lets say from date xx-xx-xx to date yy-yy-yy within table with server names, a-z and in summery for "space" then i will get my data that tells me that servers c,d,e,f,g had pace problems and then i know it is in department red because i specified only server name in the department.

Does it make sense?

At the moment i use SQuirreL SQL Client and it works fien but must copy and paste everyting then to excel and it takes forever so if i do it with ms i can automate it.

 

[Hermanito]

Yes, I'm beginning to see it, I think

I think a better way to go, would be like this:
- create a new table in your database with the following fields: ID (auto-inc), ServerName (varchar(?)) and Department (varchar(?)).
- create your queries using this new table as well, thus having no need to list all servers you want to query, just query by department

Your current method would invalidate all your queries if a server were to move from one department to another, or if a department adds a server, etcetera. In my approach, you can simply maintain the table with the Server-Department links and all queries remain valid...

 

[Slade2000]

Understand 100%,
1. The problem is that the database comes standard with the alerting application and if it is altered it will screw the application.

2. The application comes with a dashboard that can be monitored live for alerts and uses the same database and sometime they query for all space, cpu or what ever type of alert accured over all diffrent departments.

 

[Hermanito]

Adding a table should not mess up anything that uses the database, it would only be used by your queries...

I assume you are trying to add conditions per server you want to include, so, one value in the criteria of the server-column, right?
Maybe you can try the IN() function instead to combine multiple servernames per valueline... if you do this for all 8 lines, you should be able to use a reasonably big list of servernames to match against:

example condition: IN ('ServerName1';'ServerName2';'ServerName3')

I just hope you don't run into the SQL-statement length limitation, no idea how much it is exactly...

 

[Slade2000]

In() is exactly how i use it. Cell size is only 255 characters. It works fine with sql query software but cant get to work with ms. else i will just do seperate ones and run with single macro.