Where to start? 1

[dhavard]

I am needing to create a database where we have log files (rtf) from equipment. I need to import these monthly and archive them and then be able to create an interface to search on location and/or date and display a report for printing. This is my main first task more advance to come. What would be a good solution for this. Any help would be greatly apprecaited. I have created a few things but don't know if it's the best way to go with this.

 

[dhavard]

Also When I say I want them to search by location I have my tables are named the Location and in the tables I have the time, date and other data in there. I have already setup a search by date but want to somehow setup something so that users can select a location or multiple locations before searching the date range. The txt files I am importing are coming from equipment and it stores values such as kilowatts used and so forth. Each month they are overwritten with the new data. I have an append macro setup to import the data but if I accidentally run it 2 times the data is duplicated. I have tried to run a query for duplicated data but the increments on the txt files are every 15 mins so the date field has the same date over and over and I am just confused how to keep from adding the data over and over. I hope I have explained this. But trying to take it one step at a time and I keep getting ahead of myself thinking that I need to do it all at once...lol PLEASE HELP!!! lol

 

[yetanotherjo]

I'd sort out your data quality issues first. Re the duplicating - can you include the time in the date field or are there fields you can use to create a unique key for each record you are importing?

The search you could set up using forms, with a multi-select list box to choose the location/s.

 

[dhavard]

Thats the problem I am running into I wanted to try and join the time and date field...they are comma seperated in the txt file, not sure how to join them when I import the data. Also I have created a list box after look on this forum (great advice) and now I don't know how to send the selected location to my query......for instance I have a query setup that looks at the table called Deweyville and ask the user to enter a date range and it searches the deweyville table for those ranges and displays them in a report. I am trying to figure out how to create a form with a list box of locations (that part is done) then click search and it prompt for the start and end dates and then search the table based on the locations the user selected. The problem I am running into is how do I setup my query to accept the users selections and then search those tables...UGGGHH...lol Hope you can help.

Thanks!!!

 

[yetanotherjo]

Oh, I just realised you had a separate table for each location...sorry, I probably shouldn't be doing this - still got placenta brain lol...

If you need to search separate tables, then create a single report, you might need to use code to create a union query to join all the data together into a single source for your report.

The other option is to put all the data into one table and use a location field (not sure what data volume you're talking about though so this might get a bit beastly).

Date/time: depending on how you're importing the data, you could import as is and then update with a query:

UPDATE Table1 SET Table1.RecordDateTime = [RecordDate]+[RecordTime];
just adds a date and time field together to give a date/time field ie
1:00:00 PM + 02/04/2005 = 02/04/2005 1:00:00 PM
8:00:00 AM + 02/04/2005 = 02/04/2005 8:00:00 AM

Hope that helps.

 

[dhavard]

I am oooo sooo confused...if i send u a copy of the database would it make more sense and u can see what I am trying to do? Sorry I am soo dumb sometimes My brain is fried over this!

 

[yetanotherjo]

Yeah, no worries.

 

[dhavard]

just one thing...how can i get the database to you?

 

[yetanotherjo]

Ah, technical hitch there as posting email addies is frowned upon.

Paste in a couple of rows from your text file (dodgy the data if you like) so I can see what sort of data you're playing with and I'll tell you how I'd set it up.

 

[dhavard]

This is out of a table called Deweyville..... that is one of the locations and i have each location as a seperate table.


Time Date Kw KVAR A Ph Amps B Ph Amps C Ph Amps
8:15:00 AM 1/1/2005 2798.4062 -282.63721 130.9402 150.25642 154.74973
8:30:00 AM 1/1/2005 2841.5625 -265.4939 131.13556 153.82175 150.84253
8:45:00 AM 1/1/2005 3043.2187 -227.69189 133.38219 162.07571 166.76436
9:00:00 AM 1/1/2005 3288 -345.49414 122.71066 154.92068 164.88403
9:15:00 AM 1/1/2005 3321.5937 -366.59277 134.40784 157.87549 163.63863
9:30:00 AM 1/1/2005 3379.2031 -340.21973 146.2027 173.84618 172.30772
9:45:00 AM 1/1/2005 3417.5937 -248.35156 148.66913 178.36389 184.56657
10:00:00 AM 1/1/2005 3508.8437 -263.73584 157.4115 174.40784 186.93532
10:15:00 AM 1/1/2005 3696 -211.86768 158.38829 194.26135 184.24911
10:30:00 AM 1/1/2005 3796.7656 -154.2854 156.33704 207.7412 197.89992
10:45:00 AM 1/1/2005 3820.7969 -169.67017 163.63863 194.65202 212.23447
11:00:00 AM 1/1/2005 4180.7969 -113.84595 169.42618 211.3065 212.84494
11:15:00 AM 1/1/2005 4209.5937 -128.35107 173.84618 207.91211 215.77536
11:30:00 AM 1/1/2005 4296.0156 -91.867676 174.16365 215.53116 219.19417
11:45:00 AM 1/1/2005 4468.7969 -69.010498 176.28818 214.31012 212.77171
12:00:00 PM 1/1/2005 4444.7969 -54.504883 171.08672 205.12823 208.76683
12:15:00 PM 1/1/2005 4560.0156 -29.010254 176.43469 211.45303 209.93898
12:30:00 PM 1/1/2005 4473.5781 3.956543 169.69478 204.02934 210.67159
12:45:00 PM 1/1/2005 4387.2187 58.022461 178.99883 206.22714 213.5043
1:00:00 PM 1/1/2005 4348.7812 78.681885 168.4005 207.27718 214.04153
1:15:00 PM 1/1/2005 4444.7812 56.26416 168.15633 212.381 206.39809
1:30:00 PM 1/1/2005 4363.2344 86.593994 173.33336 207.79001 210.64714
1:45:00 PM 1/1/2005 4406.3906 66.373779 170.10991 204.3468 210.42735

 

[yetanotherjo]

Got it. I'll get back to you shortly.

 

[yetanotherjo]

Ok. Had a look...
To keep things simple I'd try putting all files into a single table and add a location field. Time, Date and Location would form a composite key, preventing double loading of records. The data is pretty simple so the table shouldn't grow too unmanageable, even though the number of records would be large.

This means your querying is based on a single table and would make filtering much easier if users are usually looking at data from a number of sites.

Hope this helps