Too many queries

[joey345]

I have been given the task of stream-ling our database. Previously someone created a range of queries, and then forms which open these queries through command buttons.

For example -

Query the main table for a country - Albania (for example)

Then query a query - Query Albania - for contact type - say expert

In each individual country there was 20 different contact types, with each of these queried. So for one country alone, you have the initial filter of country, and then 20 additional queries.

These were then opened via command buttons on forms.

As you can imagine, when you times that by the amount of countries you are looking at thousands of queries in total. This has obviously led to the database being extremely cumbersome. The original point to this method was that within a few clicks you could have isolated - the country and type of contact incredibly quickly... Is there another way of redesigning the database to make this possible?

Many thanks

 

[MSAccessRookie]

It sounds like the dataabse is either badly normalized, or that the previous programmer took little or no advantage of what relational Databases can do. I would like to see a set of these Queries.

The database should have at least the following Tables

tblContacts:

ContactID (PK)
(Other Information)

tblContactType:

ContactTypeID (PK)
(Other Information)

tblCountries:

CountryID (PK)
(Other Information)

tblMain

MainTableID (PK)
CountryID (FK)
ContactTypeID (FK)
(Other Information)

 

[plog]

Yes, use forms. Lets say you have 5 queries--each for a different country, but each returning the same fields, just with data for their particular country. What you should do is make a base query that eliminates the country criteria, you still have it return the country name--its just shows all 5 countries' data together.

Then you create a form with a drop down that holds those 5 countries names and a button beneath it. When you select a specific country and click the button, it opens that query, but just to the country selected.

You can do this through VBA with a filter command, or you can do it directly in the query putting a reference to the drop down in the criteria section under country:

=[Forms]![SearchMenu]![CountrySelect]

 

[Brianwarnock]

Erm it's been a while as everything is these days but can't you have filtered combos so that on plog's form there are2 combos, the first selects country, the second is filtered by this, in its creation query, and just lists the contacts for that country, then the command button runs the query that pulls the selection.

Additionally by leaving either combo empty with the correct criteria in the queries you can select all of the particular experts world wide or all of a countries experts.

Brian

 

[joey345]

Dear everyone, thanks for you help.

Plog, I like idea and I am kind of getting there but keep hitting a wall.

When I enter this =[Forms]![SearchMenu]![CountrySelect]

Do I leave [CountrySelect] generic or make it the specific country that I want to select. Also, do I do this before or after I have created the form.

I've been staring at the screen that long, that basic brain functioning is a problem...

Many thanks

 

[plog]

=[Forms]![SearchMenu]![CountrySelect]


That refers to a control on a form (specifically the control named "CountrySelect" on the form called "SearchMenu". So, if your that control nor form, the above means nothing. It's essentially a variable. A variable that is controlled by the value of the drop down on the form.

Here's what you should do.

1. Create your query with the code we are talking about (=[Forms]![SearchMenu]![CountrySelect]) in the criiteria area under the country field.

2. Create a form called "SearchMenu" and place an input box on it. Name that input box "CountrySelect"

3. Open the form and then manually run the query. You should get no results, or results where counry is blank.

4. Close the query, type a valid country name into the form (i.e. "Canada", "Mexico", etc.) and re run the query. You should only get results matching those you typed into the form.

5. Once that works, add a button to your form that opens the query when clicked. Once working, play with it--type in various values into the form and then click the button to make sure it returns correct results.

6. Change the input on the form to a drop down. For this you will need a data source to feed the drop down that lists the countries you want to appear. That may be in a table you have, or it may require you write a query.

 

[joey345]

Thanks a lot Pog.

When you say 'input box' which box do you mean?

Many thanks,

 

[plog]

a text box control.

 

[Brianwarnock]

You did not comment on rookie's or my posts and your subsequent questions to plog suggest that you are struggling with certain concepts.
You should be using combo boxes populated from you Tables and by using cascading combos the data in the second is relevant to the first.

Read this thread and although I haven't looked at Pat's example her record on here is such that I believe it would illustrate the situation very well.

http://www.access-programmers.co.uk/forums/showthread.php?t=223980&highlight=Cascade

Brian