Getting One Name From Hundreds!

[Moonshine]

Hi

We currently have a sytem, that well lets say isnt the best. But its what we've got so i've gotta use it!

First we have a tblClient, which as you can guess is a table full of clients, it has about 70000 records in it. We then have a table called tblInvolved_Professional, and that is all of the Professionals that have worked with a client. It is linked via the fields CLI_ID to INP_CLI_ID, if i did a simple query linking those two fields id get thousands of records showing me the Client and all the professionals that have ever worked with them.

What i really want is, the LAST professional that worked with the client. In the Involved Professional Table, there is a INP_FROM and an INP_TO, so when one Professional ends, that record has a INP_TO date entered, and then a new record created and so on....

I did a simple query to get the Max of that, grouped on CLI_ID. This works fine. I do indeed get the last Professional to work with the client. But, when the case gets closed, to keep the Case open for the next review, the last Professional is Ended, and a new record created opening it to a GP Practice (Code like GP***, so a review is not missed.

This doesnt help me at all, what i want is the Last Professional to work with the client, but if the Last Professional is a GP*** code, then get the Professional before that....

Hope that makes any sense...

I thought something like "if STF_CODE like "GP*" then get previous record"

But i cant figure that part out :/

 

[Stuart Heath]

Can you not just strip those records out of your query using criteria?

 

[MattS]

If a client is only ever with one professional, or wth a GP practice, then use criteria like Stuart suggested:

Where (Inp_To Is Null) OR (STF_Code Like "GP*")

 

[Moonshine]

Hi

Thanks for the reply, clients can have more than one professional and unfortunatly they can have more than one at a time.