Multi-level/field query -- Any suggestions?

[Melee]

I have a db with several hundred check boxes in multiple tables.  The boxes encompass 1.Areas of the World, ie Pacific Rim; 2.Countries in those Areas, ie Belize; 3.Product Categories, ie Dried Fruit; and 4.Specific products, ie Dried Plums.  I need to be able to respond to inquiries which can include multiple instances from each category...For instance, Provide a list of suppliers who ship to the Caribbean and Central America, specifically Barbados, Virgin Islands and Belize.  The supplier must alo provide Dried Fruit, specifically Dried Plums, Raisins, and Walnuts.  Any ideas on how to do this?

 

[Amiel]

Sorry but this is not as nearly as complex as it seems &lt;smile&gt;.<br><br>You'll use the all purpose SQL statement !<br>as in<br><br>&quot;Select * from Suppliers Where Barbados =-1 and&nbsp;&nbsp;VirginIslands = -1 and Walnuts = -1 and AreBehindOnPayments = 0&quot;<br><br>Translated it means select all suppliers who ship to Barbadoes <b> and </b> the virginislands <b> and</b> who handle Walnuts <b> and </b> who are not behind on their payments.<br><br>Further translated in VB language <i>-1</i> = checked or true and <i>0</i> = unchecked or false.<br><br><br><br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[Melee]

Amiel,&nbsp;&nbsp;Thanks for your input. Please forgive me if I seem dense. Sometimes the light doesn't come on right away.&nbsp;&nbsp;I understand the SQL statement; I am not certain how that would accomplish my goal.&nbsp;&nbsp;<br><br>What I am attempting to do is set this up so the user can select the criteria on a form and the ensuing query returns a list of only those that meet the criteria entered on the form (I have the form portion created).&nbsp;&nbsp;The query will create a subset of the Region (Western Europe, for example) and the Product Category (Animal Products).&nbsp;&nbsp;From that subset the fields containing the specific data are queried for True condition -- In the Western Europe Table the countries (if any) are queried (UK, France, etc.) and in the Animal Products Table the commodity fields (Horsehair, Goat Milk, Goose Eggs, etc.)identified are queried for the true condition.<br><br>If 'Western Europe' is true then check true value of all identified countries in Western Europe and If 'Animal Products' is true then check true value of all identified products in the Animal Products Category. Return a list of Suppliers that meet the criteria.<br><br>

 

[elizabeth]

This is just theory as I haven't worked much with multi-select so I could be totally off but am imagining the following tables: <br><br>tblArea: AreaID, AreaName<br>tblCountry: CountryID, CountryName, AreaID<br>tblCategory: CategoryID, CategoryName<br>tblProduct: ProductID, ProductName, CategoryID<br>tblSupplier: SupplierID, SupplierName<br>tblSupplierCountry: SupplierID, CountryID<br>tblSupplierProduct: SupplierID, ProductID<br><br>The 1 to many relationships are probably obvious <br>A form has 4 comboboxes, with multiselect enabled.<br>cboArea<br>cboCountry(limited to countries selected in cboArea)<br>cboCategory<br>cboProduct(limited to products selected in cboCategory)<br>Your query contains tblSupplier, tblSupplierCountry & tblSupplierProduct.<br>You select suppliers from tblSupplier that have a match on Country AND Product.<br><br>

 

[Melee]

Elizabeth,<br>That would be a way out of my dilemna, but may not be the solution for these guys.<br><br>tblCompany Data: CompName, Caribbean, EasternEurope, PacRim, etc. (Regions are check boxes); Plants, AnimalProd, ProcessedVeg, DriedFruit, etc. (also Check boxes)<br>tblCountry: CompName, Barbados, Bahamas, Jamaica, Malta, etc. (Countries are check boxes)<br>tblCommodity: CompName, HorseHair, GoatsMilk, Tubers, LivePlants, Prunes, Walnuts, etc. (Check boxes).<br><br>The possible combinations is mind-boggling.&nbsp;&nbsp;Check Boxes were chosen because of the tremendous number of choices.<br><br>Any ideas are more than appreciated.<br><br>Thanks,<br><br>PS. My VBA skills are weak, but I'm learning.

 

[elizabeth]

Melee, there may be more to the story than what I understand from your post, but your database doesn't <i>seem</i> to be normalized. If you are new to database design, you need to be concerned about this because normalizing your tables is the first step in building a database application. For instance, how will you maintain data integrity? How will you ensure that prunes are related to plants, and goat's milk to animal products? Not through hardcoding I hope. Another example, do you must have a way to ensure that company name is spelled correctly every time it is entered. The usual way to so this is to use a combobox on each form that looks up Company name from a single location.

 

[Melee]

Elizabeth,<br>Thanks for the politically correct wording.&nbsp;&nbsp;This is not a strong suit of most developers.<br><br>Yes, I understand the normalization of the data and I use the combo box selection process for other information. The Company Name is one of those.&nbsp;&nbsp;(Select from list, No duplicates allowed).&nbsp;&nbsp;I do have tables set up to tie the country to the region; the commodity to the category; etc. The reason I used the check box approach is that the supplier (Company) could supply any number of commodities to any number of countries.&nbsp;&nbsp;It was not feasible to have a combo box for each of the 500 plus commodities and 150 plus countries. <br><br>The way I have it set up (for example)is a pop up form that opens when the Region is selected and provides the country choices for that region.<br><br>There may be a better way to do this and I am certainly open to suggestions.&nbsp;&nbsp;Reinventing the wheel is not my idea of a good time.&nbsp;&nbsp;<br><br>So, I'm thinking maybe a loop through the columns of a given table to find the records.

 

[elizabeth]

OK, another idea... There is an old Microsoft Knowlege Base article, on how to use two listboxes (as seen in many wizards). One is full of the choices, and you select an item from that list box and then use a &quot;&gt;&quot; button to add it into the empty listbox. Sounds like this might be useful to you, although you'd need to use two sets, one for country and one for product. You might also display Country in the Selection Listbox as &quot;Area-Country&quot; (concatenated)&nbsp;&nbsp;in alpha order so that it is fast for the user to navigate. Ditto with &quot;Category-Product&quot;. This setup would give you the values you need to write the type of query Amiel suggested.<br><br><A HREF="

http://support.microsoft.com/support/kb/articles/Q97/5/18.ASP"

TARGET="_new">

http://support.microsoft.com/support/kb/articles/Q97/5/18.ASP</A>

 

[Melee]

Tell me if I am totally out to lunch on this possibility--<br>1. Make temp table with Company Name<br>2. Search criteria based on user input form and append table<br>3. Eliminate duplicate entries<br>3.1 Retrieve addresses and phone<br>4. Process resulting list.<br><br>The only problem I see at this point is making the query generic enough to avoid multiple queries doing essentially the same thing.

 

[elizabeth]

You seem to be creating a lot of extra work for yourself. If you need to create new tables to run a query that is at the heart of your app's functionality, then your tables are probably not optimized for your function. (An exception that comes to mind is a temp table created by a union query). I always rant about table design, so just ignore this if you're happy with yours.&nbsp;&nbsp;

 

[Melee]

OK Maybe, I get carried away sometimes. But while your button may be tables, mine is the ease of use from the client's perspective, so I tend to become excessively creative (or obsessive) sometimes.

 

[Melee]

Thanks for the inputs.&nbsp;&nbsp;I solved the problem VBA code.

 

[Guest_imported]

I would be interested to see the code used. If not too extensive could you post some here PLEASE