SubQuery - Return value from table x based on value in table y

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm very new to SQL and Databases so would appreciate any help!

I have a table (Customers) that includes a field with airport codes rather
than cities. I have a separate table (Cities) where there are fields for
airport code and the corresponding city. I want to, in a query pulling other
fields - some with criteria, pull the city name from the Cities table based
on what the code is in the Customers table.

For instance: In Customers, the value of the field is "SLC", but I want the
result to return the city name "Salt Lake City" from the Cities table based
on the matching airport code.

Clear as mud? ;)
Kate
 
I'm very new to SQL and Databases so would appreciate any help!

I have a table (Customers) that includes a field with airport codes rather
than cities. I have a separate table (Cities) where there are fields for
airport code and the corresponding city. I want to, in a query pulling other
fields - some with criteria, pull the city name from the Cities table based
on what the code is in the Customers table.

For instance: In Customers, the value of the field is "SLC", but I want the
result to return the city name "Salt Lake City" from the Cities table based
on the matching airport code.

Clear as mud? ;)
Kate
Click to expand...

No Subquery is needed. Simply create a query adding the Customers table and
the Cities table. Join the two tables on the airport code. Pull the cityname
from the Cities table, and any information you need from the Customer table.
Any of these fields can be used for searching.

John W. Vinson [MVP]
 
John, Thanks for your reply. I failed to mention that I have yet another
table of Flights being used as well. Your solution may still work, and I'll
give it a try. The problem is that the Customers table will have several
different airport codes on it based on the different flights - so I have to
reference the Cities table based on the value of the city field on the
Customers table.. just a tad tricky - unless I'm making it too hard!

Thanks!
Kate
 
John, Thanks for your reply! I failed to mention I have a third table in
the query of Flights. This may not change your solution and I'll give it a
try. Because the Customers table includes several different flights, I have
to have the city name pulled from the Cities table based on the value in the
Customer table field. I'm not quite sure how to do that.. unless I'm making
it too hard. I just want to say "If field A in Customers says "blah", then
return the value of "yadda" based on it's corresponding field of "blah"....
 
John, Thanks for your reply. I failed to mention that I have yet another
table of Flights being used as well. Your solution may still work, and I'll
give it a try. The problem is that the Customers table will have several
different airport codes on it based on the different flights - so I have to
reference the Cities table based on the value of the city field on the
Customers table.. just a tad tricky - unless I'm making it too hard!
Click to expand...

An airport ID should not be an attribute of a Customer at all; much less
should there be multiple airport codes in each record!!

I'm not understanding your table structure at all, it seems. Could you post
the structures of your two tables in the format

Tablename
Fieldname (Datatype, Primary Key)
Fieldname (Datatype)
Fieldname (Datatype)

so I can see what you're working with?

John W. Vinson [MVP]
 
Okay, I have two main tables; Customers and Flights. (fields are text unless
indicated otherwise)

Customers:
Indicator Code
Record Locator
Flight #, number
Departure Date, date/time
Origin
Destination
Customer Name
Email address
Travel Agent

Flights:
Flight #, number, primary key
Departure Date, date/time, primary key
Origin, primary key
Destination, primary key
Amount
Entertainment
Delay
Reason

The side table would be Airports, where I have two fields:
Airport Code, primary key
City Name

I import a file which contains all the data for the Customers table, and the
primary key information for the Flights table. I then have a form to add the
remaining information on the Flights table (which gives specific delay,
outage information for a flight). I then combine certain pieces of
information from both tables in an export.

What I'm hoping to do is swap out the airport code from the "origin" field
for the city name when pulling the data in a query (for the export).

I will have a list of, say, three different flights with three hundred
customers total. Obviously everyone on the same flight will have the same
origin, however, I want to add to the query or *somehow* auto-swap that
information so when we export the data, the city name shows up rather than
the airport code. (It is not ideal to change the airport code within the
Customers or Flights tables to the city code, but if possible, just for the
export.)

.....does that make better sense?

Thanks so much for your help!
Kate
 
Back
Top