query information from a matrix

[tempk]

Hi All,
I'm new to access and I've a problem that has been hounding me.

Right now, I am utilising a table containing the cost of air fare from one country to the next. It looks something like this:

Origin Destination AirRateBiz AirRateEcon
USA Japan 1200 800
Japan USA 1200 800

This is based on the assumption that the round trips cost the same.

However, I am encountering the problem of having too many permutations to update. I have about 10 countries and there might be more being added in the future. I am looking at a way to make this information easier to update.

I would like the information to be like the following:

Origin USA Japan China
USA 0 1200 1500
Japan 1200 0 500
China 1500 500 0

This will be easier to update in the future, I believe, since if I add a new country, I just need to fill in the corresponding values. However, I have been unable to query the prices because I have no idea how to turn [Destination]=[CountryTo].......

Can I be enlightened on an easier way to solve this problem? The first solution I have works like a charm except updating a new country would be horrible.

Thanks for any help rendered...

 

[Jon K]

You can use the DLookup() function to look up values from a matrix.

I have attached a sample database. You can open the form and select an origin and a destination to display the AirRateBiz.

The DLookup() function is in the On Change event of the combo boxes.
.

 

[tempk]

Wow,thanks for the help! I'll try to get this to work with what Iwant.

Thanks!