RE: tough query (for me)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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





"Earl Takasaki" wrote:

> 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?
> --
> Earl Takasaki
> Master of Financial Engineering Candidate
> University of California at Berkeley
.

Relevant Pages

  • Re: data entry via query
    ... website to a query rather than a table and putting validation rules on the ... col1 INTEGER NOT NULL, ... col2 INTEGER NOT NULL, ...
    (microsoft.public.access.queries)
  • Numeric range query help needed
    ... I have a clustered unique index on Col1, Col2. ... The problem is that a query like this takes approx. ...
    (microsoft.public.sqlserver.programming)
  • Re: Newbie - Sum the total
    ... select Col1 From table Group By Col1 ... Now you decide to add Col2: ... You have to tell the query engine how to aggregate Col2 (min, ...
    (microsoft.public.inetserver.asp.db)
  • Re: TSQL Connect Two Tables Together?
    ... Do you mean that COL1 and COL2 come from separate tables and you want to ... JOIN them in a query? ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting multiple records into two tables...with a twist
    ... I assume that you have an IDENTITY column, ... reason would typically be a high rate of concurrent insertions.) ... INSERT tblB(id, col1, col2, ... ...
    (comp.databases.ms-sqlserver)