Queries That Ue look Up Values ?

[jimtin]

Hi

I hope someone can help, I have the following scenario:-


My database has:-

Tables


Only 2

Main- This contains 2 fields, one called names of people, the other called fav fruits. The fav fruits filed looks up the table below( fruits) to find the fruit i.e the user doesn’t type in his own he selects from he lookup list.
Fruits-This has 1 filed called fruits and has every fruit entered into it.


Query

I have one query, (Q display all) this looks up all data from Main and displays it

Form


I have one form based on the query (Form main) above which is where all data is added and deleted. The fruits filed again is a lookup field so the user can't type in his own.

Scenario

My database has grown to millions and now I wish to write some queries (run in form view) that will Group per favorite fruit only. I decide I can do this two ways:-

1. Create a separate query for each fruit and therefore a separate form for each fruit as I wish to display them in form view. Now I don’t want to do this as imagine there are 1000`s of fruits , well this would not be acceptable as I need to keep the queries down to a minimum. No Go


2. Using the [Enter fruit Name] function in query design view the query would prompt the user to enter the name of the fruit, if it finds a match then it will display the records, if no match found it will display nothing. This is not a good idea as my users can't spell and so 9/10 they would get zero results back. No Go

Q

Can I create a query where the user will select a fruit from the drop Down Box Table (Fruits) remember or use this value to run a query on the table Main and show all records that matched, depending on which fruit he selected. ?


Appreciate any information

 

[DJBummy]

This may be a round about way of doing this but it works.
Create a new query called lets say qryFavFruit. In the criteria row of the FavFruit field enter this [Forms]![frmFavFruitChoice]![cmbFavFruit].
Create a popup form called frmFavFruitChoice with just a drop down box on it called cmbFavFruit. Assign a procedure to the On Click event of the Combo Box that minimizes this form and opens another form based on the qryFavFruit query. Create a command button on this form to exit and return the user to the FavFruitChoice form or to exit both.
hth.

DJ