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.
0
Utf
12/10/2009 8:58:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
618 Views

Similar Articles

[PageSpeed] 54

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



"No1momof3" <No1momof3@discussions.microsoft.com> wrote in message 
news:FCBB47B4-6C93-4C91-AF2A-B068B3AFB532@microsoft.com...
> 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. 

0
vanderghast
12/10/2009 9:27:35 PM
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

0
KenSheridan
12/11/2009 12:48:08 AM
Reply:

Similar Artilces:

Round up a decimal in a query field
Hopefully this is an easy question. How do you round up a number in a query field? Excel has the ROUNDUP function, but I can't find anything similar in Access. Can anyone help? Function:round([tablename!fieldname,2]) I think. It may be round([tablesname!fieldname],2) "bigomega73" wrote: > Hopefully this is an easy question. How do you round up a number in a query > field? Excel has the ROUNDUP function, but I can't find anything similar in > Access. Can anyone help? Thanks Golfinray, but that only rounds to the nearest decimal place. What I want is the numb...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

Can Drop Down Boxes jump to the answer as you type?
If I create a form with VBA, to create a drop down box to have a list to pick an answer for to fill in a cell in a worksheet, it will jump to the answer in the list as you type. Can a Data|Validation|List do the same thing? If not, is there a way to get that functionality in Excel? Data Validation doesn't support autocomplete. If you can use programming, there are instructions here for adding a combobox from which you can select one of the values from the data validation list. In the combobox, you can enable autocomplete: http://www.contextures.com/xlDataVal11.html rrucksda...

Restricting input box entries to integers
Dear Experts: below macro applies a user-defined paragraph style to rows using an input box. The macro is running fine. But the input box also allows for entries such as 7,2 (comma because I live in Germany). How do I have to re-write the code to only allow integers as input box entries? Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub Tbl_BodyStyle() Dim oRng As Word.Range Dim oTbl As Word.Table Dim AskRowNumber As String Dim blnAsk As Boolean If Not Selection.Information(wdWithInTable) Then MsgBox "Please place the cursor...

Self-Made Combo Boxes not working correctly...
Okay all.. Here's my deal... I have tried and tried and tried to get these to work... I'm going to post what I have done, and what I am trying to do, and see if someone can help me figure crap out... tblBuilding - PK is BuildingIDNum - Autonumber tblBuildingZones - FK is BuildingIDNum via SELECT DISTINCTROW Building.BuildingIDNum, Building.Name FROM Building ORDER BY Building.BuildingIDNum; cboBuilding - This has Building 1, Building 2, etc. The field displaying this info is "Name" in tblBuilding. cboBuildingZones - This has different "zones" for Building 1,...

Selecting rows from various sheets #4
Cheers Fran -- sha ----------------------------------------------------------------------- shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1198 View this thread: http://www.excelforum.com/showthread.php?threadid=26665 ...

Need help with Combo Box?
I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. Sure...

Do I need DSClient to run Exchange 5.5 on an NT4.0 box in Windows 2003 Native Mode ADS?
Good Evening, I am in the process of migrating my WinNT4.0 domain and Exchange 5.5 Org to Windows 2003 ADS/Exchange 2003. I know best practice is to change the domain to Native mode, but how does this affect my NT4.0 server running 5.5? Do I need to simply load the DSClient onto the server? I should also note that we did an inplace upgrade from NT 4.0. This is a single domain environment. As long as you no longer have NT 4.0 BDCs you should be able to move to native mode. Your NT 4.0 server running E55 will not be affected (unless of course it is also a BDC). It is a good idea to...

SQL Query
I'm trying to create a query that will result in receipt transaction details from POP10310 combined with GL transactions from GL20000 and so far no success. I assume I also need POP10300 in the mix. Which field(s) should I be joining between GL20000 and POP10300 or PO10310? Thanks. Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL Try using POP30310 instead. The receipt transactions would have to be posted before anything got to GL20000. -- Jim@TurboChef "Frank Hamelly, MCP-GP" wrote: > I'm trying to create a query that will result in receipt transaction &...

Table name length in microsoft query
Hi! Is it true that the maximum length of a tablename is still 8 characters. I'm useing Excel 97 and trying to get data from a Paradox 9 table via odbc-link and Microsoft Query. I have heard a few years ago that long names are OK and supported all over the microsoftian regime. Is there any trick to avoid this without shortening all the names. Greetings from Helsinki HK ...

Query by Form Problem 06-19-07
I'm using QBF with about six different combo boxes using: [forms]! [formmain]! [combo1] or [forms]![formmain1]![combo1] -like in a VBA book. This is so users can select criteria on a form with the combo boxes, and when they are done, they hit the search button, and it opens up another form based on the query just performed by the combo selection. This worked for about three combo boxes, but when I added another one, it freezes up and opens up a blank page. It's supposed to open the new form based on the query. Is there a better way to do this? I've looked exhaustive...

Query-based distribution group anomaly
When I do a TOOLS ~ OPTIONS ~ DELEGATES in Outlook 2003 and try to set a query-based distribution group as a delegate it seems to accept the group and whatever permissions I assign. But as soon as I click OK and then come back in to inspect it, the query-based distribution group has no permissions at all. This happens every time. However, in the same situation, whenever I assign a Global Security Group as a delegate and assign permissions, they stick. Should my query-based distribution group be accepted or is this kind of group not permitted as a delegate? But then if it's not perm...

Using skins in a multiline edit box
Hi, I have a dialog based application, in which I have a multi-line edit control. In the edit control, I would like to have a background image, say some .bmp file. Some status messages are to be displayed in the edit control, based on certain user actions in the dialog. How do I go about this? Thanks, Sucharit you can do it, i think, if you were to create a class which inherited from the edit control, then in the override the OnDraw()/OnPaint() event, where you first get the rect, and draw your bitmap resource, then call the parent event method to do the rest... I don't know if t...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

Why do sheet tabs keep disappearing? Box in Tools is checked.
When I open a new or existing Excel file, the sheet tabs at the bottom may or may not show up. I have repeatedly gone to Tools, Options, View and made sure the sheet tabs box is checked, but still no tabs. How do I get them back? I can't get from one page of a workbook to another. This is in Offfice 2003. Hi Arlie, Try Tools/Options/General In the Sheets in new workbook box, check and see how many sheets are set to appear when opening a new workbook. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://w...

multiple selection
How can I select multiple records into a list box? I want that for append identical records (for select records) in another table. Many thanks Florin Use a subform instead of a list box. In a subform bound to a related table, you can append as many rows as applicable. It is possible (but not simple) to use an unbound multi-select list box that is populated in the Current event of the form, and executes Append, Delete, and Update query statements based on the ItemsSelected if they are changed. You will also have to find a way to respond to the user "undoing" changes. -- Al...

Check Boxes #6
Is there an "easy" way to add check boxes to a worksheet? As it stands, I have to manually attach it to the underlying cell, and adjust the size. I would like to add several hundred checkboxes. Am I doing somethig wrong, or is it just that akward and labor intensive in Excel? Hi Adam, Try: '=============>> Public Sub Tester01() Dim SH As Worksheet Dim rng As Range Dim rCell As Range Set SH = ActiveSheet '<<==== CHANGE Set rng = SH.Range("A1:A100") '<<==== CHANGE Application.ScreenUp...

Synchronize combo boxes
I am trying to sync to combo boxes on a form. It is for plant names which consist of Genus + Species + Subspecies, basically. I have all my data in a single table and I want only applicable species to show up for a particular Genus, for example: Abies concolor Abies glauca Abies alba Quercus alba Quercus macrocarpa Any genus can have many species, some of those species names can exist within multiple Genus names, I have made to seperate tables with just Genus and just Species names, I want them to link in the combo boxes, any ideas? Previously responded to... Regards ...

Random List from Query
For accrediation purposes we have to audit a random 10% of our cases each quarter I was wondering if there is a way to run a query or report that would let me show a random 10% of cases for a specific provider. ...

auto fill in customer when contact selected
Hello, I hope Bertil reads this :) or someone with the same knowledge :) In a case when a user selects a contact is it possible to auto fill in the customer related to the contact?? Any help mucho appreciated. You can do this using javascript. onchange function of the field. -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Ian" wrote: > Hello, > > I hope Bertil reads this :) or someone with the same knowledge :) > > In a case when a user selects a contact...

Conditional Formatting for Image box on Continuous Forms
Hello again, This problem is driving me crazy. I am a car enthusiast and love taking photos of classic cars. I have a table which describes the make and model in a series of fields. There are several fields that contain a reference to photos of those cars. Ie: the fields are called "Front" "Side" "Rear" "Angle". My form has four image boxes to display the images stored in these fields. But when I open the form all the records show the images of the first car only. I asked about this a few days ago and was advised to check out http://www.lebans.co...

Combo Box 11-21-07
Hi I have a database where on the main form (it's a pop-up), there's a combo box that allows the user to select a person's name. That should bring up the record and display the data. However, for some reason, it's stopped working? The Name field at the top of the form now reads "#Name?" even though the field is specified as "Client"&FirstName& and I get an error message of "The expression After Update you entered as the event property setting produced the following error: Object or class does not support the set of events" Can someone ...

Selection.AutoFilter Issue
I have the same code for 5 different reports. The only difference in code is the criteria filtered and removed. All reports filter and remove the desired data just fine except for one. The code is below and it is the same each reports with the exception of the worksheets it is deleting and Criteria1 for each sheet. NOTE: The BCP E&M code below filters correctly but not the others...and they are the same code! Any help would be greatly appreciated. Dim myLastRow As Long Dim myLastColumn As Long Dim myLastCell As String Dim myRange As String Dim Newbook As String Di...

How do I assign a set of values to a selection from a drop list?
Hi. I am trying to assign a set of values, in separate fields ,to a each selection from a drop-down list in Excel (using data validation). What I am trying to do is very smilar to, say, to selecting a SKU from a list and having the product description, unit price etc. fill into their corresponding fields automatically. For example, I pick SKU "11111" from a drop-down list to fill the SKU field, then, automatically, "5-inch widget" comes up in the product description field and "$5.00" shows up in the unit price field. Please help. Mike Along with the d...

Selecting Charts in a Macro
I have a worksheet which contains 7 charts: 1st Chart = Chart 12 2nd Chart = Chart 7 3rd Chart = Chart 11 4th Chart = Chart 13 5th Chart = Chart 24 6th Chart = Chart 16 7th Chart = Chart 26 I have a macro which copies the worksheet it over to a new worksheet within the same workbook, the charts are then labelled: 1st Chart = Chart 9 2nd Chart = Chart 7 3rd Chart = Chart 13 4th Chart = Chart 10 5th Chart = Chart 2 6th Chart = Chart 12 7th Chart = Chart 15 The macro then tries to select each chart and change the source data, what I don't know is how excel will treat the chart numbers for...