If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
selecting a query from a combo box
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. |
#2
|
|||
|
|||
selecting a query from a combo box
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 No1momof3 wrote: 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. -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|