Lookup auto field

  • Thread starter Thread starter Eric Cohen via AccessMonster.com
  • Start date Start date
E

Eric Cohen via AccessMonster.com

First, my english is not so good, so I am gonna explain you the problem the
best I can.
I have a Table with 3 Fields:
Field1 = Medical Test
Field2 = Range1 (in number of days)
Field3 = Range2 (in Number of days)

Here is a sample Table values:

MedTest Range1 Range2(BlankField)
AATI-F 253
AATI-F 350
AATI-F 85
AATI-F 128
ABCO3-N 50
ABCO3-N 103
ABFI2-N 15
ABFI2-N 52

Now, let me explain you the problem:
I would like the Field "Range2" be computed. Let me explain you how ....
with an example.

If I take the previous sample, I would like my Table be this way:

MedTest Range1 Range2(BlankField)
AATI-F 253 128
AATI-F 350 253
AATI-F 85 0
AATI-F 128 85
ABCO3-N 50 0
ABCO3-N 103 50
ABFI2-N 15 0
ABFI2-N 52 15

So, I would like :
- Sort By each MedTest : (Ex - AATI-F)
- Lookup how many record I have for this Test ( Ex - 4 records)
- And Put the exact Range2 (Ex - AATI-F -----> 128 ------> If exist less
than 128 y/n? -----> Yes ------> So Range2 = Equal 85.

Please any ideas or help will be appreciated

Thanks
 
Hi,


SELECT a.MedTest, a.Range1, Nz(b.Range1, 0) As Range2
FROM (myTable As a LEFT JOIN myTable As b
ON a.MedTest=b.MedTest AND a.Range1 > b.Range1)
LEFT myTable As c
ON a.MedTest=c.MedTest AND a.Range1 > c.Range1
GROUP BY a.MedTest, a.Range1, Nz(b.Range1, 0)
HAVING Nz(b.Range1, 0) = MAX( Nz(c.Range1, 0) )


(untested) should do. You can simplify the formulation if the lower bound,
0, is also included, since then, you can change the LEFT joins to INNER
joins, and remove the Nz( )s.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top