tough query (for me)

[Guest]

I am trying to perform a couple of tough queries (for me)...

First Q:

Suppose we have a table with two columns Col1 and Col2.
I would like to find all of the records where there is no matching record
for Col1 in Col2 of all of the records, or no matching record for Col2 in all
of the records in Col1. For example, let's say we had the following data:

Table
Col1 Col2
A B
B C
C B
E A
A F
G H

Query result:
E A
A F
G H

In this table the query would return records 4 because the is no E in Col2,
5 because there is no F in Col1, and 6 because there is neither a G in col2
nor an H in Col1.


Second Q:
I'm also trying to produce the "Complement" of a join.

Let's say the there is a table with a key field and another table with the
same field. If perform a join on the key field, then display the fields of
the first table, i will get all of the records where the first table has a
match in the second field. However, i would like to return all of the records
in which the key field does NOT have a matching record in the second field.
Can I do this?


Finally, I want to write an expression that will return TRUE if a value is
within a table field. For example, suppose we have the following table:

TABLE1
Col1
A
B
C

I would like to write an expression (say in another query) that looks
something like

iif(myChar is a subset of (Table1.Col1), "Yes", "No")

Can this be done?

 

[Guest]

Earl:

I am fairly new to Access and SQL, so there might be better ways to do these
things, but as a long time programmer, I have a habit of turning to writing
things in code.

In answer to your question 2, a problem I had, since your cannot use OUTER
JOIN (which would solve the problem) if you access more than two tables in a
query, I had to write VB code which used one query which obtained all of the
first (two) table's information and placed it into arrays, then ran many
queries based on the ID's in first array. I then had to rerun the first
query and use its ID field to get the information from the arrays and
populate my report. A very round-about way of doing a report, but it works
because I don't but a maximum of 300 records.

In answer to question 1, I would again write VB code where the first query
would obtain data from both Col1 and Col2 into a recordset. I would then run
through each Col1 data, search for like data in Col2. If not found, write
Col1 and Col2 data to an array. Then run through each Col2 data to see if it
matches any in Col1, check the array to see if you had previously recorded
that record.

Question 3 (last). I have done something simular to what you want. I wrote
a function which would return what I wanted in the report based on a field
returned from the query. In the report textbox, put "=YesNo([Field])"
without the quotes. Then your function would be (assuming Col1 and myChar are
single characters):
Public Function YesNo( strChar as String, myChar as String) as String
' where strChar is Col1
If myChar = strChar then
YesNo = "Yes"
Else
YesNo = "No"
End Function
If myChar is always the same, you would not have to pass it as a parameter
Hope this helps.

John H W

 

[Sylvain Lafontaine]

#1: easy: use the NOT IN statement:

SELECT * from table1
where Col1 not in (select distinct Col2 from table1)
or Col2 not in (select distinct Col1 from table1)

WITH OWNERACCESS OPTION;

You could also use the Not Exists (....) statement.

#2: also easy: this is the definition of the LEFT OUTER JOIN.

#3: probably another easy one: use the Exists statements to return a logical
result; however, I'm not sure of your definition of "myChar" but if we take
Col2 as the definition of myChar:

SELECT T1.*, IIF (Exists (Select * From Table1 T2 where T1.Col2 = T2.Col1),
"Yes", "No") as Result from Table1 as T1
WITH OWNERACCESS OPTION;