If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
HELP!!! Big Project and I am lost in the data..lol
Ok here goes....We have devices out in the field that log data and counts for
the month on a 15 min interval. This data is then saved to a txt file and my plan is to import this data and be able to run queries on it. Whe have multiple sites and what I have done is create a table for each site name. Then I import the data into each one. This is where it gets cloudy. I am trying to figure out how to run a query for a person to enter a date range and it pull the information from each table or whatever table the person selects from a list. I have a query setup for someone to search one particular site by date range and it works fine but I can't figure out how to list each site in a list box and then based on the sites selected run a query by date range. That's just one problem. Next I am trying to figure out how to archive this data. That is the main reason for this database, because all the data is overwritten when the new comes in. We have 12 text files per site one for each month. But how can I back this up in Access and not having duplicates? I know I am rambling on but I know what I want to do and just can't get started. I always think of what I want to do and try to get that done and it just keeps getting bigger and bigger. So PLEASE any help would be great!! |
#2
|
|||
|
|||
On Mon, 14 Mar 2005 06:35:03 -0800, Daniel
wrote: Ok here goes....We have devices out in the field that log data and counts for the month on a 15 min interval. This data is then saved to a txt file and my plan is to import this data and be able to run queries on it. Whe have multiple sites and what I have done is create a table for each site name. That's a BIG mistake. Storing data into tablename *is simply bad design*. Instead, have a SiteName field in the table. Then I import the data into each one. This is where it gets cloudy. I am trying to figure out how to run a query for a person to enter a date range and it pull the information from each table or whatever table the person selects from a list. I have a query setup for someone to search one particular site by date range and it works fine but I can't figure out how to list each site in a list box and then based on the sites selected run a query by date range. You can't, not in a Query. You'ld have to have as many queries as you have sites, or dynamically construct the query using VBA code incorporating the selected site. That's just one problem. Next I am trying to figure out how to archive this data. That is the main reason for this database, because all the data is overwritten when the new comes in. We have 12 text files per site one for each month. But how can I back this up in Access and not having duplicates? Store the date in the table too. Why would there be duplicates?? If you have a record for Site 31, #3/14/2005 11:30:00#, value whatever, the site and date fields together uniquely identify the record; there's nothing duplicated, since the next month's records will have a different date. I know I am rambling on but I know what I want to do and just can't get started. I always think of what I want to do and try to get that done and it just keeps getting bigger and bigger. So PLEASE any help would be great!! If you import the data into one big table in Access, you can simply keep backup copies of the .mdb file containing all your data. Say you have 100 sites, 96 records per day, 365 days per year - this comes to a three million row table. Access can handle 30,000,000 rows without cracking a sweat, and by 2015 who knows what it will be able to do... g John W. Vinson[MVP] |
#3
|
|||
|
|||
I agree and the more I think about it the more it makes sense to have 1
table. Now a question on that. Say I create a table named Sites and I want to import all of my csv files to this one table. The csv files are raw data just date time and readings. I plan on creating a SiteName field but when I am importing the csv's how do I tell it what SiteName it should go under? Our csv files are created automatically and named peak?1.csv (the ? reperesnts the site for instance Buna would be peakb1.csv) This means the peak data for Buna in Januaray. Then I have peakb2 for Feb and so on until Dec which of course would be peak12.csv. I just need to figure out how to import these and tell it to put it under SiteId Buna. Thanks for the help so far. "John Vinson" wrote: On Mon, 14 Mar 2005 06:35:03 -0800, Daniel wrote: Ok here goes....We have devices out in the field that log data and counts for the month on a 15 min interval. This data is then saved to a txt file and my plan is to import this data and be able to run queries on it. Whe have multiple sites and what I have done is create a table for each site name. That's a BIG mistake. Storing data into tablename *is simply bad design*. Instead, have a SiteName field in the table. Then I import the data into each one. This is where it gets cloudy. I am trying to figure out how to run a query for a person to enter a date range and it pull the information from each table or whatever table the person selects from a list. I have a query setup for someone to search one particular site by date range and it works fine but I can't figure out how to list each site in a list box and then based on the sites selected run a query by date range. You can't, not in a Query. You'ld have to have as many queries as you have sites, or dynamically construct the query using VBA code incorporating the selected site. That's just one problem. Next I am trying to figure out how to archive this data. That is the main reason for this database, because all the data is overwritten when the new comes in. We have 12 text files per site one for each month. But how can I back this up in Access and not having duplicates? Store the date in the table too. Why would there be duplicates?? If you have a record for Site 31, #3/14/2005 11:30:00#, value whatever, the site and date fields together uniquely identify the record; there's nothing duplicated, since the next month's records will have a different date. I know I am rambling on but I know what I want to do and just can't get started. I always think of what I want to do and try to get that done and it just keeps getting bigger and bigger. So PLEASE any help would be great!! If you import the data into one big table in Access, you can simply keep backup copies of the .mdb file containing all your data. Say you have 100 sites, 96 records per day, 365 days per year - this comes to a three million row table. Access can handle 30,000,000 rows without cracking a sweat, and by 2015 who knows what it will be able to do... g John W. Vinson[MVP] |
#4
|
|||
|
|||
On Mon, 14 Mar 2005 12:43:02 -0800, Daniel
wrote: I agree and the more I think about it the more it makes sense to have 1 table. Now a question on that. Say I create a table named Sites and I want to import all of my csv files to this one table. The csv files are raw data just date time and readings. I plan on creating a SiteName field but when I am importing the csv's how do I tell it what SiteName it should go under? Our csv files are created automatically and named peak?1.csv (the ? reperesnts the site for instance Buna would be peakb1.csv) This means the peak data for Buna in Januaray. Then I have peakb2 for Feb and so on until Dec which of course would be peak12.csv. I just need to figure out how to import these and tell it to put it under SiteId Buna. Thanks for the help so far. You'll need some code to do this, but it shouldn't be all that hard. If your Sites table contains a field for the one-letter code (how would you deal with sites Buna and Brainard for example??) you could write VBA code using that table and the Dir() function to find the files on the disk, pick them apart, and ascertain the site. The month part of the site *should* be redundant - i.e. PEAKB12.CSV should contain dates in December, right? How are the files laid out? In addition, how can you distinguish January 2005 data from January 2004 data (are both years' files named PEAKB1.CSV)? John W. Vinson[MVP] |
#5
|
|||
|
|||
Ok, I have pretty much decided on creating one signle table to conatin the
information. I thought about just importing the data into seperate tables and then run a macro that puts everything in one table. My only question is how to get this data from all the tables into one? I tried doing an append query and it said I selected too many fields. Is there an easier way to do this? Also is there a way to schedule the import to run the first day of every month? Thanks for the help so far. "John Vinson" wrote: On Mon, 14 Mar 2005 12:43:02 -0800, Daniel wrote: I agree and the more I think about it the more it makes sense to have 1 table. Now a question on that. Say I create a table named Sites and I want to import all of my csv files to this one table. The csv files are raw data just date time and readings. I plan on creating a SiteName field but when I am importing the csv's how do I tell it what SiteName it should go under? Our csv files are created automatically and named peak?1.csv (the ? reperesnts the site for instance Buna would be peakb1.csv) This means the peak data for Buna in Januaray. Then I have peakb2 for Feb and so on until Dec which of course would be peak12.csv. I just need to figure out how to import these and tell it to put it under SiteId Buna. Thanks for the help so far. You'll need some code to do this, but it shouldn't be all that hard. If your Sites table contains a field for the one-letter code (how would you deal with sites Buna and Brainard for example??) you could write VBA code using that table and the Dir() function to find the files on the disk, pick them apart, and ascertain the site. The month part of the site *should* be redundant - i.e. PEAKB12.CSV should contain dates in December, right? How are the files laid out? In addition, how can you distinguish January 2005 data from January 2004 data (are both years' files named PEAKB1.CSV)? John W. Vinson[MVP] |
#6
|
|||
|
|||
On Wed, 16 Mar 2005 09:39:01 -0800, Daniel
wrote: Ok, I have pretty much decided on creating one signle table to conatin the information. I thought about just importing the data into seperate tables and then run a macro that puts everything in one table. My only question is how to get this data from all the tables into one? I tried doing an append query and it said I selected too many fields. Is there an easier way to do this? Also is there a way to schedule the import to run the first day of every month? Thanks for the help so far. Please post the structure of your table and the SQL view of this query. If you're trying to build a "wide" table with data for many sites in each record... DON'T. "Fields are expensive, records are cheap" - your table should have probably only three or four fields. You may need as many Append queries as you have imported files, or an Append query based on a UNION query stringing together the multiple files. As for scheduling... not within Access. However, you can use the Windows Scheduler to launch Access periodically; there's a command line switch to open Access running a specified macro, which in your case would simply have one line, a RunCode action to execute the VBA code which will run all the append queries. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Adding staff photographs to my database | KK | New Users | 2 | September 3rd, 2004 07:41 AM |
Upload Image | Jason MacKenzie | General Discussion | 1 | September 1st, 2004 04:38 AM |
How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |