subquery to replace multiple dlookup (1 Viewer)

[jan@BRU]

Friends,

I have a problem, which seems straightforward to solve, but I can't get around doing it.

I have a table (tMGT) , which contains the following fields:

AircraftType, AirlineDesignator, Airport, MGT

MGT is minimum ground time. The table actually contains the minimum ground time for given aircraft, airline, airport combinations. However, the whole thing is not "complete"... but some "bulk" entries should be used as fallback, if no specific entry exists...

SCH_MGT
AircraftType AirlineDesignator Airport MGT
319 LH FRA 00:45
319 LH MUC 00:30
320 LH FRA 00:55
319 LH (Null) 01:00
319 (Null) (Null) 01:30
(Null) (Null) (Null) 01:45

I query the table with certain values for Aircraft, Airline and Airport and want to get one MGT back, according to the following rules:

If the table has an entry with all criteria matched then
return the MGT for that specific entry
Else
If the table has an entry with aircraft and airline and isnull(airport) matched then
return that entry for MGT
Else
if the table has an entry with aircraft and isnull(airlinedesignator) and isnull(airport) matched then
return that entry for MGT
Elseif
return the entry where all of aircrafttype, airlinedesignator and Airport are null

I can and am doing this with a hierarchical Dlookup:

Mgtvalue = Nz(dlookup("MGT", "SCH_MGT", "all 3 criteria"), Nz(dlookup("MGT", "SCH_MGT", "2 criteria plus Airport is null"), Nz(dlookup("MGT", SCH_MGT", "1 criterion plus airline is null plus airport is null"),dlookup("MGT", "SCH_MGT", "all are null"))))


this sort of works but is horribly slow if I want to use this in a query itself.

Can somebody helpme, how I can formulate a decent subquery SQL to do the same trick: basically create a query which returns exactly one MGT, depending on the selection of the above 3 criteria, and assuming that the underlying SCH_MGT table has at least an entry will criteria set to Null, which is the ultimate fallback.

Many thanks

Jan

 

[spikepl]

Try this first:

1. Go into the query designer, and drag the fields you wish to see onto the grid
2. In the first line of criteria, input your first set of criteria from your IF THEN pseudo code
3. In the second line of criteria, input criteria from your first ELSE. For the field thqt is null you just write IS NULL
4. Repeat for the remainder of your ELSEs

and this query can then be the domain on which to use a Dlookup

 

[jan@BRU]

Hey,

thanks for this reply. I have tried the suggested solution before (with a parameter query):

SELECT SCH_MGT.AircraftType, SCH_MGT.AirlineDesignator, SCH_MGT.Airport, SCH_MGT.MGT
FROM SCH_MGT
WHERE (((SCH_MGT.AircraftType)=[AircraftTypeSel]) AND ((SCH_MGT.AirlineDesignator)=[AirlineDesignatorSel]) AND ((SCH_MGT.Airport)=[AirportSel])) OR (((SCH_MGT.AircraftType)=[AircraftTypeSel]) AND ((SCH_MGT.AirlineDesignator)=[AirlineDesignatorSel]) AND ((SCH_MGT.Airport) Is Null)) OR (((SCH_MGT.AircraftType)=[AircraftTypeSel]) AND ((SCH_MGT.AirlineDesignator) Is Null) AND ((SCH_MGT.Airport) Is Null)) OR (((SCH_MGT.AircraftType) Is Null) AND ((SCH_MGT.AirlineDesignator) Is Null) AND ((SCH_MGT.Airport) Is Null));

While I get the correct result, I also get all of the "fallbacks" ... with that. But the requested result would be to get only one value, instead of multiple rows from the query.

Any further idea?

Many thanks,

Jan

 

[Taruz]

Hi..

Try this query..

select 
          atype, 
          adsgn, 
          aport, 
          MGT
from (
           select
                    nz([AircraftType],"") as atype, 
                    nz([AirlineDesignator],"") as adsgn, 
                    nz([Airport],"") as aport, 
                         MGT
from SCH_MGT )  as trz
where 
          atype=iif([AircraftTypeSel] is null,[atype],[AircraftTypeSel]) 
     and
          adsgn=iif([AirlineDesignatorSel] is null,[adsgn],[AirlineDesignatorSel])
     and 
          aport=iif([AirportSel] is null,[aport],[AirportSel])

 

[jan@BRU]

All,
appreciate your efforts very much. But this one is not working neither.

Again, I would like to have exactly one result from this table. If it's an exact match (all 3 criteria matched), then that one. If no entry like that in the table, try 2 criteria match, if still not, 1 criteria match, and if even that fails, return the record, where all 3 fields are Null...

Still hoping for help.

Best,

Jan

 

[Taruz]

the above query, If you pass criterion, then it field gives the null records.

you according to the above instance, the first two criteria are entered, then the result is what should be?

 

[spikepl]

Check attached db.

1. Open Form1
2. Click the button
3. Close the query that comes up.
4. Now enter the date 01/07/2011 (UK date)
5. Click the button
6. see the query now

Open the Table1 and see tha data
Open the Query1 in design view and see how the conditions were set up - do the same for all your conditions.

Remember that all criteria on one line are related by an AND
each new line of conditions corresponds to an OR

You will only get ONE result if only ONE result corresponds to the inserted conditions. That value you have to pick up either by using a DlookUp, or opening the recordset in VBA and extracting the value from the first (and presumably only) record

 

[jan@BRU]

This is getting embarassing,
with all your help ;-)

Unfortunately the db example isn't exactly what our need neither, although it seems to go in the right direction. As a matter of fact, the criteria (which you are getting from the form) is always set... It's the "rules" in the tables which are sometimes set, sometimes null...

sort of the opposite, of what you are suggesting. I have fumbled around, but can't get it work with that logic neither.

HELP!

 

[spikepl]

In #3 you say that you get the right result. Why do you care if the query returns more than one record? You can still pick up the value as I suggested : by dlookup or from the recordset. If it bothers you, change SELECT into SELECT TOP 1
But you stil have a record, not a single value.

Update.. Ah ok. Because the sequence may not be according to your needs. Then you just need to add ORDER BY for each of the fields . .I don't remmber if nulls come before anything else or after. If so change ASC into DESC and that should be it.