Need query to check if there was a matching field in previous record

[ML!]

Been working on this for a bit...I think I know how I would do this in code but I really need to accomplish it in a query because I need to use the results in several other queries.

In simple terms (left out unnecessary fields etc), I have a shift table and a another table with a one to many relationship that captures products that were worked on in that shift.

tblShifts
ShiftID (pk)
ShiftDate
ShiftName

tblShiftProduction
ShiftProductionID (pk)
ShiftID (fk)
ProductID (fk)

Then there is an inventory table that captures counts done each shift

I have to do an inventory calculation but first, I have to check if the same product was counted in the previous shift because if it was, the calculation is different than if it wasn't.

Say ProductID = 1 and the ShiftID I'm working on is 20, I need to find out if there is a record matching ProductID 1 in shift 19.

I tried to construct a query using DLOOKUP in an expression using ShiftID-1 as the criteria argument but couldn't make it work.

I've been working on this awhile and the resolution isn't coming to me. Any ideas?

 

[vbaInet]

Is ShiftID an autonumber or number datattype?

 

[ML!]

Autonumber

 

[vbaInet]

You wouldn't get the results you desire. When a record is deleted that number would no longer be in use and the records that follow after that number are not renumbered. I'm guessing you're aware of this?

 

[ML!]

I'm confused (again). I'm not contemplating deleting any records.

I just need to know if ProductID 1 is in tblShiftProduction where Shift = (ShiftID-1).

If not possible to do with DLOOKUP in an expression, is there another way to accomplish it in a query?

 

[ML!]

Oh I figured out what you were saying about the renumbering.

While I'm not contemplating deleting records (the test would just fail if the record didn't exist) what I didn't consider was what happens if data was entered out of order.

You're right as usual, I can't use the ShiftID in the test.

Any other ideas (nested if's using the shiftdate/name combination maybe?)

 

[vbaInet]

Just got back. Yes, the test would "fail" if you're referencing a record that doesn't exists.

If you're looking to do a recursive call then you would need to write a function for this. In the meantime, here's how to use the DLookup() function:

DLookup("[FieldName]", "TableName", "ProductID = " & [ProductID] & " AND [ShiftID] = " & ([ShiftID] - 1))

Was that what you had?

 

[ML!]

I didn't do the AND with shiftid and productid. That makes more sense now. But I have abandoned the idea. I came to the same conclusion and am writing a function. So that I have access to the resulting data, I'm using the function to create a temp table to work with.

Thanks for your help.
ML!

 

[vbaInet]

See how you get on with the function. Let us know if you need assistance with it.