Help Querying series

[ktmchugh]

Hi,



I have a dataset from which I need to extract all the records where the Temperature (one field) is <0.1 for >6 hours (date/time are the second field). To clarify, the data were recorded hourly. So 6 hours of temp data would be 7 records.

Does anyone know how I would set up a query to do this?

Thanks!
ktmc

[CraigDolphin]

Create a query of the source table with two field in the query builder grid.
In the criteria of the temperature field use < 0.1 (this assumes that the filed contains numeric data)

Assuming that the data in the second field represents the total length of time elapsed/duration, then you could use a criteria of > #6:00# in the date/time field.

If you have a start time and end time in two different fields then you would need to use something like the datediff function to calculate the difference as a calculated field, and then use an equivalent criteria in the criteria row of that calculated field instead.

Hope it helps.

[ktmchugh]

Thanks - I'll try that!

[ktmchugh]

Hi,

I tried what Craig suggested but I think what I need is more along the lines of an IF THEN statement. I have attached a screen shot of one of the tables I'm querying so you can see what I'm dealing with.

I need to parse all the records in such a way that when there's a temp value <-0.1 the next record is checked for a neg value and if it's neg, then the next value is checked and the next, through 6 consecutive records with neg values. After the six records with neg values, any subsequent records with neg values should be deleted. Once a positive value is reached, the data continue to be parsed as before, again looking for 6 neg values in a row.

So, basically I am deleting all consecutive records with neg temp values which are preceded by 6 consecutive records with neg temp values. So if there are 12 consecutive records with neg temp values, followed by a positive temp value, the first 6 will not be deleted, but the next six will be, and then the positive will not be deleted and so on until the next place where there are >6 negative temp values in a row.

I think I need an IF THEN type of statement for this, but I'm not sure how to put it together.

Does anyone have an idea about how to write this?

Thanks!
ktmc

[CraigDolphin]

Hi again. Ok, that's a very different proposition than I had read from your first question.

There's a couple of different approaches to solve this.

One would be to write a vba function to step through the recordset and determine whether the conditions you've outlined are met or not. You could use the function to return a true or false in a field in a query, and then select your records according to that result.

However, you can also use SQL to do this although it's tricky.
The first issue is that the order of the records inyour source table is critically important in this situation. Therefore, I recommend creating a query called 'Observations' which explictly sorts the data in your source table into three columns:
[River] Order Ascending
[ObservationTime] Order Ascending
[WaterTemperature_C] Not sorted.

If there is any possibility of null values in the data set, ensure that there are no null water temps in this query by using the Is Not Null criteria in all three fields.

Then create another query and use the following SQL for this new query

SELECT Observations.River, Observations.ObservationTime, Observations.WaterTemperature_C, IIf([WaterTemperature_C]>0,0,(Select Count (*) FROM [Observations] as Temp WHERE (([Temp].[River] = [Observations].[River]) AND ([Temp].[ObservationTime] < [Observations].[ObservationTime]) AND ([Temp].[ObservationTime] >DMax("ObservationTIme","Observations","[ObservationTime]<#" & [Observations].[ObservationTime] & "# AND [WaterTemperature_C]>0") )))+1) AS ConsecutiveNum
FROM Observations
ORDER BY Observations.River, Observations.ObservationTime;

This should create a calculated column that counts the number of consecutive subzero temperature readings since the last zero/positive reading for that river.

Then you can use a criteria of >=6 in that column to select the rows you wish to delete.

Essentially this does use a conditional if statement to return a value of 0 when the water temp is positive, and if the temp is negative, then it uses a subquery to count the number of negative temperature records that have a time greater than the time of the last positive reading, but less than the current reading for the same river.

Hopefully that helps you out.

[ktmchugh]

Hi Craig,

So I have done as instructed, but when I try to run the query I am getting an error that says "Syntax error in date in query expression '[ObservationTime]<#20/06/2007 2:49:40 p.m.# AND [WaterTemperature_C]>'.

I'm just not sure what part of the syntax would be wrong?

Thanks so much for your help with this!!

katie

NB: 20/06/2007 2:49:40 p.m. is the first record.

[CraigDolphin]

Not entirely sure except that one thing sticks out. Which is
p.m.

should be:
pm (no periods)

Make sure that the field containing the date/time is actually a date/time datatype, not a text field.

otherwise, if you can post a zipped copy of your db I'll see if I can find it. I tested it ona mockup db here and it worked as posted.

[ktmchugh]

Hi Craig,

So I changed the date/time format so there's no "p.m." etc, but I am still getting the syntax error. I am noticing that even though there is no longer an a.m./p.m. the error is still saying that there is. In this instance (uploaded database) I changed the format in excel, and when I imported the table it was automatically in 24hr format.

I've uploaded a copy of the database with the table, Observation query and NegTemps query.

Sorry for being a pain, but thank you so much for helping.

katie

[CraigDolphin]

Katie,

the syntax problem is that, if you look in the table design view, in the ObservationTime Field, you have a custom format defined. The query sql needs to be able to interpret the date/time using a standard access format. I just changed the format to 'general date' and got no syntax error when I ran the query.

However...this query is a computational monster. With nearly 10,000 records in your table, it's going to take time to run.

I tried running it without making any other changes and it was still working on it 25 minutes later. So I stopped the query running and went back to your table design view and selected the observation-time and the river fields and forced the database to index them (duplicates ok).

This time, the query ran MUCH faster. It took about 2 or 3 minutes. So, I converted it to a make table query and created a copy of your original table with the addition of the ConsecutiveNum field which contains the results of the query calculation.

This way, you can run your delete query on the new table using your criterion on the ConsecutiveNum field values rather than calculating on the fly.

So here's a zipped version of your db, with the date format changed to general, and the functional make-table query based off the original query I provided. And also the new table of data with the results of the query.

[ktmchugh]

Hi Craig,

You have been so great to do all of this work to help me out! Unfortunately I'm still getting the same error. I've tried running the query from the database you zipped on different machines, with different versions of Access (2003 and 2007), thinking that maybe there's some weird setting on mine but none of that helped.

Do you think that there could be some setting or option or something that I have set that's screwing with the whole thing?

I'm at a complete loss - obviously it's possible to make this work since you succeeded.

Thanks again,
katie

[ktmchugh]

Hi Craig,

Thank you so much for taking the time to do so much to help me with this! Unfortunately I am still getting the same error. I tried running the query from the database you zipped on different machines, and with difft versions of Access (03 and 07) but the same thing.

Since you were successful with it, do you think it could be some other setting (other than the date/time format) that I have that's causing the error? Are there any Access options that could affect it?

I'm at a complete loss.

Thanks!
katie

[CraigDolphin]

Hi Katie,

Um. Err....?

To be sure, I re-downloaded the file I zipped, unzipped it, and ran the query. It took a couple minutes to execute, and worked perfectly.

I have to confess myself at a loss too.

Try re-downloading the file in case your original download somehow got corrupted.

Try doing a compact and repair on the database
Tools>Database Utilities>Compact and Repair

And try the query again. If it still doesn't work then maybe, something in the access file is corrupted (though why it isn;t showing up on my computer I could not say).

Create a clean new database file. Import the original table, and the two queries from this database into the new one.

Run another compact and repair on the new one, and try again.

Failing that I'm stumped. I would be tempted to think your access installation is faulty, but that seems unlikely on two different machines. Although, if both Ac2003 and AC2007 are ont he same machine, there may be conflicts created by this situation that maybe (clutching at straws here now) might explain the situation.

I run AC2003 (only) on my computer.

RuralGuy might be able to offer you some other ideas as he's one of the cleverest Access people I know of. If you are unable to succeed with any of the suggestions I've given you, post back and I'll PM RG and ask him to take a look at this thread.

[ktmchugh]

Hi Craig,

So I did as you suggested (created a new database, re-created the queries, etc and also did a compact and repair), but no luck. I have no idea what's going on. Weird. Maybe Rural Guy has some other ideas but you've done so much I don't know if there's a solution.

Thanks so much for your help,
katie

[RuralGuy]

Hi Katie,
I downloaded Craig's db and ran the MakeTable query without any problems. The only thing I can think of is did you "Unblock the file" before you unzipped it? Are you saying you are still getting the same error you posted in post #6 of this thread?

[ktmchugh]

Hi Rural guy,

Yes, same error as in post #6 (same syntax error). I did not "ublock the file" before unzipping - should I do that? And how? I'm on Vista (grrr) with Access 2007 - are there any weird issues b/n Access and Vista that might be factors in this problem?

Thanks!
katie