[Resolved] ACCESS SQL syntax problems

leatherback

Dear all,

Sorry to bother you: Not really a PhP question, although it is pure database related..

A friend of mine set-up a horrible database, and now I am trying to solve some of her work. But i have no clue of access SQL!

What I try to do:

I have a table with oberservations, with multiple fields, some selected here:

observations.start, observations.end, observation.obser date

Now I want to for a certain day, know whether observations were made at a certain time, say, each 15 seconds of a day (This interval is dynamic). (This is some animal behavioural database). This will of course result in duplicate records when any observtion lasts longer then the sampling interval, whic is desired

What I came up with: I added a table NATURALS with intergers: 1..65 000.

My thought: I can multiply the sampling interval with the integer colum, and then do a select based on that. Please find the SQL below:

PARAMETERS [Day] DateTime, SamplingInSeconds Long;
SELECT (N.N-1)*SamplingInSeconds AS Sample, P3.*
FROM [P-3 Video observations corrected data] AS P3, Naturals AS N
WHERE (((([N].[N]-1)*[SamplingInSeconds]) Between (Hour([Start])*3600+Minute([Start]*60+Second([Start]))) And (Hour([End])*3600+Minute([End]*60+Second([End])))) AND (([Day])=[P3].[Obser date]));

Unfortunately, this does not even get close to selecting all the records. Is theer an ACCESS wizz here who can have a quick peek at the code?

Thanks!

Roger_Ramjet

Lets see if I understand you first.

Observation interval = 15 secs (for example)
Individual observation record has start and finish time = observation duration.
Q Do these every extend across 2 dates? Not really important as native date function will give interval regardless of dates.

Solution: How many intervals in a duration? I think that this is actually your core question

If I'm right in this then I can do your query easily.

leatherback

Hey Roger,

Thx:

The sample int. can extend across two dates: It is actually a database which contains the observations of slowed-down videos takes of a forest site. Each tape contains 1 frame per second, for several days, continuously. From this, she has made observations, such as, when is an animal present. From start-end. This would be the duration, which can be anything from a second (1 frame) to say, half an hour. (In the latetr case, she'd want the same record returned for each interval: 30*4=120 times).
Although the observations do extend over two dates, I think she entered them as individual observations. (And unfortunately, she used different fields for observation date and time. I would have rebuild the whoile thing on MySQL, if it weren't for the fact that there are like a zillion (~60) sub-tables connected, which I canot convince her to loose..

Now she has realized that these ~16 000 (no kidding!) records should be 'resampled', to "What was present in second 15, 30, 45, 60, 75, ... of the tape"

The query I made does work partially, but there is something funny. It returns say only 8 records, even though there are 15 observations for that day, each of which lasts several minutes.

If you have any ideas, that would be great!

PS: I might be a bit slow responding the next few days: I am at a conference, and am not sure whether there is time to sit behind the machine..

Roger_Ramjet

Well hopefully she has got her db schema correct and you can actually get back out what was put in.

Best solution, reconstruct the data and simplify things, quite literally.

Not going to happen though, is it?

OK. let me mulll this over for a while. One good thing about access is that it does support nested subqueries so we are not in as bad a shape as might be.

Roger_Ramjet

OK. My solution is as follows.

Film is 1 frame per second = 86400 frames per day
Give each frame an identity is the logic behind this. Identity = date and integer equivelent of time (2 parts that uniquely identify every frame on a tape)
Add 2 columns to the observations tabel:
IntStart = integer identifier
IntEnd = integer identifier

Convert start and end times to their integer equivs 1 to 86400

Now, we can do some simple calculations to find out how many intervals there are in any observation, ie end - start / interval in seconds

I'm sure you can work out the controls to aline this with your frame at 15, 30, 45 etc seconds into the film

Do not be afraid to pull the data out into another table for this to simplify queries. Although Access is capable of very complex query constructions, it is also very slow to process them. So extracting data into intermediate tables for subsequent query processing is often the best solution. eg I have letter runs that require complex conditional processing. So I run 4 queries to produce 3 intermediate tables and then the final dataset for the print routine.

I have attached an access mdb with a function that does the time to integer conversion, a table for data to work on, and an example query that shows how to use the function.

Hope you follow me, if not ask and I'll explain.

Roger_Ramjet

My personal approach, were this my data, would be to actually construct a table where every frame in every film had it's own record. - Really Raw Data. - Then I could run whatever analysis I wanted. Once you've summarised it you've limited your options

I've worked for market research firms over the years, and the most important thing I learnt is Always keep your raw data. Summaries and analyses are all very good, they are what you want to achieve. But you must always be able to return to the original source for further analysis. Well, you already know this being a research scientist.

leatherback

Hey Roger,

Thanks! I'll have a look at it when I get back on friday. The problem with the framerate... This is not standard even. Depending on the amount of time she had, she varied the frame-rate per monitoring tape. But I will have a look, and see whether there is some way of putting the frame-rate in dynamically..

As for dataformat/storage.. Let me just say: She is doing a PhD project, and I do not understand how her promotor can even have let her go on with this database this long. the structure is not correct, and no, she has not really looked at a good way for analyzing / storing data.

My first step in any project is alway: place everything in a huge spreadsheet (small dataset) or database (larger dataset, >100 records), copy that, and place one copy on backup system. Then start analyzing. But well.. She is a long way from her PhD, I assume..

Currently we are looking at writing a tool in java, to open a digitized film, and stepping through it a frame at a time, and grabbing individual frames where interesting changes occur. Any information placed in the linked forms will then autmatically be inserted into a database (Likely mysql, since I am building it ;-) ) where it will be available for analysis. But that still leaves the data which has been processed, as I understand >> 8 months work, to be recovered.. -aaahh-

sigh, we are too good for this world ;-)

Roger_Ramjet

Actually, a spreadsheeet is probably better suited to this anyway. Excell has things like serial time to turn times into decimal nos - which Access does not, hence my function. Makes division of time by interval a heck of a lot easier. Not to mention all the statistics tools that no relational DB has but any good spreadsheet does.

leatherback

Hey Roger,
I am going the working way: I am writing a PhP/MySQL tool, and I'll convert that one table into resampled tables in MySQL, which will work for sure..
I am tired of trying to get her (% database to work. Then she can reload the table into acces, and do whatever linking she wants there..

Thx.'