LIKE function match values

D

dorionsmom

I'm trying to use a data field in the LIKE Function...
This is my data..first line is thefiled names ...each additional line is a
sample record..

Flight # LOS
2 V05
345 V20
345D/267 B55
1545 S09
647 L04
647D/1234 V06

The D denotes that the number following "/" is the second flight. All
flights have an LOS (service level). I want the query to produce a record
that looks like this:

Flight # LOS1 LOS2
2 V05
345 V20 B55
1545 S09
647 L04 V06

Can anyone help me please...... I want to use the [Flight #] in the LIKE
function to search all flight numbers and pull the second service level for
the second flight...
 
K

KARL DEWEY

Close but no cigar ---
SELECT dorionsmom.[Flight #], dorionsmom.LOS AS LOS1, dorionsmom_1.[Flight
#], dorionsmom_1.LOS AS LOS2
FROM dorionsmom, dorionsmom AS dorionsmom_1
WHERE ((((dorionsmom_1.[Flight #]) Like [dorionsmom].[Flight #] & "*D/*")))
UNION SELECT dorionsmom.[Flight #], dorionsmom.LOS AS LOS1, null as [Flight
#], null as LOS2
FROM dorionsmom;
 
J

John W. Vinson

I'm trying to use a data field in the LIKE Function...
This is my data..first line is thefiled names ...each additional line is a
sample record..

Flight # LOS
2 V05
345 V20
345D/267 B55
1545 S09
647 L04
647D/1234 V06

The D denotes that the number following "/" is the second flight. All
flights have an LOS (service level). I want the query to produce a record
that looks like this:

Flight # LOS1 LOS2
2 V05
345 V20 B55
1545 S09
647 L04 V06

Can anyone help me please...... I want to use the [Flight #] in the LIKE
function to search all flight numbers and pull the second service level for
the second flight...
Click to expand...

Assuming that the "second service level" is always the first few bytes of the
Flight# field try a Self Join:

SELECT A.[Flight #], A.LOS AS LOS1, B.LOS AS LOS2
FROM yourtable AS A
LEFT JOIN yourtable AS B
ON B.[Flight #] LIKE A.[Flight #] & "*"
WHERE B.[Flight #] <> A.[Flight #];

This won't work for three-leg flights but you can join the table a third time
as C with similar logic if that's needed.

The problem is, of course, that your Flight # field is not atomic: storing two
pieces of information in one field is a bad idea for this very reason.
 
Top