largeselection
Active Member
- Joined
- Aug 4, 2008
- Messages
- 358
This is a bit complicated...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o></o>
Basically I have recaps (excel based reports) which run every wednesday. They are typically approx 1000 rows x 70 columns. One of the columns is usually "ID#" which is common across most of the reports. What I have started doing is saving them as CSV files into a main folder. I have used Excel to "connect to an external data source" using the text driver and connecting to that main folder. That way I can run queries and define relationships pulling information from all the different text reports in the folder without having to do a lot of lookups. <o></o>
<o> </o>
The issue is that there are a lot of reports to sift through and select fields from when trying to do analysis. So what I am attempting to do is create a front end which is simpler and more graphical. <o></o>
<o> </o>
So far I have created the beginnings of a userform in excel and ultimately the goal would be that I could select things on the userform, have it translate the selections into SQL which I can then pass to MS Query and then have the resultant data set returned back into Excel. <o></o>
<o> </o>
The reports are named with the same convention:<o></o>
REPORT#_Area#_Month#Week#_Year#<o></o>
<o> </o>
The fields in the reports are also similarly named:<o></o>
Time Period (weekly, monthly, quarterly, etc)_ Field_Unit of measurement($, %, etc)_Qualifier(TY, NY)<o></o>
<o> </o>
<o> </o>
This is my thought process and what I am looking for is suggestions since as I am doing this I keep thinking that I’m sure there is a more elegant way to get this done…<o></o>
<o> </o>
In the userform I have some comboboxes which have the report fields<o></o>
1- Month<o></o>
2- Week<o></o>
3- Year<o></o>
4- Report#<o></o>
5- Area#<o></o>
<o> </o>
So far I have it so that I can select which month week etc info and then it will populate an excel sheet with the selected info when I click a button. This process allows me to add the first parts of the SQL statements.<o></o>
<o> </o>
My SQL statements are usually:<o></o>
SELECT RPT155_A10_MarWk1_2010.’WEEKLY_BIKETRAVEL_%_TY’<o></o>
FROM ‘<MAIN FOLDER LOCATION>’\RPT155_A10_MarWk1_2010.csv RPT155_A10_MarWk1_2010<o></o>
WHERE RPT155_A10_Marwk1_2010.’ID#’ = RPT155_A10_Aprwk5_2010.’ID#’<o></o>
<o> </o>
With my userform passing the selections to the excel worksheet I can build the “RPT155_A10_MarWk1_2010” part. And then string many of them together which I will ultimately pass through to MS Query. The problem is that when I get to trying to add the second half of the statement (the fields) there are tooo many! I was originally planning on using a section of checkboxes in the userform and then pass through the checked boxes values to the excel sheet, but that takes too much memory in excel. <o></o>
<o> </o>
So does this sound as crazy as I think it is? Is there a better way of trying to simplify pulling information from a quasi relational database constructed of text files in a folder? Any suggestions on how I can have the available fields/reports populate based on the reports in the main folder? <o></o>
<o> </o>
Sorry for the long, confusing post, but this is usually the place I turn when I have something too complicated for many others haha.<o></o>
<o> </o>
Thanks for any suggestions.<o></o>
fficeffice" /><o></o><o
></o>Basically I have recaps (excel based reports) which run every wednesday. They are typically approx 1000 rows x 70 columns. One of the columns is usually "ID#" which is common across most of the reports. What I have started doing is saving them as CSV files into a main folder. I have used Excel to "connect to an external data source" using the text driver and connecting to that main folder. That way I can run queries and define relationships pulling information from all the different text reports in the folder without having to do a lot of lookups. <o
></o><o
> </o>The issue is that there are a lot of reports to sift through and select fields from when trying to do analysis. So what I am attempting to do is create a front end which is simpler and more graphical. <o
></o><o
> </o>So far I have created the beginnings of a userform in excel and ultimately the goal would be that I could select things on the userform, have it translate the selections into SQL which I can then pass to MS Query and then have the resultant data set returned back into Excel. <o
></o><o
> </o>The reports are named with the same convention:<o
></o>REPORT#_Area#_Month#Week#_Year#<o
></o><o
> </o>The fields in the reports are also similarly named:<o
></o>Time Period (weekly, monthly, quarterly, etc)_ Field_Unit of measurement($, %, etc)_Qualifier(TY, NY)<o
></o><o
> </o><o
> </o>This is my thought process and what I am looking for is suggestions since as I am doing this I keep thinking that I’m sure there is a more elegant way to get this done…<o
></o><o
> </o>In the userform I have some comboboxes which have the report fields<o
></o>1- Month<o
></o>2- Week<o
></o>3- Year<o
></o>4- Report#<o
></o>5- Area#<o
></o><o
> </o>So far I have it so that I can select which month week etc info and then it will populate an excel sheet with the selected info when I click a button. This process allows me to add the first parts of the SQL statements.<o
></o><o
> </o>My SQL statements are usually:<o
></o>SELECT RPT155_A10_MarWk1_2010.’WEEKLY_BIKETRAVEL_%_TY’<o
></o>FROM ‘<MAIN FOLDER LOCATION>’\RPT155_A10_MarWk1_2010.csv RPT155_A10_MarWk1_2010<o
></o>WHERE RPT155_A10_Marwk1_2010.’ID#’ = RPT155_A10_Aprwk5_2010.’ID#’<o
></o><o
> </o>With my userform passing the selections to the excel worksheet I can build the “RPT155_A10_MarWk1_2010” part. And then string many of them together which I will ultimately pass through to MS Query. The problem is that when I get to trying to add the second half of the statement (the fields) there are tooo many! I was originally planning on using a section of checkboxes in the userform and then pass through the checked boxes values to the excel sheet, but that takes too much memory in excel. <o
></o><o
> </o>So does this sound as crazy as I think it is? Is there a better way of trying to simplify pulling information from a quasi relational database constructed of text files in a folder? Any suggestions on how I can have the available fields/reports populate based on the reports in the main folder? <o
></o><o
> </o>Sorry for the long, confusing post, but this is usually the place I turn when I have something too complicated for many others haha.<o
></o><o
> </o>Thanks for any suggestions.<o
></o>
