Comparing results of two queries (1 Viewer)

[diversoln]

I'm using a query to set-up the format for my report. My table has 5 fields:

[Case]
[Patient]
[Specimen]
[Block]
[BlockNum]

A patient may have more than one specimen, which in turn may have more than one block. The blocks are identified as A, B, C etc. The BlockNum's are 1, 2, 3 ... n. This information is downloaded from a medical database in this format. On the report, I only want the [Case] and [Patient] to appear once for each patient and then list all the specimens and block totals for each.

I've come up with two queries, but I'm not sure how to get them to work together. The first query determines the total number of blocks for each specimen and does some formatting of the patient name. The second query determines the block ID for the first block for the patient (unfortunately it's not always "A")

Here's the SQL for the first Query:


SELECT DISTINCTROW APWorklistTable.CASE, APWorklistTable.PATIENT, Left(UCase(Left([PATIENT],InStr([PATIENT],',')-1)),10) AS SHORTPAT, APWorklistTable.SPECIMEN, APWorklistTable.BLOCK, Max(APWorklistTable.BLOCK_NUM) AS [Max Of BLOCK_NUM]
FROM APWorklistTable
GROUP BY APWorklistTable.CASE, APWorklistTable.PATIENT, APWorklistTable.SPECIMEN, APWorklistTable.BLOCK
ORDER BY APWorklistTable.CASE, APWorklistTable.BLOCK;


Here's the SQL for the second Query:

SELECT APWorklistTable.CASE, First(APWorklistTable.BLOCK) AS FirstOfBLOCK, Max(APWorklistTable.BLOCK_NUM) AS MaxOfBLOCK_NUM, First(APWorklistTable.PATIENT) AS FirstOfPATIENT
FROM APWorklistTable
GROUP BY APWorklistTable.CASE
HAVING (((First(APWorklistTable.BLOCK))<>"A"))
ORDER BY APWorklistTable.CASE, First(APWorklistTable.BLOCK);


I'd like to use an IIf function that compares the [Block] in the first query to First(APWorklist.BLOCK) from the second query for each patient. If these are equal for a patient, its the first occurrence so the [CASE], [PATIENT] [SPECIMEN], [BLOCK] and [MaxOfBLOCK_NUM] should show up on the report, if not, "" will take the place of the [CASE] and [PATIENT] on the report and only the [SPECIMEN], [BLOCK] and [MaxOfBLOCK_NUM] will actually print.


Any ideas ?

 

[diversoln]

I found a solution so here it is.... I used a third query to join the first two queries on the [CASE] field. I also pulled a couple fields out of the second query that weren't really needed leaving only [CASE] and [FirstOfBLOCK].

The IIf statements worked like a charm after I made the join.

Here's the SQL for the third query that did the trick:

SELECT APWorklistQ.CASE, APWorklistQ.PATIENT, APWorklistQ.SHORTPAT, APWorklistQ.SPECIMEN, APWorklistQ.BLOCK, APWorklistQ.[Max Of BLOCK_NUM], FirstBlock.FirstOfBLOCK, IIf(([BLOCK]=[FirstOfBLOCK]),[APWorklistQ.CASE],"") AS SHOWCASE, IIf(([BLOCK]=[FirstOfBLOCK]),[APWorklistQ.SHORTPAT],"") AS SHOWPAT
FROM FirstBlock INNER JOIN APWorklistQ ON FirstBlock.CASE = APWorklistQ.CASE
ORDER BY APWorklistQ.CASE, APWorklistQ.BLOCK;



Maybe this all could have been done through a single query or maybe two, but using the three queries ended up being a straightforward solution & did the trick for me.