有一列与访问中另一个表中的2个不同字段相关吗？(having one column related to 2 different fields in another table in access?)

有一列与访问中另一个表中的2个不同字段相关吗？(having one column related to 2 different fields in another table in access?)

我正在为我的公司建立一个空运货物访问数据库。 我有一张包含所有机场的桌子，而且我还有另一张包含空运费用的桌子。 以下是每个表的字段（a *表示它是键的一部分）

tblAirports

AirportID *（自动编号） AirportCode（文字 - 3字母IATA代码） AirportCity（号码 - 连接到城市表）

tblAirFreight

OriginAirport *（数字 - 连接到tblAirports） DestAirport *（数字 - 连接到tblAirports） 公司*（编号 - 连接到公司表） 50公斤 100公斤 500公斤。

空运与机场桌面相连，在始发地和目的地服务上具有参考完整性。

现在，即使是简单的选择查询也不适用于空运表。 我想输入原始机场代码（SEA，JFK等）和/或目的地机场代码，并查询以返回相应的空运费率。 我该怎么做呢？

I am building an access database for air freight for my company. I have a table with all the airports, and I have another table with air freight costs. The following are my fields for each table (a * means that it is part of a key)

tblAirports

AirportID*(Autonumber) AirportCode(Text - 3 letter IATA code) AirportCity(Number - connects to a cities table)

tblAirFreight

OriginAirport* (Number - connects to tblAirports) DestAirport* (Number - connects to tblAirports) Company* (Number - connects to a table of companies) 50 kgs. 100 kgs. 500 kgs.

Air freight is connected to the airport table with referential integrity on both origin and destination services.

Right now, not even a straightforward select query will work on the air freight table. I want to type in an origin airport code (SEA, JFK, etc.) and/or destination airport code, and the query to return the corresponding rates for air freight. How do I do this?

最满意答案

听起来您想要获取货运表中每个机场列的机场详细信息，因此您只需加入机场表两次。 然后根据提供的参数进行过滤。 这只是粗略指向正确的方向，你可能需要稍微使用where子句。

select * from tblAirFreight f inner join tblAirports o on o.AirportID = f.OriginAirport inner join tblAirports d on d.AirportID = f.DestAirport where (f.OriginAirport = @YourOriginAirport or isnull(@YourOriginAirport) = 1) and (f.DestAirport = @YourDestAirport or isnull(@YourDestAirport) = 1)

It sounds like you want to get the airport details for each of the airport columns in the freight table so you just join to the airports table twice. then you filter based on the parameters supplied. This is only rough to point you in the right direction, you will probably need to play with the where clause a bit.

select * from tblAirFreight f inner join tblAirports o on o.AirportID = f.OriginAirport inner join tblAirports d on d.AirportID = f.DestAirport where (f.OriginAirport = @YourOriginAirport or isnull(@YourOriginAirport) = 1) and (f.DestAirport = @YourDestAirport or isnull(@YourDestAirport) = 1)