Trying to create a query that shows...

[Lanky77]

part numbers that have been accepted at least six consecutive times, with no rejections. This is what I have to work with. I have a column with part numbers, one with lot numbers, and one with a reject column. The only part of this that I can figure out on my own is that I have to put "is null" in the criteria for the reject column. The hard part is how to only show part numbers where the most recent lot numbers go back at least six consecutive (56, 55, 54, 53, 52, 51). I hope this question makes enough sense and that someone out there knows the solution. Thank you!

 

[downwitchyobadself]

If I understand you correctly, you need to use a group-by query. Set the grouping under the reject field to where, and use is null as you said. Get the minimum lot number, the maximum lot number, (2 separate columns, same lot field in each, one with grouping set to Min, the other to Max), and maybe even a third to get the count of lots.

What I don't understand from your post is whether there can be intervening lot numbers that don't contain a certain part? Can a part appear in 51, 52, 53, 55, 56, 57, for example? And can there be more than six?

Have to know that to do the rest. But you can already start playing with group-by...

 

[Lanky77]

OK. I'll try to explain this a little better for you. Say a certain part has come in 7 times. There will be lots 1 thru 7 in the lot number field. Let's also assume that the part was rejected the 4th time it came in. This means that the reject field for lot 4 contains some kind of information describing the problem. Now, what I want to look up is only the part numbers that have six consecutive accepted lots (nothing in the reject field). These lots also need to be the most recent that we have received. Does this help? Actually, let me try to show you the data I'm working with.

Part Number Lot Number Rejection

2500-0560-002 1
2500-0560-002 2
2500-0560-002 3
2500-0560-002 4
2500-0560-002 5
2500-0560-002 6
2500-5991-002 1
2500-5991-002 2
2500-5991-002 3 Scratched
2500-5991-002 4
2500-5991-002 5
2500-5991-002 6

So, if the only data that I had in my table was this, I would want the query to display 2500-0560-002, because there have been six consecutive good lots, but not 2500-5991-002, since lot 3 had a rejection. Note: I didn't show it with this example, but most of the parts I'm dealing with have come in much more than six times, this is just a simple example. I hope this helps you help me! Thanks for the effort so far!

 

[Lanky77]

Just one more thing. I notice that a lot of these questions are mentioning Access 2000. I am actually using Access 97. I don't know what the differences are, but if that matters, there you go.