Rookie questions

[just2cool]

How do I query from a table with a specific field (region, for example) that
I need by feeding in another field (location) from another table. The table
with the region field has the matching locations. The user will input the
departure and destination location and I want it to look up the table with
the regions and display the two different regions in the same query.

Do I write SQL statements or is it possible by using the functions within Access?

Regards,
Jason

 

[Newman]

You can add a table twice or more in a query. The new table created will be called the same name adding a number at it's end. When refering to the departure, you'll say something like that: "tblRegion!strRegion" and when refering to the destination, you'll say something like that: "tblRegion2!strRegion".

 

[DrSnuggles]

You're going to have problems with having this in one query.
It might be easier to make 3 queries. The first 2 to filter the data, the final brings the data together for presentation purposes.

First query:
Add the two tables in the Query window and make a link between the field Location. Bring down the Location and Region fields. In the Criteria for the Location type [Enter Departure Location]. By encapsulating the text in these brackets [], it will prompt the user to enter a location.
Rename the Region field to Departure_Region:Region
Save this as qSelDepartureRegion

Second Query:
Add the two tables in the Query window and make a link between the field Location. Bring down the Location and Region fields. In the Criteria for the Location type [Enter Destination Location]. Rename the Region field to Destination_Region:Region
Save this as qSelDestinationRegion.

Third Query:
Add the two queries qSelDepartureRegion and qSelDestinationRegion to a new query. Pull down the fileds Departure_Region and Destination_Region.
Save this query as qSel_Region_Departure_and_Destination_By_Location

All you have to do now is run the query qSel_Region_Departure_and_Destination_By_Location and the user will be prompted by the two input boxes: Enter Departure Location, and Enter Destination Location.

Hope this helps and is not too confusing. Probably the best way if you're new to this.
Please note: If the user misspells a Location it won't return a Region.

 

[just2cool]

Thanks guys. I did Newman's way and it worked.

The thing is I'm asking the user to input the Orig Dest, I'm simply pulling info from a table. But thanks for the tips I will remember it for reference!

Thanks again.