HELP!!! Big Project and I am lost in the data..lol

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!
 
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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!!
Click to expand...

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]
 
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.
 
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.
Click to expand...

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]
 
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.
 
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.
Click to expand...

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]
 
Back
Top