selecting a query from a combo box

  • Thread starter Thread starter No1momof3
  • Start date Start date
N

No1momof3

HELP! Need to design a DB for my boss and I am lost!

I have a database which lists students who have went on exchange over the
last 17 years to over 20 countries and numerous institutions.. I have set up
3 queries/reports using parameters so the user can enter: 1) the year 2) the
country or 3) the insitution. Now the problem is the insitution query as the
name of the institution can get spelt various ways so I would prefer the user
to select the institution from the drop down box which they use to enter the
data into the table under the field "institution".

My file is called: Exchange 1986 onward
The table is called: Incoming students
The field is called: Insitututions

Can anyone please help - I am not well versed in sql and just enter it into
the crieria section of the query but am willing to try anything.
 
I am not sure I understand the whole problem, but it seems a case of
'translation'. A typical example would be to make some statistic by 'city'
but where the city name may be written in different ways.

So, have a table CitiesAlias, two fields City, AliasOf with data like:

City AliasOf ' fields
----------------------------
Constantinople Istanbul
Byzance Istanbul
Québec Quebec City
Bytown Ottawa ' data



Then, someone can use:

SELECT
Nz( CitiesAlias.AliasOf, myTable.CityName) As useThisName
FROM mytable LEFT JOIN CitiesAlias
ON myTable.CityName = CitiesAlias.City




As example, if mytable.CityName owns the value Constantinople, the
useThisName will be Istanbul. If myTable.CityName is Paris, then useThisName
will be Paris. In other words, table CitiesAlias list the possible ways to
mention a given city and if a city name is NOT in that table, then it is
considered the proper name to be used, through the use of an outer join and
the function Nz.


In your case, it would be a matter to make a table of the different
spellings and supplying the 'official' spelling which has to be used. If an
institution has no a unique spelling, it is not an obligation to supply it,
ie, it is NOT required to have:


City AliasOf
---------------------
Paris Paris




Vanderghast, Access MVP
 
Back
Top