selecting a query from a combo box

[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.

 

[vanderghast]

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

 

[KenSheridan via AccessMonster.com]

You can create an unbound dialogue form with controls into which to enter the
year, institution or country, with each being optional. The year control can
be a text box (though a combo box would be better), but the country and
institution controls should be combo boxes.

Assuming you have separate Countries and Institutions tables with one row per
Country/Institution respectively (I'll come back to what to do if you don't)
the RowSource properties for the country and institution combo boxes would be:


SELECT Country FROM Countries ORDER BY Country;

and:

SELECT Institution FROM Institutions ORDER BY Institution;

ON the same form add a command button to open the report (the control wizard
can create this for you). You should only need one query and report unless
you want to layout or sort the three reports differently, in which case you
could probably use the same query for each report, and have three buttons on
the form, one to open each report.

In the query on which the report is based you will need to enter parameters
which reference the controls on the dialogue form, so first remove the
existing parameters and in the year column's 'criteria' row in query design
view enter the following, as a single line in each case:

Forms![YourDialogueForm]![txtYear] OR Forms![YourDialogueForm]![txtYear] IS
NULL

In the countries column enter:

Forms![YourDialogueForm]![cboCountry] OR Forms![YourDialogueForm]![cboCountry]
IS NULL

In the institutions enter:

Forms![YourDialogueForm]![cboInstitution] OR Forms![YourDialogueForm]!
[cboInstitution] IS NULL

Change the form and control names to the actual names you've given the form
and the three controls being very careful to get the names exactly the same.
One thing to note is that if you save the query and then open it again in
design view Access will have moved things around. The underlying logic will
be the same, however, and they'll work in the same way.

To open the report you'd now open the form, and enter/select a value from any
of the three controls. Each is optional so you can enter/select from any one,
two or all three in combination, or even leave then all blank to return all
records in the report.

Once you've selected the value(s) you want in the controls click the button
to open the report.

If you don't have separate Countries and Institutions tables you should
really create and fill them with append queries based on your Incoming
students table. If you first make the Institution and Country columns the
primary keys of the two tables, when you then append these columns from the
Incoming students table only one row for each country and institution will be
inserted into the relevant table.

Even without these tables you can still do the above, however, though its not
ideal. You'd just change the RowSource properties of the combo boxes on the
dialogue form to:

SELECT DISTINCT Country FROM [Incoming students] ORDER BY Country;

and:

SELECT DISTINCT Institution FROM [Incoming students] ORDER BY Institution;

Ken Sheridan
Stafford, England