Query question

[Accessbegin]

Hi, I am a beginner learning MS Access in school.


And I faced this problem while doing my project.

The problem that I need to do is to create report based on the query I should create.
The thing that I don't know is how to make a field in query to lookup(?) value that matches from another value.
I am not sure if it quite makes sense but.. what I am trying to do is like:




To make depature/arrrival city to lookup the value from another table "CITY" so that it will show city names instead of numbers.
I am not sure if certain relationship is needed to make this work...

Thank you

[CJ_London]

in your query query, drag the flights table on once and the cities table on twice (the second one will have _1 added to the end) from the navigation window.

then create a link from departure city to the first city table by clicking on departure city and dragging to the matching field in the city table. Do the same for arrival city to the other city table

now drag down to the grid at the bottom the fields you want in your report

[Accessbegin]

Thank you very much.

It worked perfectly. But why it doesn't work when there is only one city table (without city_1) if I may ask.. ?

[CJ_London]

Not sure what you mean by 'it doesn't work...' You have two links to the same table but from different fields - so you have two copies of the table. Take one of the tables away and it will still work, but you can't report on data in the table you have removed.

the table name .._1 is what is called an alias - open the properties for the table in the query window and you will see it there.

Aliasing is a good habit to get into as you can make the names more relevant to the task in hand

e.g.

SELECT ... FROM citytable as departurecity....citytable as arrivalcity

or cut down on the amount of typing

SELECT ... FROM citytable as D....citytable as A

particularly as many newbies insist on really long over descriptive table and field names, often with spaces

[the data I got from excel last week].[interest after deducting tax]