Sorting problem

[rcreed1]

I have a field (called source) that will have a 0,6, or a 7 as it's data.

I then have a field that has a flight number (input as a text since it is comprised of both letters and numbers).

Some of these flight numbers appear three or more times once with a 0, once with a 6, and once with a 7 in the column 'source'. (Some may appear twice - with a 0 or a 7, and some only once - with a 0 or 7 or 6).

Ok if I haven't confused everyone yet what I am trying to do is display all the flight numbers that have a '0' in the source column when that same flight number has an entry with a 6 in the source column and a 7 in the source column.

As I had stated the flight number may appear several times. I only need the '0' flights that are also '6 & 7' flights.

Is this possible?
Thank you

 

[KenReay]

Hi Not sure why you described this as a Sorting Problem, maybe I do not understand your question, but I think teh answer is SQL something like so

SELECT * FROM FlightsTable WHERE Source = 0 AND FlightNo IN (SELECT FlightNo FROM FlightsTable WHERE Source = 6 OR Source = 7) ORDER BY FlightNo;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[rcreed1]

If I understand your suggestion it wouldn't work in my case because I have about 13,000 flight numbers to go through.

so my columns would look like this

source flight number
0 ab123
6 ab 123
7 ab 123
0 ac 12
7 ab 12
7 dc 124
6 af 56

I would only want the query to pull 0 ab123 since that flight has a 6 & 7 also.
Again, realize I have about 13,000 lines of info.
Thank you

 

[KenReay]

Hi

OK so you need

SELECT * FROM FlightsTable WHERE Source = 0 AND FlightNo IN (SELECT FlightNo FROM FlightsTable WHERE Source = 6) AND FlightNo IN SELECT FlightNo FROM FlightsTable WHERE Source = 7) ORDER BY FlightNo;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Genimuse]

I don't see what the number of rows has to do with it. The select will return the records you want, though I think based on the logic you describe you might need one more nesting level (though this might be done faster with a union):

SELECT * FROM FlightsTable WHERE Source = 0 AND FlightNo IN (SELECT FlightNo FROM FlightsTable WHERE Source = 6 [COLOR=blue]AND FlightNo IN (SELECT FlightNo FROM FlightsTable WHERE Source = 7)[/color]) ORDER BY FlightNo;

The inner-most select grabs all flights with a 7, then the select outside that grabs all flights with a 6 that are in that first list, then the outermost select grabs all flights with a 0 that are in that combined 6 & 7 list. The result is a recordset with only the flights that have a 0, 6, and 7.

 

[KenReay]

Hi

Sorry missed out a (

SELECT * FROM FlightsTable WHERE Source = 0 AND FlightNo IN (SELECT FlightNo FROM FlightsTable WHERE Source = 6) AND FlightNo IN (SELECT FlightNo FROM FlightsTable WHERE Source = 7) ORDER BY FlightNo;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Another way:
SELECT A.*
FROM (FlightsTable A
INNER JOIN FlightsTable B ON A.[flight number] = B.[flight number])
INNER JOIN FlightsTable C ON A.[flight number] = C.[flight number]
WHERE A.Source = 0 AND B.Source = 6 AND C.Source = 7

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244