how to write this in 1 select statement

M

marianne

Table 1:

System Function
____________________
A Func1
A Func2
A Func3
B Func1
B Func3
C Func1
C Func2
D Func1
D Func2
D Func3
E Func2
E Func3
....


Table 2

User System
__________________
User1 A
User2 D
....


Inputparameter : string of functions ( "Func1-Funct2-..." ) separted by a
specific char.

Question :
How to select from table1 all systems not assigned to a user and
having all of the functions mentionned in the inputprm implemented.

For inputprm = "Func1-Funct2-" the answer would be :

System
______
C

(1 record)


Thanks in advance.
 
T

Tom Ellison

Dear Marianne:

This is not a simple thing to do, and there may not be enough
information in your original post. I'll make some explicit
assumptions and suggest a way it could be done on that basis.

According to your example, System A would be a correct result, since
it contains Func1 and Func2. I'll interpret this to be that you want
only those systems that have exactly those functions and no other.
Right?

Place the list of functions (your "inputparameter") into a temporary
table "IP" having cleared it first.

I'll assume that Table 1 has no duplicates of System/Function. This
simplifies the problem so that all we have to do is to COUNT() the
number of rows found. When there are 2 functions in inputparameter
and the number of rows found in Table 1 is 2, then you have an exact
match. Make sense?

SELECT DISTINCT System
FROM [Table 1] T1
WHERE
(SELECT COUNT(*) FROM IP) =
(SELECT COUNT(*) FROM [Table 1] T1a
INNER JOIN IP ON IP.Function = T1a.Function
WHERE T1a.System = T1.System)

Doing this without creating the temporary table will be rather
difficult. However, this may make for a rather nice user interface.
You could put a continuous subform on the screen for this "temporary"
table along with buttons to clear it and to put all functions in it.
You could also allow it to persist by not clearing it between uses, if
that would be a desirable approach.

The temporary table would be one in the "front end" database rather
than the back end, assuming you have split the database. If it is a
single user database then this doesn't matter.

Any help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
M

Michel Walsh

Hi,


=================

SELECT x.Sys

FROM (
SELECT Table1.System As sys,
"*-" & Table1.Function & "-*" As fct
FROM table1 LEFT JOIN table2
ON Table1.System=Table2.System
WHERE Table2.System IS NULL
) As x


WHERE ("-" & param ) LIKE x.fct

GROUP BY x.Sys

HAVING COUNT(*) = len(param) - len( Replace(param, "-", ""))

====================

param has to have a number of - equal to the number of 'values' (ie, it
has to end with a - ), and * is assumed to be the wildcard. It also
assumes you use Access 2002 or 2003, so len(string) - len(replace(string,
"-", "" ) returns the number of "-" in the string.


The virtual table x removes the assigned systems, like the unmatched query
wizard does, and prepare a "pattern" to be used with the operator LIKE.
Note that the parameter occurs at the left of LIKE, which is not its most
commonly illustrated use. Definitively, then, the field name, used at the
right, acts like the pattern. So, may be explicit explanations may be
required about it:


"-" & 'func1-func2-" LIKE "*-func1-*"

returns true, while

"-" & 'func1-func2-" LIKE "*-func9-*"
returns false.


Remember that the parameter is at the left, and the field, at the right. So,
that uses simulate an IN( ) list, where the list is with a variable amount
of values. It is versatile, but can be slow. Probably preferable to use an
intermediate table, instead, if the number of involved records, in table x,
is high.



Hoping it may help,
Vanderghast, Access MVP
 
M

marianne

Thanks a lot Tom and Michel for your answers, I will try out when i am again
at my clients company.
marianne
 
Top