I've seen posts here and elsewhere which read something along the lines of
"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind of
subject line isn't descriptive, but I sure can relate right now. I've been
struggling for days--days!!-- on this one simple query. I really need to
get past this thing and move on. Please help.

I have a classic ASP page, and it gives you 4 dropdowns. You can select any
or none of them. Each of them is built by an ACCESS table. These 4 tables
are union tables; they have just 2 fields: the PK from the master table,
called Personal, and one of the 4 static tables that I call People, Area,
Ability, Gift.

Anyway, when someone selects a value from one or more of the dropdowns, the
next page grabs the values and puts them into variables (strGiftID,
strAbilityID, strAreaID, and strPeopleID). This is where I am running into
problems. If you see my previous threads on this, you'll see lots of
suggestions, and I have tried every one of them.

Here's my current code:

strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
Personal P "
strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
PG.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID "

If strGiftID <> "" Then
strWhere = "GiftID = "&strGiftID&" AND "
End if
If strAreaID <> "" Then
strWhere = strWhere & "AreaID = "&strAreaID&" AND "
End if
If strAbilityID <> "" Then
strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
End if
If strPeopleID <> "" Then
strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
End if

If Len(strWhere) > 0 Then
' Remove the last AND
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = strSQL & "WHERE " & strWhere
End If
----------------------------------------------

Here's the error:

'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID'.
/grace/list.asp, line 64

(where line 64 is the line which executes the SQL statement)


Please, please let me know what you see that I am doing wrong.

TIA

Have you tried putting single quotes around your where values?

I can't remember ... do you need to put the table aliases before the
column names in the where?

Have you tried this sql statement directly against the database?

Have you tried making a stored query in Access?





On Thu, 8 Jan 2004 00:03:36 -0600, "middletree"
<middletree@htomail.com> wrote:
[color=blue]
>I've seen posts here and elsewhere which read something along the lines of
>"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind of
>subject line isn't descriptive, but I sure can relate right now. I've been
>struggling for days--days!!-- on this one simple query. I really need to
>get past this thing and move on. Please help.
>
>I have a classic ASP page, and it gives you 4 dropdowns. You can select any
>or none of them. Each of them is built by an ACCESS table. These 4 tables
>are union tables; they have just 2 fields: the PK from the master table,
>called Personal, and one of the 4 static tables that I call People, Area,
>Ability, Gift.
>
>Anyway, when someone selects a value from one or more of the dropdowns, the
>next page grabs the values and puts them into variables (strGiftID,
>strAbilityID, strAreaID, and strPeopleID). This is where I am running into
>problems. If you see my previous threads on this, you'll see lots of
>suggestions, and I have tried every one of them.
>
>Here's my current code:
>
>strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
>Personal P "
>strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
>PG.PersonalID "
>strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalID "
>strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.PersonalID "
>strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
>PP.PersonalID "
>
> If strGiftID <> "" Then
> strWhere = "GiftID = "&strGiftID&" AND "
> End if
> If strAreaID <> "" Then
> strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> End if
> If strAbilityID <> "" Then
> strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> End if
> If strPeopleID <> "" Then
> strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> End if
>
> If Len(strWhere) > 0 Then
> ' Remove the last AND
> strWhere = Left(strWhere, Len(strWhere) - 5)
> strSQL = strSQL & "WHERE " & strWhere
> End If
>----------------------------------------------
>
>Here's the error:
>
>'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
>PP.PersonalID'.
>/grace/list.asp, line 64
>
>(where line 64 is the line which executes the SQL statement)
>
>
>Please, please let me know what you see that I am doing wrong.
>
>TIA
>
>
>
>
>
>
>
>[/color]

As always, the first step in debugging this type of problem is to
response.write your variable values (including the variable that contains
the SQL statement.). Here is how I would start debugging your code. Give it
a try and if it does not highlight your problem, post back with the results
of these response.writes.

strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
Personal P "
strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
PG.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID "

Response.Write "strSQL contains: " & strSQL & "<BR>"
Response.Write "strGiftID contains: " & strGift & "<BR>"
If strGiftID <> "" Then
strWhere = "GiftID = "&strGiftID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"
Response.Write "strAreaID contains: " & strAreaID & "<BR>"
If strAreaID <> "" Then
strWhere = strWhere & "AreaID = "&strAreaID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"
Response.Write "strAbilityID contains: " & strAbilityID & "<BR>"
If strAbilityID <> "" Then
strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"
Response.Write "strPeopleID contains: " & strPeopleID & "<BR>"
If strPeopleID <> "" Then
strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"

If Len(strWhere) > 0 Then
' Remove the last AND
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = strSQL & "WHERE " & strWhere
End If

Response.Write "strSQL now contains: " & strSQL & "<BR>"

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From header is
my spam trap, so I don't check it very often. You will get a quicker
response by posting to the newsgroup.

Comments inline:

"Brynn" <z@z.com> wrote in message
news:3ffd0c73.22569042@news.comcast.giganews.com.. .[color=blue]
>
> Have you tried putting single quotes around your where values?[/color]

Yes, but it didn't work, because these are integers. They have to be,
because they are the PK in their respective tables, where they are
autonumbers.
[color=blue]
>
> I can't remember ... do you need to put the table aliases before the
> column names in the where?
>[/color]

Not sure what you mean

[color=blue]
> Have you tried this sql statement directly against the database?[/color]

Of course. Got the same error. Of course, I can't do the If part in the
database, so that kind of limits what I could test there.
[color=blue]
>
> Have you tried making a stored query in Access?
>
>[/color]

Not sure how, and not sure what difference it could make. What do you have
in mind?


[color=blue]
>
>
>
> On Thu, 8 Jan 2004 00:03:36 -0600, "middletree"
> <middletree@htomail.com> wrote:
>[color=green]
> >I've seen posts here and elsewhere which read something along the lines[/color][/color]
of[color=blue][color=green]
> >"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind[/color][/color]
of[color=blue][color=green]
> >subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
been[color=blue][color=green]
> >struggling for days--days!!-- on this one simple query. I really need to
> >get past this thing and move on. Please help.
> >
> >I have a classic ASP page, and it gives you 4 dropdowns. You can select[/color][/color]
any[color=blue][color=green]
> >or none of them. Each of them is built by an ACCESS table. These 4 tables
> >are union tables; they have just 2 fields: the PK from the master table,
> >called Personal, and one of the 4 static tables that I call People, Area,
> >Ability, Gift.
> >
> >Anyway, when someone selects a value from one or more of the dropdowns,[/color][/color]
the[color=blue][color=green]
> >next page grabs the values and puts them into variables (strGiftID,
> >strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
into[color=blue][color=green]
> >problems. If you see my previous threads on this, you'll see lots of
> >suggestions, and I have tried every one of them.
> >
> >Here's my current code:
> >
> >strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> >Personal P "
> >strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> >PG.PersonalID "
> >strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> >PA.PersonalID "
> >strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.PersonalID "
> >strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> >PP.PersonalID "
> >
> > If strGiftID <> "" Then
> > strWhere = "GiftID = "&strGiftID&" AND "
> > End if
> > If strAreaID <> "" Then
> > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > End if
> > If strAbilityID <> "" Then
> > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > End if
> > If strPeopleID <> "" Then
> > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > End if
> >
> > If Len(strWhere) > 0 Then
> > ' Remove the last AND
> > strWhere = Left(strWhere, Len(strWhere) - 5)
> > strSQL = strSQL & "WHERE " & strWhere
> > End If
> >----------------------------------------------
> >
> >Here's the error:
> >
> >'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID[/color][/color]
=[color=blue][color=green]
> >PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> >PP.PersonalID'.
> >/grace/list.asp, line 64
> >
> >(where line 64 is the line which executes the SQL statement)
> >
> >
> >Please, please let me know what you see that I am doing wrong.
> >
> >TIA
> >
> >
> >
> >
> >
> >
> >
> >[/color]
>[/color]

Of course, I did try lots of response.writes. I guess I should have
mentioned that in my post, but if I would have mentioned everything I did,
the post would have been extremely long.

Let me see what I can do on this. Thanks.


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uoKdMre1DHA.2572@TK2MSFTNGP12.phx.gbl...[color=blue]
> As always, the first step in debugging this type of problem is to
> response.write your variable values (including the variable that contains
> the SQL statement.). Here is how I would start debugging your code. Give[/color]
it[color=blue]
> a try and if it does not highlight your problem, post back with the[/color]
results[color=blue]
> of these response.writes.
>
> strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> Personal P "
> strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> PG.PersonalID "
> strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> PA.PersonalID "
> strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> PAB.PersonalID "
> strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> PP.PersonalID "
>
> Response.Write "strSQL contains: " & strSQL & "<BR>"
> Response.Write "strGiftID contains: " & strGift & "<BR>"
> If strGiftID <> "" Then
> strWhere = "GiftID = "&strGiftID&" AND "
> End if
> Response.Write "strWhere contains: " & strWhere & "<BR>"
> Response.Write "strAreaID contains: " & strAreaID & "<BR>"
> If strAreaID <> "" Then
> strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> End if
> Response.Write "strWhere contains: " & strWhere & "<BR>"
> Response.Write "strAbilityID contains: " & strAbilityID & "<BR>"
> If strAbilityID <> "" Then
> strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> End if
> Response.Write "strWhere contains: " & strWhere & "<BR>"
> Response.Write "strPeopleID contains: " & strPeopleID & "<BR>"
> If strPeopleID <> "" Then
> strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> End if
> Response.Write "strWhere contains: " & strWhere & "<BR>"
>
> If Len(strWhere) > 0 Then
> ' Remove the last AND
> strWhere = Left(strWhere, Len(strWhere) - 5)
> strSQL = strSQL & "WHERE " & strWhere
> End If
>
> Response.Write "strSQL now contains: " & strSQL & "<BR>"
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From header[/color]
is[color=blue]
> my spam trap, so I don't check it very often. You will get a quicker
> response by posting to the newsgroup.
>
>[/color]

middletree wrote:[color=blue]
> Of course, I did try lots of response.writes. I guess I should have
> mentioned that in my post, but if I would have mentioned everything I
> did, the post would have been extremely long.
>[/color]
I realize that. :-)

The thing is: we cannot help without seeing the results of the
response.writes. They are almost always the key to solving the problem.

The other key to solving these problems is telling us the data types of the
columns in your query. That lets us know where delimiters are required in
your sql statement.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Since you prefixed your variable names with "str" - strAreaID - it gives the
impression that these are strings. If you are going to use this type of
notation, it should reflect the type of variable.

Bob Lehmann

"middletree" <middletree@htomail.com> wrote in message
news:OMoi%23Ff1DHA.716@TK2MSFTNGP12.phx.gbl...[color=blue]
> Comments inline:
>
> "Brynn" <z@z.com> wrote in message
> news:3ffd0c73.22569042@news.comcast.giganews.com.. .[color=green]
> >
> > Have you tried putting single quotes around your where values?[/color]
>
> Yes, but it didn't work, because these are integers. They have to be,
> because they are the PK in their respective tables, where they are
> autonumbers.
>[color=green]
> >
> > I can't remember ... do you need to put the table aliases before the
> > column names in the where?
> >[/color]
>
> Not sure what you mean
>
>[color=green]
> > Have you tried this sql statement directly against the database?[/color]
>
> Of course. Got the same error. Of course, I can't do the If part in the
> database, so that kind of limits what I could test there.
>[color=green]
> >
> > Have you tried making a stored query in Access?
> >
> >[/color]
>
> Not sure how, and not sure what difference it could make. What do you have
> in mind?
>
>
>[color=green]
> >
> >
> >
> > On Thu, 8 Jan 2004 00:03:36 -0600, "middletree"
> > <middletree@htomail.com> wrote:
> >[color=darkred]
> > >I've seen posts here and elsewhere which read something along the lines[/color][/color]
> of[color=green][color=darkred]
> > >"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind[/color][/color]
> of[color=green][color=darkred]
> > >subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
> been[color=green][color=darkred]
> > >struggling for days--days!!-- on this one simple query. I really need[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > >get past this thing and move on. Please help.
> > >
> > >I have a classic ASP page, and it gives you 4 dropdowns. You can select[/color][/color]
> any[color=green][color=darkred]
> > >or none of them. Each of them is built by an ACCESS table. These 4[/color][/color][/color]
tables[color=blue][color=green][color=darkred]
> > >are union tables; they have just 2 fields: the PK from the master[/color][/color][/color]
table,[color=blue][color=green][color=darkred]
> > >called Personal, and one of the 4 static tables that I call People,[/color][/color][/color]
Area,[color=blue][color=green][color=darkred]
> > >Ability, Gift.
> > >
> > >Anyway, when someone selects a value from one or more of the dropdowns,[/color][/color]
> the[color=green][color=darkred]
> > >next page grabs the values and puts them into variables (strGiftID,
> > >strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
> into[color=green][color=darkred]
> > >problems. If you see my previous threads on this, you'll see lots of
> > >suggestions, and I have tried every one of them.
> > >
> > >Here's my current code:
> > >
> > >strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> > >Personal P "
> > >strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> > >PG.PersonalID "
> > >strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> > >PA.PersonalID "
> > >strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > >PAB.PersonalID "
> > >strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> > >PP.PersonalID "
> > >
> > > If strGiftID <> "" Then
> > > strWhere = "GiftID = "&strGiftID&" AND "
> > > End if
> > > If strAreaID <> "" Then
> > > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > > End if
> > > If strAbilityID <> "" Then
> > > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > > End if
> > > If strPeopleID <> "" Then
> > > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > > End if
> > >
> > > If Len(strWhere) > 0 Then
> > > ' Remove the last AND
> > > strWhere = Left(strWhere, Len(strWhere) - 5)
> > > strSQL = strSQL & "WHERE " & strWhere
> > > End If
> > >----------------------------------------------
> > >
> > >Here's the error:
> > >
> > >'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON[/color][/color][/color]
P.PersonalID[color=blue]
> =[color=green][color=darkred]
> > >PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > >PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> > >PP.PersonalID'.
> > >/grace/list.asp, line 64
> > >
> > >(where line 64 is the line which executes the SQL statement)
> > >
> > >
> > >Please, please let me know what you see that I am doing wrong.
> > >
> > >TIA
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >[/color]
> >[/color]
>
>[/color]

Yeah, I guess since it's ASP, everything is a variant, so I tend to treat
almost everything like it's a string. You are correct that it implies the
wrong thing


"Bob Lehmann" <nospam@dontbotherme.zzz> wrote in message
news:e7Pah$f1DHA.4060@TK2MSFTNGP11.phx.gbl...[color=blue]
> Since you prefixed your variable names with "str" - strAreaID - it gives[/color]
the[color=blue]
> impression that these are strings. If you are going to use this type of
> notation, it should reflect the type of variable.
>
> Bob Lehmann[/color]

I have a feeling all of those inner joins aren't nesting the way you want
them to.

I tried this query in Access and it returned a record if there was a
matching "join" in all of the "union" tables. That is, I created a Personal
item and an item for each of the four other tables, then a join in each of
the other 4 tables. If any one of the join tables did not contain my
Personal then it didn't show up. I assume that's what you want.

SELECT Personal.FName, Personal.LName, Personal.PreferredName,
Personal.PersonalID
FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
PersonalGift.PersonalID) INNER JOIN PersonalAbility ON Personal.PersonalID =
PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON Personal.PersonalID
= PersonalPeople.PersonalID) INNER JOIN PersonalArea ON Personal.PersonalID
= PersonalArea.PersonalID

You'll notice I took out the aliases (PersonalAbility PAB) I'm not sure if
they were causing the problem (they shouldn't have been) but I figured I'd
get them out of the way to be sure.

So I think the problem is with the parentheses. Adding the where clause
didn't cause a problem.


"middletree" <middletree@htomail.com> wrote in message
news:utggLva1DHA.2388@TK2MSFTNGP09.phx.gbl...[color=blue]
> I've seen posts here and elsewhere which read something along the lines of
> "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind of
> subject line isn't descriptive, but I sure can relate right now. I've been
> struggling for days--days!!-- on this one simple query. I really need to
> get past this thing and move on. Please help.
>
> I have a classic ASP page, and it gives you 4 dropdowns. You can select[/color]
any[color=blue]
> or none of them. Each of them is built by an ACCESS table. These 4 tables
> are union tables; they have just 2 fields: the PK from the master table,
> called Personal, and one of the 4 static tables that I call People, Area,
> Ability, Gift.
>
> Anyway, when someone selects a value from one or more of the dropdowns,[/color]
the[color=blue]
> next page grabs the values and puts them into variables (strGiftID,
> strAbilityID, strAreaID, and strPeopleID). This is where I am running into
> problems. If you see my previous threads on this, you'll see lots of
> suggestions, and I have tried every one of them.
>
> Here's my current code:
>
> strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> Personal P "
> strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> PG.PersonalID "
> strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> PA.PersonalID "
> strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> PAB.PersonalID "
> strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> PP.PersonalID "
>
> If strGiftID <> "" Then
> strWhere = "GiftID = "&strGiftID&" AND "
> End if
> If strAreaID <> "" Then
> strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> End if
> If strAbilityID <> "" Then
> strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> End if
> If strPeopleID <> "" Then
> strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> End if
>
> If Len(strWhere) > 0 Then
> ' Remove the last AND
> strWhere = Left(strWhere, Len(strWhere) - 5)
> strSQL = strSQL & "WHERE " & strWhere
> End If
> ----------------------------------------------
>
> Here's the error:
>
> 'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
> PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> PP.PersonalID'.
> /grace/list.asp, line 64
>
> (where line 64 is the line which executes the SQL statement)
>
>
> Please, please let me know what you see that I am doing wrong.
>
> TIA
>
>
>
>
>
>
>
>[/color]

Ok, I will try it out. If I'm reading it right, I don't need to have my IF
statements. We'll see.

thanks



"TomB" <shuckle@hotmailXXX.com> wrote in message
news:um1DrRg1DHA.1704@tk2msftngp13.phx.gbl...[color=blue]
> I have a feeling all of those inner joins aren't nesting the way you want
> them to.
>
> I tried this query in Access and it returned a record if there was a
> matching "join" in all of the "union" tables. That is, I created a[/color]
Personal[color=blue]
> item and an item for each of the four other tables, then a join in each of
> the other 4 tables. If any one of the join tables did not contain my
> Personal then it didn't show up. I assume that's what you want.
>
> SELECT Personal.FName, Personal.LName, Personal.PreferredName,
> Personal.PersonalID
> FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> PersonalGift.PersonalID) INNER JOIN PersonalAbility ON Personal.PersonalID[/color]
=[color=blue]
> PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON[/color]
Personal.PersonalID[color=blue]
> = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON[/color]
Personal.PersonalID[color=blue]
> = PersonalArea.PersonalID
>
> You'll notice I took out the aliases (PersonalAbility PAB) I'm not sure if
> they were causing the problem (they shouldn't have been) but I figured I'd
> get them out of the way to be sure.
>
> So I think the problem is with the parentheses. Adding the where clause
> didn't cause a problem.
>
>
> "middletree" <middletree@htomail.com> wrote in message
> news:utggLva1DHA.2388@TK2MSFTNGP09.phx.gbl...[color=green]
> > I've seen posts here and elsewhere which read something along the lines[/color][/color]
of[color=blue][color=green]
> > "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind[/color][/color]
of[color=blue][color=green]
> > subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
been[color=blue][color=green]
> > struggling for days--days!!-- on this one simple query. I really need[/color][/color]
to[color=blue][color=green]
> > get past this thing and move on. Please help.
> >
> > I have a classic ASP page, and it gives you 4 dropdowns. You can select[/color]
> any[color=green]
> > or none of them. Each of them is built by an ACCESS table. These 4[/color][/color]
tables[color=blue][color=green]
> > are union tables; they have just 2 fields: the PK from the master table,
> > called Personal, and one of the 4 static tables that I call People,[/color][/color]
Area,[color=blue][color=green]
> > Ability, Gift.
> >
> > Anyway, when someone selects a value from one or more of the dropdowns,[/color]
> the[color=green]
> > next page grabs the values and puts them into variables (strGiftID,
> > strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
into[color=blue][color=green]
> > problems. If you see my previous threads on this, you'll see lots of
> > suggestions, and I have tried every one of them.
> >
> > Here's my current code:
> >
> > strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> > Personal P "
> > strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> > PG.PersonalID "
> > strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> > PA.PersonalID "
> > strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > PAB.PersonalID "
> > strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> > PP.PersonalID "
> >
> > If strGiftID <> "" Then
> > strWhere = "GiftID = "&strGiftID&" AND "
> > End if
> > If strAreaID <> "" Then
> > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > End if
> > If strAbilityID <> "" Then
> > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > End if
> > If strPeopleID <> "" Then
> > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > End if
> >
> > If Len(strWhere) > 0 Then
> > ' Remove the last AND
> > strWhere = Left(strWhere, Len(strWhere) - 5)
> > strSQL = strSQL & "WHERE " & strWhere
> > End If
> > ----------------------------------------------
> >
> > Here's the error:
> >
> > 'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID[/color][/color]
=[color=blue][color=green]
> > PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> > PP.PersonalID'.
> > /grace/list.asp, line 64
> >
> > (where line 64 is the line which executes the SQL statement)
> >
> >
> > Please, please let me know what you see that I am doing wrong.
> >
> > TIA
> >
> >
> >
> >
> >
> >
> >
> >[/color]
>
>[/color]

TomB wrote:[color=blue]
> I have a feeling all of those inner joins aren't nesting the way you
> want them to.
>[/color]
Of course you are right! Joins involving more than two tables need to be
nested by parentheses in JetSQL. I can't believe I missed that![color=blue]
>
> You'll notice I took out the aliases (PersonalAbility PAB) I'm not
> sure if they were causing the problem (they shouldn't have been) but
> I figured I'd get them out of the way to be sure.
>[/color]
No need. Table aliases help, not hinder.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Well, there is good news and bad news. I tried it out (you can, too, just go
to www.middletree.net/ then add to the URL: /list.asp
(Sorry to break it up like that, but don't want it ending up in Google
Groups)

Anyway, the good news is, it doesn't throw me an error. Bad news is, it
shows 19 rows, every time, no matter what I selected. Thing is, there are
only 6 rows in the Personal table!



"TomB" <shuckle@hotmailXXX.com> wrote in message
news:um1DrRg1DHA.1704@tk2msftngp13.phx.gbl...[color=blue]
> I have a feeling all of those inner joins aren't nesting the way you want
> them to.
>
> I tried this query in Access and it returned a record if there was a
> matching "join" in all of the "union" tables. That is, I created a[/color]
Personal[color=blue]
> item and an item for each of the four other tables, then a join in each of
> the other 4 tables. If any one of the join tables did not contain my
> Personal then it didn't show up. I assume that's what you want.
>
> SELECT Personal.FName, Personal.LName, Personal.PreferredName,
> Personal.PersonalID
> FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> PersonalGift.PersonalID) INNER JOIN PersonalAbility ON Personal.PersonalID[/color]
=[color=blue]
> PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON[/color]
Personal.PersonalID[color=blue]
> = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON[/color]
Personal.PersonalID[color=blue]
> = PersonalArea.PersonalID
>
> You'll notice I took out the aliases (PersonalAbility PAB) I'm not sure if
> they were causing the problem (they shouldn't have been) but I figured I'd
> get them out of the way to be sure.
>
> So I think the problem is with the parentheses. Adding the where clause
> didn't cause a problem.
>
>
> "middletree" <middletree@htomail.com> wrote in message
> news:utggLva1DHA.2388@TK2MSFTNGP09.phx.gbl...[color=green]
> > I've seen posts here and elsewhere which read something along the lines[/color][/color]
of[color=blue][color=green]
> > "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind[/color][/color]
of[color=blue][color=green]
> > subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
been[color=blue][color=green]
> > struggling for days--days!!-- on this one simple query. I really need[/color][/color]
to[color=blue][color=green]
> > get past this thing and move on. Please help.
> >
> > I have a classic ASP page, and it gives you 4 dropdowns. You can select[/color]
> any[color=green]
> > or none of them. Each of them is built by an ACCESS table. These 4[/color][/color]
tables[color=blue][color=green]
> > are union tables; they have just 2 fields: the PK from the master table,
> > called Personal, and one of the 4 static tables that I call People,[/color][/color]
Area,[color=blue][color=green]
> > Ability, Gift.
> >
> > Anyway, when someone selects a value from one or more of the dropdowns,[/color]
> the[color=green]
> > next page grabs the values and puts them into variables (strGiftID,
> > strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
into[color=blue][color=green]
> > problems. If you see my previous threads on this, you'll see lots of
> > suggestions, and I have tried every one of them.
> >
> > Here's my current code:
> >
> > strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> > Personal P "
> > strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> > PG.PersonalID "
> > strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> > PA.PersonalID "
> > strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > PAB.PersonalID "
> > strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> > PP.PersonalID "
> >
> > If strGiftID <> "" Then
> > strWhere = "GiftID = "&strGiftID&" AND "
> > End if
> > If strAreaID <> "" Then
> > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > End if
> > If strAbilityID <> "" Then
> > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > End if
> > If strPeopleID <> "" Then
> > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > End if
> >
> > If Len(strWhere) > 0 Then
> > ' Remove the last AND
> > strWhere = Left(strWhere, Len(strWhere) - 5)
> > strSQL = strSQL & "WHERE " & strWhere
> > End If
> > ----------------------------------------------
> >
> > Here's the error:
> >
> > 'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID[/color][/color]
=[color=blue][color=green]
> > PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> > PP.PersonalID'.
> > /grace/list.asp, line 64
> >
> > (where line 64 is the line which executes the SQL statement)
> >
> >
> > Please, please let me know what you see that I am doing wrong.
> >
> > TIA
> >
> >
> >
> >
> >
> >
> >
> >[/color]
>
>[/color]

I would not normally email someone from the groups, but I have sent
you an email to your hotmail account listed.

If you no longer want me to email you, just tell me.


On Thu, 8 Jan 2004 00:03:36 -0600, "middletree"
<middletree@htomail.com> wrote:
[color=blue]
>I've seen posts here and elsewhere which read something along the lines of
>"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind of
>subject line isn't descriptive, but I sure can relate right now. I've been
>struggling for days--days!!-- on this one simple query. I really need to
>get past this thing and move on. Please help.
>
>I have a classic ASP page, and it gives you 4 dropdowns. You can select any
>or none of them. Each of them is built by an ACCESS table. These 4 tables
>are union tables; they have just 2 fields: the PK from the master table,
>called Personal, and one of the 4 static tables that I call People, Area,
>Ability, Gift.
>
>Anyway, when someone selects a value from one or more of the dropdowns, the
>next page grabs the values and puts them into variables (strGiftID,
>strAbilityID, strAreaID, and strPeopleID). This is where I am running into
>problems. If you see my previous threads on this, you'll see lots of
>suggestions, and I have tried every one of them.
>
>Here's my current code:
>
>strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
>Personal P "
>strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
>PG.PersonalID "
>strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalID "
>strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.PersonalID "
>strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
>PP.PersonalID "
>
> If strGiftID <> "" Then
> strWhere = "GiftID = "&strGiftID&" AND "
> End if
> If strAreaID <> "" Then
> strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> End if
> If strAbilityID <> "" Then
> strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> End if
> If strPeopleID <> "" Then
> strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> End if
>
> If Len(strWhere) > 0 Then
> ' Remove the last AND
> strWhere = Left(strWhere, Len(strWhere) - 5)
> strSQL = strSQL & "WHERE " & strWhere
> End If
>----------------------------------------------
>
>Here's the error:
>
>'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
>PP.PersonalID'.
>/grace/list.asp, line 64
>
>(where line 64 is the line which executes the SQL statement)
>
>
>Please, please let me know what you see that I am doing wrong.
>
>TIA
>
>
>
>
>
>
>
>[/color]

I have no problem with that. I'll take help where I can get it. As long as
you're not trying to sell me products to make body parts larger.

thanks


"Brynn" <z@z.com> wrote in message
news:3ffd9bfa.8908770@news.comcast.giganews.com...[color=blue]
>
> I would not normally email someone from the groups, but I have sent
> you an email to your hotmail account listed.
>
> If you no longer want me to email you, just tell me.
>
>
> On Thu, 8 Jan 2004 00:03:36 -0600, "middletree"
> <middletree@htomail.com> wrote:
>[color=green]
> >I've seen posts here and elsewhere which read something along the lines[/color][/color]
of[color=blue][color=green]
> >"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind[/color][/color]
of[color=blue][color=green]
> >subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
been[color=blue][color=green]
> >struggling for days--days!!-- on this one simple query. I really need to
> >get past this thing and move on. Please help.
> >
> >I have a classic ASP page, and it gives you 4 dropdowns. You can select[/color][/color]
any[color=blue][color=green]
> >or none of them. Each of them is built by an ACCESS table. These 4 tables
> >are union tables; they have just 2 fields: the PK from the master table,
> >called Personal, and one of the 4 static tables that I call People, Area,
> >Ability, Gift.
> >
> >Anyway, when someone selects a value from one or more of the dropdowns,[/color][/color]
the[color=blue][color=green]
> >next page grabs the values and puts them into variables (strGiftID,
> >strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
into[color=blue][color=green]
> >problems. If you see my previous threads on this, you'll see lots of
> >suggestions, and I have tried every one of them.
> >
> >Here's my current code:
> >
> >strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> >Personal P "
> >strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> >PG.PersonalID "
> >strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> >PA.PersonalID "
> >strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.PersonalID "
> >strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> >PP.PersonalID "
> >
> > If strGiftID <> "" Then
> > strWhere = "GiftID = "&strGiftID&" AND "
> > End if
> > If strAreaID <> "" Then
> > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > End if
> > If strAbilityID <> "" Then
> > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > End if
> > If strPeopleID <> "" Then
> > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > End if
> >
> > If Len(strWhere) > 0 Then
> > ' Remove the last AND
> > strWhere = Left(strWhere, Len(strWhere) - 5)
> > strSQL = strSQL & "WHERE " & strWhere
> > End If
> >----------------------------------------------
> >
> >Here's the error:
> >
> >'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID[/color][/color]
=[color=blue][color=green]
> >PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> >PP.PersonalID'.
> >/grace/list.asp, line 64
> >
> >(where line 64 is the line which executes the SQL statement)
> >
> >
> >Please, please let me know what you see that I am doing wrong.
> >
> >TIA
> >
> >
> >
> >
> >
> >
> >
> >[/color]
>[/color]

middletree wrote:[color=blue]
> Well, there is good news and bad news. I tried it out (you can, too,
> just go to www.middletree.net/ then add to the URL: /list.asp
> (Sorry to break it up like that, but don't want it ending up in Google
> Groups)
>
> Anyway, the good news is, it doesn't throw me an error. Bad news is,
> it shows 19 rows, every time, no matter what I selected. Thing is,
> there are only 6 rows in the Personal table!
>
>[/color]
You must have multiple records for each person in at least one of the tables
involved in the join.

This is where you need to provide us with your table structures (relevant
fields only), sample data, intended results from that data, along with the
results your query is currently returning.It really helps us if you provide
the sample data for each table in the form of insert statements (insert
(<columns>) values (<data>) which we can copy, paste and run to insert the
data. The intended results should be provided in tabular format if possible.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Can you explain what it is you want returned?
I'm looking at list.asp right now, and I see a whole bunch of Joe Dirt's at
the bottom are your 4 combo boxes.

Are you hoping to limit the number of Joe Dirt's returned to those
personal's that match the contents of your combos?

In my database, I've only got 1 Personal and 1 for each of the 4 tables,
plus an entry for each combination in the join table. This returns one row.

However, if I add a second ability and add it to the join table as well.
Then I get two rows returned. I then add a second Area and the join, now I
have 4 rows returned. All of which makes sense.

You will still want your IF statements to limit the rows returned (see
example below).

Try throwing in a DISTINCT after SELECT ....

SELECT DISTINCT Personal.FName, Personal.LName, Personal.PreferredName,
Personal.PersonalID
FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
PersonalGift.PersonalID) INNER JOIN PersonalAbility ON Personal.PersonalID =
PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON Personal.PersonalID
= PersonalPeople.PersonalID) INNER JOIN PersonalArea ON Personal.PersonalID
= PersonalArea.PersonalID WHERE PersonalPeople=1;


"middletree" <middletree@htomail.com> wrote in message
news:u3mrcjg1DHA.2060@TK2MSFTNGP10.phx.gbl...[color=blue]
> Well, there is good news and bad news. I tried it out (you can, too, just[/color]
go[color=blue]
> to www.middletree.net/ then add to the URL: /list.asp
> (Sorry to break it up like that, but don't want it ending up in Google
> Groups)
>
> Anyway, the good news is, it doesn't throw me an error. Bad news is, it
> shows 19 rows, every time, no matter what I selected. Thing is, there are
> only 6 rows in the Personal table!
>
>
>
> "TomB" <shuckle@hotmailXXX.com> wrote in message
> news:um1DrRg1DHA.1704@tk2msftngp13.phx.gbl...[color=green]
> > I have a feeling all of those inner joins aren't nesting the way you[/color][/color]
want[color=blue][color=green]
> > them to.
> >
> > I tried this query in Access and it returned a record if there was a
> > matching "join" in all of the "union" tables. That is, I created a[/color]
> Personal[color=green]
> > item and an item for each of the four other tables, then a join in each[/color][/color]
of[color=blue][color=green]
> > the other 4 tables. If any one of the join tables did not contain my
> > Personal then it didn't show up. I assume that's what you want.
> >
> > SELECT Personal.FName, Personal.LName, Personal.PreferredName,
> > Personal.PersonalID
> > FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> > PersonalGift.PersonalID) INNER JOIN PersonalAbility ON[/color][/color]
Personal.PersonalID[color=blue]
> =[color=green]
> > PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON[/color]
> Personal.PersonalID[color=green]
> > = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON[/color]
> Personal.PersonalID[color=green]
> > = PersonalArea.PersonalID
> >
> > You'll notice I took out the aliases (PersonalAbility PAB) I'm not sure[/color][/color]
if[color=blue][color=green]
> > they were causing the problem (they shouldn't have been) but I figured[/color][/color]
I'd[color=blue][color=green]
> > get them out of the way to be sure.
> >
> > So I think the problem is with the parentheses. Adding the where clause
> > didn't cause a problem.
> >
> >
> > "middletree" <middletree@htomail.com> wrote in message
> > news:utggLva1DHA.2388@TK2MSFTNGP09.phx.gbl...[color=darkred]
> > > I've seen posts here and elsewhere which read something along the[/color][/color][/color]
lines[color=blue]
> of[color=green][color=darkred]
> > > "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that[/color][/color][/color]
kind[color=blue]
> of[color=green][color=darkred]
> > > subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
> been[color=green][color=darkred]
> > > struggling for days--days!!-- on this one simple query. I really need[/color][/color]
> to[color=green][color=darkred]
> > > get past this thing and move on. Please help.
> > >
> > > I have a classic ASP page, and it gives you 4 dropdowns. You can[/color][/color][/color]
select[color=blue][color=green]
> > any[color=darkred]
> > > or none of them. Each of them is built by an ACCESS table. These 4[/color][/color]
> tables[color=green][color=darkred]
> > > are union tables; they have just 2 fields: the PK from the master[/color][/color][/color]
table,[color=blue][color=green][color=darkred]
> > > called Personal, and one of the 4 static tables that I call People,[/color][/color]
> Area,[color=green][color=darkred]
> > > Ability, Gift.
> > >
> > > Anyway, when someone selects a value from one or more of the[/color][/color][/color]
dropdowns,[color=blue][color=green]
> > the[color=darkred]
> > > next page grabs the values and puts them into variables (strGiftID,
> > > strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
> into[color=green][color=darkred]
> > > problems. If you see my previous threads on this, you'll see lots of
> > > suggestions, and I have tried every one of them.
> > >
> > > Here's my current code:
> > >
> > > strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
> > > Personal P "
> > > strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> > > PG.PersonalID "
> > > strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> > > PA.PersonalID "
> > > strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > > PAB.PersonalID "
> > > strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> > > PP.PersonalID "
> > >
> > > If strGiftID <> "" Then
> > > strWhere = "GiftID = "&strGiftID&" AND "
> > > End if
> > > If strAreaID <> "" Then
> > > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > > End if
> > > If strAbilityID <> "" Then
> > > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > > End if
> > > If strPeopleID <> "" Then
> > > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > > End if
> > >
> > > If Len(strWhere) > 0 Then
> > > ' Remove the last AND
> > > strWhere = Left(strWhere, Len(strWhere) - 5)
> > > strSQL = strSQL & "WHERE " & strWhere
> > > End If
> > > ----------------------------------------------
> > >
> > > Here's the error:
> > >
> > > 'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON[/color][/color][/color]
P.PersonalID[color=blue]
> =[color=green][color=darkred]
> > > PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > > PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> > > PP.PersonalID'.
> > > /grace/list.asp, line 64
> > >
> > > (where line 64 is the line which executes the SQL statement)
> > >
> > >
> > > Please, please let me know what you see that I am doing wrong.
> > >
> > > TIA
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

For most of your questions, I just answered them in my post to Bob, right
above yours.

I added DISTINCT to see what happens, but it now shows 3 users, regardless
of what was selected.



"TomB" <shuckle@hotmailXXX.com> wrote in message
news:#osPSkh1DHA.3496@TK2MSFTNGP11.phx.gbl...[color=blue]
> Can you explain what it is you want returned?
> I'm looking at list.asp right now, and I see a whole bunch of Joe Dirt's[/color]
at[color=blue]
> the bottom are your 4 combo boxes.
>
> Are you hoping to limit the number of Joe Dirt's returned to those
> personal's that match the contents of your combos?
>
> In my database, I've only got 1 Personal and 1 for each of the 4 tables,
> plus an entry for each combination in the join table. This returns one[/color]
row.[color=blue]
>
> However, if I add a second ability and add it to the join table as well.
> Then I get two rows returned. I then add a second Area and the join, now[/color]
I[color=blue]
> have 4 rows returned. All of which makes sense.
>
> You will still want your IF statements to limit the rows returned (see
> example below).
>
> Try throwing in a DISTINCT after SELECT ....
>
> SELECT DISTINCT Personal.FName, Personal.LName, Personal.PreferredName,
> Personal.PersonalID
> FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> PersonalGift.PersonalID) INNER JOIN PersonalAbility ON Personal.PersonalID[/color]
=[color=blue]
> PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON[/color]
Personal.PersonalID[color=blue]
> = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON[/color]
Personal.PersonalID[color=blue]
> = PersonalArea.PersonalID WHERE PersonalPeople=1;
>
>
> "middletree" <middletree@htomail.com> wrote in message
> news:u3mrcjg1DHA.2060@TK2MSFTNGP10.phx.gbl...[color=green]
> > Well, there is good news and bad news. I tried it out (you can, too,[/color][/color]
just[color=blue]
> go[color=green]
> > to www.middletree.net/ then add to the URL: /list.asp
> > (Sorry to break it up like that, but don't want it ending up in Google
> > Groups)
> >
> > Anyway, the good news is, it doesn't throw me an error. Bad news is, it
> > shows 19 rows, every time, no matter what I selected. Thing is, there[/color][/color]
are[color=blue][color=green]
> > only 6 rows in the Personal table!
> >
> >
> >
> > "TomB" <shuckle@hotmailXXX.com> wrote in message
> > news:um1DrRg1DHA.1704@tk2msftngp13.phx.gbl...[color=darkred]
> > > I have a feeling all of those inner joins aren't nesting the way you[/color][/color]
> want[color=green][color=darkred]
> > > them to.
> > >
> > > I tried this query in Access and it returned a record if there was a
> > > matching "join" in all of the "union" tables. That is, I created a[/color]
> > Personal[color=darkred]
> > > item and an item for each of the four other tables, then a join in[/color][/color][/color]
each[color=blue]
> of[color=green][color=darkred]
> > > the other 4 tables. If any one of the join tables did not contain my
> > > Personal then it didn't show up. I assume that's what you want.
> > >
> > > SELECT Personal.FName, Personal.LName, Personal.PreferredName,
> > > Personal.PersonalID
> > > FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> > > PersonalGift.PersonalID) INNER JOIN PersonalAbility ON[/color][/color]
> Personal.PersonalID[color=green]
> > =[color=darkred]
> > > PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON[/color]
> > Personal.PersonalID[color=darkred]
> > > = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON[/color]
> > Personal.PersonalID[color=darkred]
> > > = PersonalArea.PersonalID
> > >
> > > You'll notice I took out the aliases (PersonalAbility PAB) I'm not[/color][/color][/color]
sure[color=blue]
> if[color=green][color=darkred]
> > > they were causing the problem (they shouldn't have been) but I figured[/color][/color]
> I'd[color=green][color=darkred]
> > > get them out of the way to be sure.
> > >
> > > So I think the problem is with the parentheses. Adding the where[/color][/color][/color]
clause[color=blue][color=green][color=darkred]
> > > didn't cause a problem.
> > >
> > >
> > > "middletree" <middletree@htomail.com> wrote in message
> > > news:utggLva1DHA.2388@TK2MSFTNGP09.phx.gbl...
> > > > I've seen posts here and elsewhere which read something along the[/color][/color]
> lines[color=green]
> > of[color=darkred]
> > > > "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that[/color][/color]
> kind[color=green]
> > of[color=darkred]
> > > > subject line isn't descriptive, but I sure can relate right now.[/color][/color][/color]
I've[color=blue][color=green]
> > been[color=darkred]
> > > > struggling for days--days!!-- on this one simple query. I really[/color][/color][/color]
need[color=blue][color=green]
> > to[color=darkred]
> > > > get past this thing and move on. Please help.
> > > >
> > > > I have a classic ASP page, and it gives you 4 dropdowns. You can[/color][/color]
> select[color=green][color=darkred]
> > > any
> > > > or none of them. Each of them is built by an ACCESS table. These 4[/color]
> > tables[color=darkred]
> > > > are union tables; they have just 2 fields: the PK from the master[/color][/color]
> table,[color=green][color=darkred]
> > > > called Personal, and one of the 4 static tables that I call People,[/color]
> > Area,[color=darkred]
> > > > Ability, Gift.
> > > >
> > > > Anyway, when someone selects a value from one or more of the[/color][/color]
> dropdowns,[color=green][color=darkred]
> > > the
> > > > next page grabs the values and puts them into variables (strGiftID,
> > > > strAbilityID, strAreaID, and strPeopleID). This is where I am[/color][/color][/color]
running[color=blue][color=green]
> > into[color=darkred]
> > > > problems. If you see my previous threads on this, you'll see lots of
> > > > suggestions, and I have tried every one of them.
> > > >
> > > > Here's my current code:
> > > >
> > > > strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID[/color][/color][/color]
FROM[color=blue][color=green][color=darkred]
> > > > Personal P "
> > > > strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> > > > PG.PersonalID "
> > > > strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> > > > PA.PersonalID "
> > > > strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > > > PAB.PersonalID "
> > > > strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> > > > PP.PersonalID "
> > > >
> > > > If strGiftID <> "" Then
> > > > strWhere = "GiftID = "&strGiftID&" AND "
> > > > End if
> > > > If strAreaID <> "" Then
> > > > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > > > End if
> > > > If strAbilityID <> "" Then
> > > > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > > > End if
> > > > If strPeopleID <> "" Then
> > > > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > > > End if
> > > >
> > > > If Len(strWhere) > 0 Then
> > > > ' Remove the last AND
> > > > strWhere = Left(strWhere, Len(strWhere) - 5)
> > > > strSQL = strSQL & "WHERE " & strWhere
> > > > End If
> > > > ----------------------------------------------
> > > >
> > > > Here's the error:
> > > >
> > > > 'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON[/color][/color]
> P.PersonalID[color=green]
> > =[color=darkred]
> > > > PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > > > PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> > > > PP.PersonalID'.
> > > > /grace/list.asp, line 64
> > > >
> > > > (where line 64 is the line which executes the SQL statement)
> > > >
> > > >
> > > > Please, please let me know what you see that I am doing wrong.
> > > >
> > > > TIA
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

Yes, but if you run that in Access, you'll see that your three records are
all DISTINCT.
1. Joe Dirt PersonalID=215
2. Joe Dirt PersonalID=217
3. joe Doe PersonalID=218

"middletree" <middletree@htomail.com> wrote in message
news:uC4Rkph1DHA.208@TK2MSFTNGP12.phx.gbl...[color=blue]
> For most of your questions, I just answered them in my post to Bob, right
> above yours.
>
> I added DISTINCT to see what happens, but it now shows 3 users, regardless
> of what was selected.
>
>
>
> "TomB" <shuckle@hotmailXXX.com> wrote in message
> news:#osPSkh1DHA.3496@TK2MSFTNGP11.phx.gbl...[color=green]
> > Can you explain what it is you want returned?
> > I'm looking at list.asp right now, and I see a whole bunch of Joe Dirt's[/color]
> at[color=green]
> > the bottom are your 4 combo boxes.
> >
> > Are you hoping to limit the number of Joe Dirt's returned to those
> > personal's that match the contents of your combos?
> >
> > In my database, I've only got 1 Personal and 1 for each of the 4 tables,
> > plus an entry for each combination in the join table. This returns one[/color]
> row.[color=green]
> >
> > However, if I add a second ability and add it to the join table as well.
> > Then I get two rows returned. I then add a second Area and the join,[/color][/color]
now[color=blue]
> I[color=green]
> > have 4 rows returned. All of which makes sense.
> >
> > You will still want your IF statements to limit the rows returned (see
> > example below).
> >
> > Try throwing in a DISTINCT after SELECT ....
> >
> > SELECT DISTINCT Personal.FName, Personal.LName, Personal.PreferredName,
> > Personal.PersonalID
> > FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> > PersonalGift.PersonalID) INNER JOIN PersonalAbility ON[/color][/color]
Personal.PersonalID[color=blue]
> =[color=green]
> > PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON[/color]
> Personal.PersonalID[color=green]
> > = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON[/color]
> Personal.PersonalID[color=green]
> > = PersonalArea.PersonalID WHERE PersonalPeople=1;
> >
> >
> > "middletree" <middletree@htomail.com> wrote in message
> > news:u3mrcjg1DHA.2060@TK2MSFTNGP10.phx.gbl...[color=darkred]
> > > Well, there is good news and bad news. I tried it out (you can, too,[/color][/color]
> just[color=green]
> > go[color=darkred]
> > > to www.middletree.net/ then add to the URL: /list.asp
> > > (Sorry to break it up like that, but don't want it ending up in Google
> > > Groups)
> > >
> > > Anyway, the good news is, it doesn't throw me an error. Bad news is,[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > shows 19 rows, every time, no matter what I selected. Thing is, there[/color][/color]
> are[color=green][color=darkred]
> > > only 6 rows in the Personal table!
> > >
> > >
> > >
> > > "TomB" <shuckle@hotmailXXX.com> wrote in message
> > > news:um1DrRg1DHA.1704@tk2msftngp13.phx.gbl...
> > > > I have a feeling all of those inner joins aren't nesting the way you[/color]
> > want[color=darkred]
> > > > them to.
> > > >
> > > > I tried this query in Access and it returned a record if there was a
> > > > matching "join" in all of the "union" tables. That is, I created a
> > > Personal
> > > > item and an item for each of the four other tables, then a join in[/color][/color]
> each[color=green]
> > of[color=darkred]
> > > > the other 4 tables. If any one of the join tables did not contain[/color][/color][/color]
my[color=blue][color=green][color=darkred]
> > > > Personal then it didn't show up. I assume that's what you want.
> > > >
> > > > SELECT Personal.FName, Personal.LName, Personal.PreferredName,
> > > > Personal.PersonalID
> > > > FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> > > > PersonalGift.PersonalID) INNER JOIN PersonalAbility ON[/color]
> > Personal.PersonalID[color=darkred]
> > > =
> > > > PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON
> > > Personal.PersonalID
> > > > = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON
> > > Personal.PersonalID
> > > > = PersonalArea.PersonalID
> > > >
> > > > You'll notice I took out the aliases (PersonalAbility PAB) I'm not[/color][/color]
> sure[color=green]
> > if[color=darkred]
> > > > they were causing the problem (they shouldn't have been) but I[/color][/color][/color]
figured[color=blue][color=green]
> > I'd[color=darkred]
> > > > get them out of the way to be sure.
> > > >
> > > > So I think the problem is with the parentheses. Adding the where[/color][/color]
> clause[color=green][color=darkred]
> > > > didn't cause a problem.
> > > >
> > > >
> > > > "middletree" <middletree@htomail.com> wrote in message
> > > > news:utggLva1DHA.2388@TK2MSFTNGP09.phx.gbl...
> > > > > I've seen posts here and elsewhere which read something along the[/color]
> > lines[color=darkred]
> > > of
> > > > > "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that[/color]
> > kind[color=darkred]
> > > of
> > > > > subject line isn't descriptive, but I sure can relate right now.[/color][/color]
> I've[color=green][color=darkred]
> > > been
> > > > > struggling for days--days!!-- on this one simple query. I really[/color][/color]
> need[color=green][color=darkred]
> > > to
> > > > > get past this thing and move on. Please help.
> > > > >
> > > > > I have a classic ASP page, and it gives you 4 dropdowns. You can[/color]
> > select[color=darkred]
> > > > any
> > > > > or none of them. Each of them is built by an ACCESS table. These 4
> > > tables
> > > > > are union tables; they have just 2 fields: the PK from the master[/color]
> > table,[color=darkred]
> > > > > called Personal, and one of the 4 static tables that I call[/color][/color][/color]
People,[color=blue][color=green][color=darkred]
> > > Area,
> > > > > Ability, Gift.
> > > > >
> > > > > Anyway, when someone selects a value from one or more of the[/color]
> > dropdowns,[color=darkred]
> > > > the
> > > > > next page grabs the values and puts them into variables[/color][/color][/color]
(strGiftID,[color=blue][color=green][color=darkred]
> > > > > strAbilityID, strAreaID, and strPeopleID). This is where I am[/color][/color]
> running[color=green][color=darkred]
> > > into
> > > > > problems. If you see my previous threads on this, you'll see lots[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> > > > > suggestions, and I have tried every one of them.
> > > > >
> > > > > Here's my current code:
> > > > >
> > > > > strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID[/color][/color]
> FROM[color=green][color=darkred]
> > > > > Personal P "
> > > > > strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
> > > > > PG.PersonalID "
> > > > > strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> > > > > PA.PersonalID "
> > > > > strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID[/color][/color][/color]
=[color=blue][color=green][color=darkred]
> > > > > PAB.PersonalID "
> > > > > strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> > > > > PP.PersonalID "
> > > > >
> > > > > If strGiftID <> "" Then
> > > > > strWhere = "GiftID = "&strGiftID&" AND "
> > > > > End if
> > > > > If strAreaID <> "" Then
> > > > > strWhere = strWhere & "AreaID = "&strAreaID&" AND "
> > > > > End if
> > > > > If strAbilityID <> "" Then
> > > > > strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
> > > > > End if
> > > > > If strPeopleID <> "" Then
> > > > > strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
> > > > > End if
> > > > >
> > > > > If Len(strWhere) > 0 Then
> > > > > ' Remove the last AND
> > > > > strWhere = Left(strWhere, Len(strWhere) - 5)
> > > > > strSQL = strSQL & "WHERE " & strWhere
> > > > > End If
> > > > > ----------------------------------------------
> > > > >
> > > > > Here's the error:
> > > > >
> > > > > 'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON[/color]
> > P.PersonalID[color=darkred]
> > > =
> > > > > PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > > > > PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
> > > > > PP.PersonalID'.
> > > > > /grace/list.asp, line 64
> > > > >
> > > > > (where line 64 is the line which executes the SQL statement)
> > > > >
> > > > >
> > > > > Please, please let me know what you see that I am doing wrong.
> > > > >
> > > > > TIA
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

Yes, I understand that. That definitely solves part of the problem. I'm
glad you caught that.


"TomB" <shuckle@hotmailXXX.com> wrote in message
news:#LE0zuh1DHA.1508@TK2MSFTNGP12.phx.gbl...[color=blue]
> Yes, but if you run that in Access, you'll see that your three records are
> all DISTINCT.
> 1. Joe Dirt PersonalID=215
> 2. Joe Dirt PersonalID=217
> 3. joe Doe PersonalID=218
>
> "middletree" <middletree@htomail.com> wrote in message
> news:uC4Rkph1DHA.208@TK2MSFTNGP12.phx.gbl...[color=green]
> > For most of your questions, I just answered them in my post to Bob,[/color][/color]
right[color=blue][color=green]
> > above yours.
> >
> > I added DISTINCT to see what happens, but it now shows 3 users,[/color][/color]
regardless[color=blue][color=green]
> > of what was selected.
> >
> >
> >
> > "TomB" <shuckle@hotmailXXX.com> wrote in message
> > news:#osPSkh1DHA.3496@TK2MSFTNGP11.phx.gbl...[color=darkred]
> > > Can you explain what it is you want returned?
> > > I'm looking at list.asp right now, and I see a whole bunch of Joe[/color][/color][/color]
Dirt's[color=blue][color=green]
> > at[color=darkred]
> > > the bottom are your 4 combo boxes.
> > >
> > > Are you hoping to limit the number of Joe Dirt's returned to those
> > > personal's that match the contents of your combos?
> > >
> > > In my database, I've only got 1 Personal and 1 for each of the 4[/color][/color][/color]
tables,[color=blue][color=green][color=darkred]
> > > plus an entry for each combination in the join table. This returns[/color][/color][/color]
one[color=blue][color=green]
> > row.[color=darkred]
> > >
> > > However, if I add a second ability and add it to the join table as[/color][/color][/color]
well.[color=blue][color=green][color=darkred]
> > > Then I get two rows returned. I then add a second Area and the join,[/color][/color]
> now[color=green]
> > I[color=darkred]
> > > have 4 rows returned. All of which makes sense.
> > >
> > > You will still want your IF statements to limit the rows returned (see
> > > example below).
> > >
> > > Try throwing in a DISTINCT after SELECT ....
> > >
> > > SELECT DISTINCT Personal.FName, Personal.LName,[/color][/color][/color]
Personal.PreferredName,[color=blue][color=green][color=darkred]
> > > Personal.PersonalID
> > > FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
> > > PersonalGift.PersonalID) INNER JOIN PersonalAbility ON[/color][/color]
> Personal.PersonalID[color=green]
> > =[color=darkred]
> > > PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON[/color]
> > Personal.PersonalID[color=darkred]
> > > = PersonalPeople.PersonalID) INNER JOIN PersonalArea ON[/color]
> > Personal.PersonalID[color=darkred]
> > > = PersonalArea.PersonalID WHERE PersonalPeople=1;
> > >