Search Allowing greater specification of filters.

[DatabaseIntern]

Hello,

I have created a database designed to hold informatoin on both "Modules" and the "Micros" they pertain two. A module is an individual component used in a microcontroller (Think individual types of legos) and a Micro is a chip that can consist of many Modules (think fininshed lego "creation"). Each module can be used in multiple micros, and each micro can consist of multiple modules (this is why I like the lego analogy, each lego can be used to help build more than one possible "creation", and each creation uses more than one lego.) In addition there is some info that pertains to each "Intersection" of the matched up module and micro (although I am not sure that is relevant for my question I want to lay out my DB design/needs as clearly as possible).

I have created my Db as 3 tables, a "modules" table, a "Micros" table, and a "Micromodules table to hold the data for each occurence of a module matching with a micro. To the best of my knowledge (which is admittedly limited) I have created the database according to the principles laid out by Ted Codds database principles, as outlined here:



DBprinciples[1].pdf

What I want to do is to be able to create an "Advanced Search" that will allow me to create more advanced reports/queries than simply "contains Micro1" - I would like to be able to specify something like "Show me the modules that are used on both micro 1 and micro 2" and have the query return only the modules that pertain. (In the case of the below example that would be Modules 1 and 5.) and Even go so far as to say "show me the Modules that are on Micro 2 and 3, but NOT on Micro 4, the more flexibility the better. As of now I am working on an SQL builder as I think thats the only way to allow this sort of flexibility, but since each record has only 1 link (since MicroMods is a list of individual intersections) I cannot figure out how to get any results other than "show me micro1 or micro2, etc...) Any help with this issue would be greatly appreciated, as I suspect there is a clever way to make this work.

Here is a simplified layout of the database to help clear things up:



Please let me know if you have any questions regarding my post. Thanks in advance for taking the time to read this, these forums are a lifesaver lately:

[June7]

I don't think an Access query object could be designed with parameters to handle this.

Might be able to build VBA code that will construct the WHERE clause of an SQL statement or the WHERE CONDITION of OpenForm/OpenReport. It's not difficult to construct criteria with all OR operators or all AND operators. The complexity builds when mixing them.

Multi-select listbox might be relevant to your situation. Review http://allenbrowne.com/ser-50.html

[DatabaseIntern]

So I have been giving this a lot of thought (This issue is still keeping me up at night unfotunately) and I think I have an idea that might work. If I could make some VBA that was to cycle through each module and check that it meets the requirements, and if so leave it "True" and if not set it to be filtered out I think that could work, but I am not sure how to do that. some code that looks something like this. *I am not sure how to VBA code so I am psuedo coding here, splitting each element of my idea into a bracket.

'This is all psuedo code, Any help turning it to workable code is appreciatted
'
dim SQL as String

For [Each Module Name] If [Micromodules Query contains]( [Module name AND MicroName(1)] AND [Module name AND MicroName(2)])
Then EndIf
Else SQL = SQL & "AND [ModuleName] <> " & chr(34) & [Current Module Name] & chr(34)
EndIF

'Dont worry about the starting a where with AND, i will handle that seperately. 

If I thinking through this correcttly wouldnt this systematically generate an SQL statement eliminating all the Modules that do not meet the requirements, and if so is it possible to code in VBA?

Also is there a way to move this thread to the programming section? as It appears to have taken a turn in that direction.

[June7]

I moved your thread.

Your example allows for two micros with AND operator. This can easily be done in a query. For more versatility to select any number of micros as criteria, I suggested a mulit-select listbox. Then VBA code will construct the sql WHERE clause in a loop that concatenates each selected micro. The referenced link is an example of this looping structure. This still allows for only one operator type.

[DatabaseIntern]

Yes, I agree with the listbox Idea for forming the criteria portion,

The issue is that since each record is a record of only 1 module and 1 microcontroller (And the data that goes with that particular intersection) if i were to simply SQL [microname] = "micro1" AND microname] = "Micro2" I would get nothing back. If i did OR I would get a list of all Records with Micro 1 and Micro 2, but i want to limit it to modules with BOTH. So I really need to know if there is a Query.Contains[Module AND Micro] or something, so that I can get data on which modules are on both. Maybe some tricky work with a repeats query? but that seems quite convoluted.

[June7]

You're right about the AND operator. I was asleep on that one. I built table based on your example. Built a crosstab query.
TRANSFORM Count(MicroModules.Micro) AS CountOfMicro
SELECT MicroModules.Module
FROM MicroModules
GROUP BY MicroModules.Module
PIVOT MicroModules.Micro;

The output is like:

Module	Micro1	Micro2	Micro4	Micro5	Micro6
1	1	1
3	1		1
4				1
5	1	1
6			1	1	1

Now I can construct SQL that will query the crosstab for all Modules where Micro1 AND Micro2 Not Is Null.

[DatabaseIntern]

Brilliant! I've been working with this all morning to great success! I am very pleased with the results, Thank you once again Super Moderator, I am in your debt.