Counting through records

[chrisbrooke]

I have a field in a record to hold a test case number, for each record I input a new test case number. For a particular device I have 42 test cases. Therefore, when complete there should be 42 records for that device. I already have a query that tells me the amount missing (if any), what I want to do now is display the actual test case numbers that are missing i.e. test case 2 is missing etc.. thanks in advance if u can help me. Chris.

 

[dcx693]

I'm working on a potential query solution for this, but one way to do this is to use VB to go through the devices one-by-one and check each case number for existence.

 

[dcx693]

Unless you have a very small number of devices, the query solution will not be feasible. At least - not my query solution. You may have to get down and dirty with some VB.

 

[WayneRyan]

Chris,

What you need is a query in this form:

sql = "Select TestID, TestCaseNumber
      "From   TestCases
      "Where  TestID = " & Forms![YourForm]![TestID] " And " & _
      "       TestNumber Not In " & _
      "       (Select TestNumber " & _
      "        From   AllTests " & _
      "        Where  TestID = " & Forms![YourForm]![TestID] " And " & _
      "               AllTests <= " & DMax("[TestID]", "TestCases", TestID = " & Forms![YourForm]![TestID]))

Or something like that. The table AllTests just has the numbers
one through the maximum number of test steps.

Change field names accordingly.

Wayne

 

[dalenz]

Try this,

Set up a table with the values 1 thru 42.

Run an unmatched, ie is null, on your test results table against this table.

Missing numbers should be the result..

 

[dcx693]

dalenz's idea was the same initial idea I had that I didn't post. The only problem is - it won't work.

You need not only get the numbers 1 to 42, but you also need them by device. Meaning that device x might have numbers 1 to 40, but device y might have numbers 1 to 10. The unmatched query would pull up numbers 41 to 42, wouldn't it? The result you want is 41 to 42 for device x and 11 to 42 for device y.

 

[namliam]

dcx, Chris, try :

First you make a query getting all products. This you match with your numbers Table (no join)
Result is ALL products ALL Numbers

This then outerjoin to your test table... and done...

Regards

 

[dcx693]

namliam,I had previously considered this as well , the problem is how do you create that query that has all the device and incident numbers? Create a query that contains unique device numbers, then create a query based on that query that has the numbers 1-42 for each device. I guess it's not so hard to do. At some point, I just figured VBA would be easier.

 

[namliam]

Just for the Heck of it I created a little (learning) sample, check it out and tell me what you think (A97 format)

Regards