Solved: Run Queries automatically

[EricM]

I would like to do the following from an excel spreadsheet.

I would like to filter the database by date range. ( this needs to be user input)
next I need to run a query to delete the two right most characters. (This is already setup in db)
next I need to run a third query that takes the data and give a average. (This is done) then put the results in the proper cells. ( This can be done manually if I can get it to show me the results of this query since there are 6 results that would show up and would go into 6 different cells each month.)
last save the filter as a new db in mmyy format. (0505.mdb) and delete all records in the old database.

Attached is the db and spreadsheet.

[Ken Puls]

Attached is the db and spreadsheet.

Psst! No it isn't... ;)

[EricM]

Yeah i noticed that when I got home. I will try to reattach it in the morning. Is there a size linit because the file was about 6 meg zipped

[xCav8r]

In the meantime, a few questions that might help speed things along...


I would like to filter the database by date range. ( this needs to be user input)

I assume you mean filter a recordset in a database by date range. Have you already set this up in Excel? If not, is this something that a user can input into a sheet, or does the range need to be obtained in a different way (eg., UserForm, InputBox, etc.)?


last save the filter as a new db in mmyy format. (0505.mdb) and delete all records in the old database.

This is somewhat confusing for me. Usually filters are saved as queries which are objects of an Access database. Do you perhaps mean to save the results of the query to a new table (in a new or existing database)?

Also, I assume there is a good reason (security/confidentiality/etc) for deleting all records in an old database while creating a new one with a subset of the previously deleted records. I only mention it because it seems a bit odd.

[Ken Puls]

Yeah i noticed that when I got home. I will try to reattach it in the morning. Is there a size linit because the file was about 6 meg zipped

You know, I'm actually not sure. I would say that there probably is though.

A couple of options for you then:
-If you have your own web server, maybe upload it there and post a link
-Offer to email it to anyone who needs it (exchange your email via PM though. Posting it could attract Spam)
-Break it up into smaller chunks if possible and post separately. (Excel file one post, Access DB another)
-Get rid of all non relevant parts to make the file smaller

HTH,

[EricM]

In the meantime, a few questions that might help speed things along...



I assume you mean filter a recordset in a database by date range. Have you already set this up in Excel? If not, is this something that a user can input into a sheet, or does the range need to be obtained in a different way (eg., UserForm, InputBox, etc.)?
Yes filter the record set and the date range can be obtained in any manner. Whether user input into a sheet or any of the other ways mentioned. (I would like a UserForm but it is not imparitive.


This is somewhat confusing for me. Usually filters are saved as queries which are objects of an Access database. Do you perhaps mean to save the results of the query to a new table (in a new or existing database)?

Also, I assume there is a good reason (security/confidentiality/etc) for deleting all records in an old database while creating a new one with a subset of the previously deleted records. I only mention it because it seems a bit odd.

The reason for this is because within a 30 day span this access database will grow to over 150 meg.

Basicly here is what happens.
I use a program that monitors network devices and reports them to a log (db file) Once a month I have to report the metrics on the monitoring. To do this I have to run the 2 quieries you will find in the db. I then take the results and I type them into the spreadsheet. What I would like to do is to be able to just open the spreadsheet and be able to run the queries and have the results popup so I can type them in.

as for the part about saving the file to a different db is because I need to clear the original records from the original db in order for the metrics to be accurate. The db is in consant use so I cannot just rename it and create a new one so I am forced to clear the records every month. The creating a new db with the is just for archiving purposes. Does this make sense or should I start over? Cause even I get a bit confussed reading it :-):bug:

[xCav8r]

Alright, this is a bit involved for me to look at while at work. If someone else doesn't address it by this evening, I'll help you out when I get home.

[xCav8r]

I think I'm going to need a better understanding of how you're currently using what I see and what you'd like it to do. Here's what I've gathered so far...

An application stores data in an access database.
You use an UPDATE SET to remove tabs from hostmon.aliveratio and hostmon.deadratio.
You use an aggregate query to determine the arithmetic mean of hostmon.aliveratio for each hostmon.testmethod.
You use the results of last query to type stuff in an Excel spreadsheet. I see six rows in the results, but the data doesn't match what I see in the spreadsheet, so I assume the spreadsheet's data for May is based on last month's data in the database. I don't know what matches to what, but I don't know that I need to know either.
I'm a little fuzzy on the rest. I think you want to delete old data based on the date.
Here are some things I think you want to know...

You can run queries from Excel without opening Access. You could do so via DAO or ADO in Excel modules, or you could invoke Access routines that use DAO or ADO from Excel modules.
You can populate cells in your spreadhsheet based on those queries without getting into Access.
It isn't necessary as a matter of procedure to delete old data because you can't rename the file. Although it doesn't seem too important that you keep the data for archival purposes, if you needed the data, there are a variety of ways--not all of them good with Access--in which you could maintain your current system without deleting the records.
You could create something in Access to sort of replace what you're doing in Excel, but it doesn't seem like copying and pasting into a spreadsheet takes a lot of time, so it may not be worth the effort, especially since it looks like you put together information from sources other than just the Access db you provided.

[EricM]

I think I'm going to need a better understanding of how you're currently using what I see and what you'd like it to do. Here's what I've gathered so far...

An application stores data in an access database.
You use an UPDATE SET to remove tabs from hostmon.aliveratio and hostmon.deadratio.
You use an aggregate query to determine the arithmetic mean of hostmon.aliveratio for each hostmon.testmethod.
You use the results of last query to type stuff in an Excel spreadsheet. I see six rows in the results, but the data doesn't match what I see in the spreadsheet, so I assume the spreadsheet's data for May is based on last month's data in the database. I don't know what matches to what, but I don't know that I need to know either.
I'm a little fuzzy on the rest. I think you want to delete old data based on the date.



1. Correct
2. Correct
3. Correct
4. Check Service = Network Printers
CPU Usage = All Servers
Text Log = Successful MRP
Trace Test = Wan Availibility (Combine both to 1 average)
Traffic WAN Utilization
5. Yes what happens as stated above the file size grows real fast and it takes longer and longer to process the information. So ultimately I would like to just delete the data after I get my infromation. The reason for the date was because I do not always get the opportunity to run this at every month end. So If I am a week into the next month I would like to be able to keep that data. (Does this make sense?)



Here are some things I think you want to know...

You can run queries from Excel without opening Access. You could do so via DAO or ADO in Excel modules, or you could invoke Access routines that use DAO or ADO from Excel modules.
You can populate cells in your spreadhsheet based on those queries without getting into Access.
It isn't necessary as a matter of procedure to delete old data because you can't rename the file. Although it doesn't seem too important that you keep the data for archival purposes, if you needed the data, there are a variety of ways--not all of them good with Access--in which you could maintain your current system without deleting the records.
You could create something in Access to sort of replace what you're doing in Excel, but it doesn't seem like copying and pasting into a spreadsheet takes a lot of time, so it may not be worth the effort, especially since it looks like you put together information from sources other than just the Access db you provided.

1 and 2
I am aware of but unsure how. I know how to get the sql statements from access in the query design mode but not sure how to get excel to connect to the db that resides on a seperate server.

3. I am not really concerned about archiving once I get the data. That would be just a bonus but not worth to much effort as long as I can complete my answer to #5 above.
4. exactly. It only takes a couple seconds to put it into excel after I get the info. If needed there can be a seperate worksheet added to the excel where it can give the results in cells and then I can just copy them to the correct cells in the metrics worksheet.

[xCav8r]

ADO example of connecting to Access db from Excel: http://www.exceltip.com/st/Import_data_from_Access_to_Excel_(ADO)_using_VBA_in_Microsoft_Excel/427.html

[EricM]

Thanks Alot. I hope to someday return the favor. That worked.

[xCav8r]

Cool. Very cool, indeed, Mr. .adp. :)