Comparing Quality Control Data to Samples in the same Batch

I work in a forensic toxicology lab and I use a SQL server to compile data from data analysis software and transfer the data to an xml template. For more complex calculations that the data analysis software does not do I use an excel query to perform the calculations. The LC-MS/MS system that I use runs staggard injections to double the throughput of the batch using two HPLC columns. I have to show that the QC controls are within the specified range on each column, so each of the controls ("LOQ" & "NIDA") are ran through each column (Column 1=Stream 1, Column 2=Stream 2). LOQ and NIDA are always named the same way:

LOQ: 1 q ###### (where # is the batch #)
NIDA: 2 m ###### (where # is the batch #)
BNEG: 3 ######### (where # is a randomly generated specimen #)

There is a 3rd Control (BNEG) that is ran on both streams but it does not contain pertinent QC information. Once the controls are ran, the samples are then ran in sequenitcial order, but alternating streams.
Example:

Stream Sample
1 1 q 123456 (QC)
2 1 q 123456 (QC)
1 2 m 123456 (QC)
2 2 m 123456 (QC)
1 3 314515686 (QC)
2 3 314515686 (QC)
1 4 123456789 (Sample)
2 5 987654321 (Sample)
1 6 456987654 (Sample)
2 7 654987456 (Sample)

If a QC sample is out of range on Stream 1 for a particular analyte then any sample on Stream 1 that is positive for that analyte needs to be flagged "QC" or If a QC sample is out of range on Stream 2 for a particular analyte then any sample on Stream 2 that is positive for that analyte needs to be flagged "QC". I have figured out how to flag if the QC is out of range either "High", "Low", or "Pass". I am sure there is a formula that could check if QC is out of range and the stream # is the same then flag, but I can not figure it out. I have attached a sample of once of the queries from the temp folder that might make it easier to understand. Let me know if there are any questions. Thanks!

I have been looking at your sample but I can't follow as to what it is you are looking for.

Where do you want the formula to go and, using your sample, what results would you expect the formula to return, and why?

The formula should go into the Sample_QC Check Column under the Calculations Tab. The example I posted is a temp query file. The query itself fills down from starting with the second row for each sample. I have attached the example temp. query file with notes on where the formula should go and the results I expect and why. Also I put a copy of the actual query file so you can see the difference. I use the temp. query file to debug the formulas.

OK, I THINK this might do what you want.

It is for cell I17 and dragged up/down:

Please Login or Register  to view this content.

Let me know if it works as required.

Damn, I posted the wrong one:

This one is for cell I18 and draggable up/down:

Please Login or Register  to view this content.

A slightly shortened version - for cell I14 and dragged down:

Please Login or Register  to view this content.

I have attached the temp query file that ran after I used your formula. I am getting a #REF error in the cell. This is probably due to the nature of the query. It has to be formula that is in cell I2 b/c that can fill down as each sample is brought in.