Help with Union & lookup

H

Hugh self taught

Hi Everyone,

I have a table with competitor_idx, competitor.fname, competitor.sname

In another table I have couples.id, couples.cplnumber, couples.maleid,
couples.femaleid

The maleid & femaleid are PK competitor_idx. My objective is to create a
lookup with any part of the first & or surname from competitor & list (if
there is a match) the cplnumber, male partner & female partner from couples.
There will be competitors who do not have a record in couples & they still
need to be listed.

What I envisage is a text box on a form with the query result as a subform.

Any assistance greatly appreciated as my brain has departed with any
faculties I had!
 
C

Clifford Bass

Hi Hugh,

Try this which pulls up first all of the couples and then the single
people:

SELECT P1.competitor_idx, P1.fname, P1.sname, C.cplnumber,
P2.competitor_idx, P2.fname, P2.sname
FROM (competitor AS P1 INNER JOIN couples AS C ON P1.competitor_idx =
C.maleid) INNER JOIN competitor AS P2 ON C.femaleid = P2.competitor_idx
WHERE P1.fname = [Forms]![frmYourForm]![txtfname_search] OR P2.fname =
[Forms]![frmYourForm]![txtfname_search]
UNION ALL
SELECT P1.competitor_idx, P1.fname, P1.sname, Null, Null, Null, Null
FROM (competitor AS P1 LEFT JOIN couples AS C1 ON P1.competitor_idx =
C1.maleid) LEFT JOIN couples AS C2 ON P1.competitor_idx = C2.femaleid
WHERE C1.id Is Null AND C2.id Is Null AND P1.fname =
[Forms]![frmYourForm]![txtfname_search];

Where frmYourForm is the name of your form and txtfname_search is the
name of a first name-search text box on that form.

Clifford Bass
 
H

Hugh self taught

Hi Clifford,

Thanks for this so far. First reference to C1.id & C2.id is "WHERE C1.id Is
Null AND C2.id Is Null AND P1.fname =" so they have to be input from the
keyboard. I also want to use a "LIKE" type lookup but my syntax Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" gives me an error.

Any ideas on getting past these 2 issues?

Thanks
Hugh

Clifford Bass said:
Hi Hugh,

Try this which pulls up first all of the couples and then the single
people:

SELECT P1.competitor_idx, P1.fname, P1.sname, C.cplnumber,
P2.competitor_idx, P2.fname, P2.sname
FROM (competitor AS P1 INNER JOIN couples AS C ON P1.competitor_idx =
C.maleid) INNER JOIN competitor AS P2 ON C.femaleid = P2.competitor_idx
WHERE P1.fname = [Forms]![frmYourForm]![txtfname_search] OR P2.fname =
[Forms]![frmYourForm]![txtfname_search]
UNION ALL
SELECT P1.competitor_idx, P1.fname, P1.sname, Null, Null, Null, Null
FROM (competitor AS P1 LEFT JOIN couples AS C1 ON P1.competitor_idx =
C1.maleid) LEFT JOIN couples AS C2 ON P1.competitor_idx = C2.femaleid
WHERE C1.id Is Null AND C2.id Is Null AND P1.fname =
[Forms]![frmYourForm]![txtfname_search];

Where frmYourForm is the name of your form and txtfname_search is the
name of a first name-search text box on that form.

Clifford Bass

Hugh self taught said:
Hi Everyone,

I have a table with competitor_idx, competitor.fname, competitor.sname

In another table I have couples.id, couples.cplnumber, couples.maleid,
couples.femaleid

The maleid & femaleid are PK competitor_idx. My objective is to create a
lookup with any part of the first & or surname from competitor & list (if
there is a match) the cplnumber, male partner & female partner from couples.
There will be competitors who do not have a record in couples & they still
need to be listed.

What I envisage is a text box on a form with the query result as a subform.

Any assistance greatly appreciated as my brain has departed with any
faculties I had!
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Hugh,

The C1.id and C2.id should not be causing a parameter box unless you do
not have an "id" column in the couples table. Your original post indicated
there was. It there is not, use another field such as the "cplnumber" field.
You can use any column in the table as long as the column cannot contain
null values in the table. The Like part may be working, but you would not
know it while the C1.id and C2.id issue is there.

If that does not help, post your actual SQL.

Clifford Bass
 
H

Hugh self taught

Hi Clifford,

Okay The first part I've corrected & works. The Like is throwing out a
syntax error (missing operator) in the first WHERE statement. The actual SQL
is below.

SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, C.CoupleNumber,
P2.Competitor_idx, P2.First_Name, P2.Surname
FROM (Competitors AS P1 INNER JOIN Couples AS C ON P1.Competitor_idx =
C.MaleID) INNER JOIN Competitors AS P2 ON C.FemaleID = P2.Competitor_idx
WHERE P1.First_Name = Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P2.First_Name = Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*"
UNION ALL SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, Null, Null,
Null, Null
FROM (Competitors AS P1 LEFT JOIN Couples AS C1 ON P1.Competitor_idx =
C1.MaleID) LEFT JOIN Couples AS C2 ON P1.Competitor_idx = C2.FemaleID
WHERE C1.CoupleID Is Null AND C2.CoupleID Is Null AND P1.First_Name = Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*";

Maybe I'm missing some brackets but I've not had the time to experiment.
Maybe on the weekend. Your help is greatly appreciated. BTW that "LIKE"
expression I'm using works fine in another SQL query but with 3 sets of
brackets.


Clifford Bass said:
Hi Hugh,

The C1.id and C2.id should not be causing a parameter box unless you do
not have an "id" column in the couples table. Your original post indicated
there was. It there is not, use another field such as the "cplnumber" field.
You can use any column in the table as long as the column cannot contain
null values in the table. The Like part may be working, but you would not
know it while the C1.id and C2.id issue is there.

If that does not help, post your actual SQL.

Clifford Bass

Hugh self taught said:
Hi Clifford,

Thanks for this so far. First reference to C1.id & C2.id is "WHERE C1.id Is
Null AND C2.id Is Null AND P1.fname =" so they have to be input from the
keyboard. I also want to use a "LIKE" type lookup but my syntax Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" gives me an error.

Any ideas on getting past these 2 issues?

Thanks
Hugh
Click to expand...
Click to expand...
 
H

Hugh self taught

Hi Clifford,

What a twit I am... how can I have = Like
I realised it while I was driving to site. Now that I've fixed that I did a
search for "gar" I ended up with 3 gary (first name)but there should also
have been a Legarde (surname) Any suggestions on that?

Hugh self taught said:
Hi Clifford,

Okay The first part I've corrected & works. The Like is throwing out a
syntax error (missing operator) in the first WHERE statement. The actual SQL
is below.

SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, C.CoupleNumber,
P2.Competitor_idx, P2.First_Name, P2.Surname
FROM (Competitors AS P1 INNER JOIN Couples AS C ON P1.Competitor_idx =
C.MaleID) INNER JOIN Competitors AS P2 ON C.FemaleID = P2.Competitor_idx
WHERE P1.First_Name = Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P2.First_Name = Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*"
UNION ALL SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, Null, Null,
Null, Null
FROM (Competitors AS P1 LEFT JOIN Couples AS C1 ON P1.Competitor_idx =
C1.MaleID) LEFT JOIN Couples AS C2 ON P1.Competitor_idx = C2.FemaleID
WHERE C1.CoupleID Is Null AND C2.CoupleID Is Null AND P1.First_Name = Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*";

Maybe I'm missing some brackets but I've not had the time to experiment.
Maybe on the weekend. Your help is greatly appreciated. BTW that "LIKE"
expression I'm using works fine in another SQL query but with 3 sets of
brackets.


Clifford Bass said:
Hi Hugh,

The C1.id and C2.id should not be causing a parameter box unless you do
not have an "id" column in the couples table. Your original post indicated
there was. It there is not, use another field such as the "cplnumber" field.
You can use any column in the table as long as the column cannot contain
null values in the table. The Like part may be working, but you would not
know it while the C1.id and C2.id issue is there.

If that does not help, post your actual SQL.

Clifford Bass

Hugh self taught said:
Hi Clifford,

Thanks for this so far. First reference to C1.id & C2.id is "WHERE C1.id Is
Null AND C2.id Is Null AND P1.fname =" so they have to be input from the
keyboard. I also want to use a "LIKE" type lookup but my syntax Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" gives me an error.

Any ideas on getting past these 2 issues?

Thanks
Hugh
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

I don't see where you are searching the surname field in the query you posted.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi Clifford,

What a twit I am... how can I have = Like
I realised it while I was driving to site. Now that I've fixed that I did a
search for "gar" I ended up with 3 gary (first name)but there should also
have been a Legarde (surname) Any suggestions on that?

Hugh self taught said:
Hi Clifford,

Okay The first part I've corrected & works. The Like is throwing out a
syntax error (missing operator) in the first WHERE statement. The actual SQL
is below.

SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, C.CoupleNumber,
P2.Competitor_idx, P2.First_Name, P2.Surname
FROM (Competitors AS P1 INNER JOIN Couples AS C ON P1.Competitor_idx =
C.MaleID) INNER JOIN Competitors AS P2 ON C.FemaleID = P2.Competitor_idx
WHERE P1.First_Name = Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P2.First_Name = Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*"
UNION ALL SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, Null, Null,
Null, Null
FROM (Competitors AS P1 LEFT JOIN Couples AS C1 ON P1.Competitor_idx =
C1.MaleID) LEFT JOIN Couples AS C2 ON P1.Competitor_idx = C2.FemaleID
WHERE C1.CoupleID Is Null AND C2.CoupleID Is Null AND P1.First_Name = Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*";

Maybe I'm missing some brackets but I've not had the time to experiment.
Maybe on the weekend. Your help is greatly appreciated. BTW that "LIKE"
expression I'm using works fine in another SQL query but with 3 sets of
brackets.


Clifford Bass said:
Hi Hugh,

The C1.id and C2.id should not be causing a parameter box unless you do
not have an "id" column in the couples table. Your original post indicated
there was. It there is not, use another field such as the "cplnumber" field.
You can use any column in the table as long as the column cannot contain
null values in the table. The Like part may be working, but you would not
know it while the C1.id and C2.id issue is there.

If that does not help, post your actual SQL.

Clifford Bass

:

Hi Clifford,

Thanks for this so far. First reference to C1.id & C2.id is "WHERE C1.id Is
Null AND C2.id Is Null AND P1.fname =" so they have to be input from the
keyboard. I also want to use a "LIKE" type lookup but my syntax Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" gives me an error.

Any ideas on getting past these 2 issues?

Thanks
Hugh
Click to expand...
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Hugh,

Yep, the = Like would cause a problem. And as John indicated, you will
need to add the search of the surname field also as separate conditions. Two
more Or ... in the first select and a new Or ... in the second. I would
enclose the two or-ed items in the second one in parantheses just to make
sure they are distinct from the two And parts. .. And ... And (firstname
like ... Or surname like ...).

Clifford Bass
 
H

Hugh self taught

Thanks guys,

What if I join First_Name & " " & Surname AS [Competitor Name] then I only
need to check against one field? I can't get past P1.[Competitor Name] in the
syntax though. I get an error referring to the SELECT reserved word or
misspelled argument. Clearly I'm not as far down the road understanding SQL
as I thought I was.

How do I change the query to combine First_Name & Surname?
 
C

Clifford Bass

Hi Hugh,

You could do the combination of the fields, but that probably is less
efficient, so I probably would recommend against it. It is easiest to do as
I suggested:

WHERE P1.First_Name Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P2.First_Name Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P1.Surname
Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR
P2.Surname Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*"


WHERE C1.CoupleID Is Null AND C2.CoupleID Is Null AND (P1.First_Name Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P1.Surname
Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*");

More efficient I would think because the query engine does not have to
concatenate the fields before testing the resulting name and if any one of
the or-ed conditions is true it does not have to check further.

Clifford Bass
 
H

Hugh self taught

Hi Cliffard,

Okay. Pity though. I was hoping to list the lookup person as one column &
their partner (if they have one) also in one column, then add some other
columns of relative data.

Thanks for the help. I'd be lost without you guys.

Clifford Bass said:
Hi Hugh,

You could do the combination of the fields, but that probably is less
efficient, so I probably would recommend against it. It is easiest to do as
I suggested:

WHERE P1.First_Name Like "*" &
[Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P2.First_Name Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P1.Surname
Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR
P2.Surname Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*"


WHERE C1.CoupleID Is Null AND C2.CoupleID Is Null AND (P1.First_Name Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P1.Surname
Like
"*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*");

More efficient I would think because the query engine does not have to
concatenate the fields before testing the resulting name and if any one of
the or-ed conditions is true it does not have to check further.

Clifford Bass

Hugh self taught said:
Thanks guys,

What if I join First_Name & " " & Surname AS [Competitor Name] then I only
need to check against one field? I can't get past P1.[Competitor Name] in the
syntax though. I get an error referring to the SELECT reserved word or
misspelled argument. Clearly I'm not as far down the road understanding SQL
as I thought I was.

How do I change the query to combine First_Name & Surname?
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Hugh,

I would not rule it out. You can of course concatenate names in the
select portion if you wish without impacting performance in any significant
way. You now have something that is functional. Make a copy and play
around. For purposes of experimentation, you could each part in its own
query so you can revert to the use of the query designer.

Glad to help, and good luck!

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL JOIN for Unmatched records & JOIN fields from another table 4

Top