I need to craft a query (qryMilesError) that looks at tblPrimaryData, using the Fields IDNumber (primary key, with autonumbr as it's data type), Route, Arrive, StreetNumber and Miles.
That part is easy but I need to see only the records where if the StreetNumber is the same as the record preceding if but the miles is different.
Full Version: Problem Crafting Query
Hi,
Unfortunately, Access tables do not have a specified "order" when storing the data. Do you have a date/time field that we can use to determine which record is the previous one?
Just my 2 cents...
Unfortunately, Access tables do not have a specified "order" when storing the data. Do you have a date/time field that we can use to determine which record is the previous one?
Just my 2 cents...
Sorry, yes there is a Primary Key called IDNumber (AutoNumber)
QUOTE (ordnance1 @ May 24 2012, 10:16 AM) 
Sorry, yes there is a Primary Key called IDNumber (AutoNumber)
Okay, check out some of the options in this MS Article.
Just my 2 cents...
I took a look and created their test, but this seems to be related to a Form. I need to do this in a query. I will then be running an unmatch query against this query.
Hi,
Did you see the portion that says: "In a Query?"
Did it not work as well?
Just my 2 cents...
QUOTE (ordnance1 @ May 24 2012, 11:02 AM)
I took a look and created their test, but this seems to be related to a Form. I need to do this in a query. I will then be running an unmatch query against this query.
Did you see the portion that says: "In a Query?"
Did it not work as well?
Just my 2 cents...
Look down further in theDBguy's link to the article and you will see how to lookup the previous record in a Query.
Example:
Expr1: DLookUp("[Field1]","Table1","[ID]=" & [ID]-1)
Example:
Expr1: DLookUp("[Field1]","Table1","[ID]=" & [ID]-1)
As noted, tables have no order; and you can't even count on autonumbers to be sequential or gapless.
To find those cases where two records have the same Route, StreetNumber and Miles but are in fact different records, you could use a SELF JOIN query:
To find those cases where two records have the same Route, StreetNumber and Miles but are in fact different records, you could use a SELF JOIN query:
CODE
SELECT A.ID, B.ID, A.Route, A.StreetNumber, A.Miles
FROM Table1 AS A INNER JOIN Table1 AS B
ON A.Route = B.Route AND A.StreetNumber=B.StreetNumber AND A.Miles=B.Miles
WHERE B.ID > A.ID;
FROM Table1 AS A INNER JOIN Table1 AS B
ON A.Route = B.Route AND A.StreetNumber=B.StreetNumber AND A.Miles=B.Miles
WHERE B.ID > A.ID;
Tanks, totally missed that.
Possibly even better, you could prevent the duplicate from ever being created in the first place by putting a unique three-field index on Route, StreetNumber and Miles (and trapping the error on the form with a user-friendly error message if one should be added).
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.