items with entry first shift but no entry second shift

[hmho]

Here are the fields for my query [ID], [ShiftNumber], [SalesDate], [ProdSerial] and [ProdEndTag]. I'm trying to find how to run query that will give me ProdSerial that was entered in the previous shift but not current shift in the same Date. Shift numbers are 1-5 and there are about 5 shifts each day, and for example if the second shift entered five items and the third shift enters 4 items I would like to see all items that has entry in the second shift but no entry in the third shift. I was thinking query with parameter, but I'm not sure if that is right way to tackle it.

Thanks

 

[namliam]

Lets solve problem number 1 first, how do you find the next shift...
Is it as easy as 1,2,3,4,5,6,7,8,9,10,11,12, etc?
Or is there some other logic to it?

 

[hmho]

Yes it is as easy as 1,2,3,4,5,6

There are six shifts and it is number as 1 to 6.

 

[hmho]

Lets solve problem number 1 first, how do you find the next shift...
Is it as easy as 1,2,3,4,5,6,7,8,9,10,11,12, etc?
Or is there some other logic to it?

Yes it is as easy as 1, 2, 3, 4, 5, 6 and there is 6 shifts.

I attached sample of my data here and in the query what I'm looking for is on 08/19/09 there is no entry on serial numbe KL-123 on Sixth shift but there was entry on the previous shift (FifthShift) on same day, so in my query I want to be able to catch that.

 

[hmho]

I also attached 2000 version for those who don't have 2007 version

Thanks

 

[namliam]

Yes it is as easy as 1, 2, 3, 4, 5, 6 and there is 6 shifts.

I attached sample of my data here and in the query what I'm looking for is on 08/19/09 there is no entry on serial numbe KL-123 on Sixth shift but there was entry on the previous shift (FifthShift) on same day, so in my query I want to be able to catch that.

You only want to find the previous shift on the same day?? Or also find the previous shift on the previous day...

I.e.
19-aug-2009 Shift 5 : Next shift, 19-aug-2009 Shift 6... Easy enough
19-aug-2009 Shift 6 : Previous shift, 20-aug-2009 Shift 1... A little harder

Lets do the easy one, hoping you dont need the hard one...
1)
Make a query based on your tblShiftSales, add all columns plus a new one:
NextShift: [shiftnumber]+1

save this query, qryMakeNextShift

2)
Make another query, add qryMakeNextShift and your tblShiftSales.
Do the outer join you already have but use the NextShift instead of the ShiftNumber.

Hope you understand what I mean. Good luck !

P.S. You also have to think up something to work around a problem that this query will have... It will always show shift 6 as having a missing "next shift" of 7.

 

[hmho]

You only want to find the previous shift on the same day?? Or also find the previous shift on the previous day...

I.e.
19-aug-2009 Shift 5 : Next shift, 19-aug-2009 Shift 6... Easy enough
19-aug-2009 Shift 6 : Previous shift, 20-aug-2009 Shift 1... A little harder

Lets do the easy one, hoping you dont need the hard one...
1)
Make a query based on your tblShiftSales, add all columns plus a new one:
NextShift: [shiftnumber]+1

save this query, qryMakeNextShift

2)
Make another query, add qryMakeNextShift and your tblShiftSales.
Do the outer join you already have but use the NextShift instead of the ShiftNumber.

Hope you understand what I mean. Good luck !

P.S. You also have to think up something to work around a problem that this query will have... It will always show shift 6 as having a missing "next shift" of 7.

I,m little confuse on item two.

 

[hmho]

I'm littel confuse on part two.

Thanks

 

[namliam]

what dont you understand of part 2?
You have an outer join query already in the DB, simply do the same but a little different...