Using multiple criteria DLookups for a Query (1 Viewer)

Hello all.

I have a rather interesting problem, and I can't seem to find the right solution.

I am working with a database which contains dates and a measurement value with data within the following fields:-

• Record
• SurveyID
• DateTimez
• mX
• mY
• mZ

Here is an snapshot of my main table "SurveyData"

Record | SurveyID | DateTimez | mX | mY | mZ
7194 | A2 | 16-Mar-14 | 12797.4815 | 25781.8672 | 81.1848
7195 | A2 | 17-Mar-14 | 12797.4863 | 25781.8672 | 81.1867
7196 | A2 | 18-Mar-14 | 12797.4907 | 25781.8698 | 81.1875
7197 | A2 | 19-Mar-14 | 12797.4844 | 25781.8642 | 81.1846
7198 | A2 | 18-Mar-14 | 12797.4883 | 25781.8646 | 81.186
7199 | G23 | 21-Mar-14 | 12419.8779 | 24822.6836 | 132.6177
7200 | G23 | 12-Mar-14 | 12419.8928 | 24822.6812 | 132.6075
7201 | G23 | 13-Mar-14 | 12419.9211 | 24822.6829 | 132.5915
7202 | G23 | 14-Mar-14 | 12419.9392 | 24822.6837 | 132.5795
7203 | G23 | 15-Mar-14 | 12419.9581 | 24822.684 | 132.5662


(apologies about the formatting)

Record is the primary key which is a auto increment integer. The Survey ID is a text field that contains the name of the survey sensor, DateTimez is, we the Date and the time of survey and mX is the X coordinate. There is also mY and mZ, but once I can do the mE then its just a matter of duplication.


Now, I am trying to develop a query that will add some additional calculated fields to calculate the difference between the mX coordinates from the previous pickup, which is called 'dX'. The code will be duplicated to calculate the dY and dZ. I have managed to solve this using a Dlookup statement as follows:-


This works fine, however I am struggling to make it work so it will only take the difference for each SurveyID. For example, the calculation does not calculate the difference between the X-coordinates between the Sensors A2 and G23,as they are not spatialy related. The code I am trying to use is shown below:-


I think I have got myself totally confused, but what I am trying to achieve is calculating the difference between the x coordinates from a previous record but only if the SurveyID matches on the previous record. I am using the record field to find the previous record.

If you have any suggestions how I can make this work, or better ways to doing this then please let me know.

Thanks in advance.

I haven't got Access anymore to try this but I would drag the table into the query grid twice say t1 and t2

Join on SurveyID

Dx= t2.mx-t1.mx

Switch to SQL view and add Where t1.record=t2.record+1

If this works then you can do dy and dz in the same query

Brian

Thanks for the response Brian,

I have had a stab at it and used the following code:-


The answer keeps coming back as -1, but on a positive note, I am only getting the results for one Survey senor which is good. It just seems that the actual calc is not working.

Any Ideas from my SQL?

Ok.. Ignore me.. I was subtracting the record from itself!! It seems to work

Thanks

You appear to be doing the calculation on the record field not mx

Brian

Ok we typed at the same time

Glad it's working

Back to the garden

I was, I noticed the error earlier.. Thanks for the tip, the query works well.