Calculating functions based on previous row's data

[Brian]

In Excel, it is very easy for me to write if statements based on a previous
row’s content. For example: if(and(a1=a2,b1=b2),â€Yesâ€,â€Noâ€). Is there a way
to write a similar statement in Access with regard to a field’s content in a
previous row? For example: iif(and(row1 of field1 = row2 of field1, row1 of
field2 = row 2 of field2),â€yesâ€,â€noâ€). Any help would be greatly appreciated.

This is in regard to a calculation that is dependent upon date, technician,
and then previous GPS coordinates....so basically, if the date is the same
and the technician is the same, then calculate the distance from the current
GPS coordiantes from the previous GPS coordinates.

What I need to do is very easy in Excel, but the data set I'm working with,
as well as the process going forward, needs to be Access based.

I am open to SQL suggestions or other resources I can try.

Thanks,
Brian

 

[KARL DEWEY]

Records in Access is like a barrel of bricks without order. When you want to
see them you need to sort based on the fields. So sorting on date and
technician gets you started but how do you know which is the previous GPS
coordinate and which is the latter?
If your date includes time then that should tell you. Use these two queries
---
Brian_X1 --
SELECT Q.Technician, DateValue([Q].[LocationDate]) AS Sample_Date,
Q.GPS_Lat, Q.GPS_Lng, (SELECT COUNT(*) FROM Brian_X Q1
WHERE Q1.[Technician] = Q.[Technician] AND Datevalue(Q.LocationDate) =
datevalue(Q1.LocationDate)
AND Q1.LocationDate < Q.LocationDate)+1 AS Rank
FROM Brian_X AS Q
ORDER BY Q.Technician, DateValue([Q].[LocationDate]), Q.LocationDate;


SELECT Brian_X1.Technician, Brian_X1.Sample_Date, Brian_X1.GPS_Lat,
Brian_X1.GPS_Lng, Brian_X1.Rank, Brian_X1_1.Rank,
[Brian_X1].[GPS_Lat]-[Brian_X1_1].[GPS_Lat] AS Lat_Diff,
[Brian_X1].[GPS_Lng]-[Brian_X1_1].[GPS_Lng] AS Lng_Diff
FROM Brian_X1 INNER JOIN Brian_X1 AS Brian_X1_1 ON (Brian_X1.Sample_Date =
Brian_X1_1.Sample_Date) AND (Brian_X1.Technician = Brian_X1_1.Technician)
WHERE (((Brian_X1_1.Rank)=[Brian_X1].[Rank]+1));

 

[Brian]

Each record represents a 'stop' at a customers house with a unique work order
number. We have Lat and Longs tied to the work order number. So I have a
sequential list by date, by tech, by time dispatched. Their starting points
are always the same.....here is my if statement in excel that I am trying to
replicate...
G1 & H1 = start lat and start long....G2 & H2 = ending lat and ending long

P1=P2 makes sure the date is the same
J1=J2 makes sure the tech is the same

The false clause contains the GPS coordinates of their starting location, as
false would mean that it is the first call dispatched of the day, and I
should be calculating the distance from their starting location to their
first call of the day.

Dataset is sorted Date Ascending, Tech Ascending, Time Dispatched Ascending.

All is sorted the same in Access, but I can't do the calculation.

Thanks in advance.

=IF(AND(P1=P2,J1=J2),1 * 3963 * ACOS(SIN(G1 / 57.2958) * SIN(G2 / 57.2958) +
COS(G1 / 57.2958) * COS(G2 / 57.2958) * COS(H2 / 57.2958 - H1 / 57.2958)),1 *
3963 * ACOS(SIN(61.6239166259766 / 57.2958) * SIN(G2 / 57.2958) +
COS(61.6239166259766 / 57.2958) * COS(G2 / 57.2958) * COS(H2 / 57.2958 -
-53.6543884277344 / 57.2958)))

Records in Access is like a barrel of bricks without order. When you want to
see them you need to sort based on the fields. So sorting on date and
technician gets you started but how do you know which is the previous GPS
coordinate and which is the latter?
If your date includes time then that should tell you. Use these two queries
---
Brian_X1 --
SELECT Q.Technician, DateValue([Q].[LocationDate]) AS Sample_Date,
Q.GPS_Lat, Q.GPS_Lng, (SELECT COUNT(*) FROM Brian_X Q1
WHERE Q1.[Technician] = Q.[Technician] AND Datevalue(Q.LocationDate) =
datevalue(Q1.LocationDate)
AND Q1.LocationDate < Q.LocationDate)+1 AS Rank
FROM Brian_X AS Q
ORDER BY Q.Technician, DateValue([Q].[LocationDate]), Q.LocationDate;


SELECT Brian_X1.Technician, Brian_X1.Sample_Date, Brian_X1.GPS_Lat,
Brian_X1.GPS_Lng, Brian_X1.Rank, Brian_X1_1.Rank,
[Brian_X1].[GPS_Lat]-[Brian_X1_1].[GPS_Lat] AS Lat_Diff,
[Brian_X1].[GPS_Lng]-[Brian_X1_1].[GPS_Lng] AS Lng_Diff
FROM Brian_X1 INNER JOIN Brian_X1 AS Brian_X1_1 ON (Brian_X1.Sample_Date =
Brian_X1_1.Sample_Date) AND (Brian_X1.Technician = Brian_X1_1.Technician)
WHERE (((Brian_X1_1.Rank)=[Brian_X1].[Rank]+1));

--
KARL DEWEY
Build a little - Test a little

In Excel, it is very easy for me to write if statements based on a previous
row’s content. For example: if(and(a1=a2,b1=b2),â€Yesâ€,â€Noâ€). Is there a way
to write a similar statement in Access with regard to a field’s content in a
previous row? For example: iif(and(row1 of field1 = row2 of field1, row1 of
field2 = row 2 of field2),â€yesâ€,â€noâ€). Any help would be greatly appreciated.

This is in regard to a calculation that is dependent upon date, technician,
and then previous GPS coordinates....so basically, if the date is the same
and the technician is the same, then calculate the distance from the current
GPS coordiantes from the previous GPS coordinates.

What I need to do is very easy in Excel, but the data set I'm working with,
as well as the process going forward, needs to be Access based.

I am open to SQL suggestions or other resources I can try.

Thanks,
Brian