SQL syntax--getting demoralized

**Brynn** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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@hto mail.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.PersonalI D "
>strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalI D "
>strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.Personal ID "
>strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
>PP.PersonalI D "
>
> 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 = "&strPeople ID&" 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.PersonalI D = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalI D INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.Personal ID 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]

**Bob Barrows** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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 & " "
Response.Write "strGiftID contains: " & strGift & " "
If strGiftID <> "" Then
strWhere = "GiftID = "&strGiftID &" AND "
End if
Response.Write "strWhere contains: " & strWhere & " "
Response.Write "strAreaID contains: " & strAreaID & " "
If strAreaID <> "" Then
strWhere = strWhere & "AreaID = "&strAreaID &" AND "
End if
Response.Write "strWhere contains: " & strWhere & " "
Response.Write "strAbility ID contains: " & strAbilityID & " "
If strAbilityID <> "" Then
strWhere = strWhere & "AbilityID = "&strAbilityID& " AND "
End if
Response.Write "strWhere contains: " & strWhere & " "
Response.Write "strPeopleI D contains: " & strPeopleID & " "
If strPeopleID <> "" Then
strWhere = strWhere & "PeopleID = "&strPeople ID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & " "

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 & " "

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.

**middletree** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

Comments inline:

"Brynn" <z@z.com> wrote in message
news:3ffd0c73.2 2569042@news.co mcast.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@hto mail.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.PersonalI D "
> >strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> >PA.PersonalI D "
> >strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.Personal ID "
> >strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> >PP.PersonalI D "
> >
> > 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 = "&strPeople ID&" 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.PersonalI D = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID[/color][/color]
=[color=blue][color=green]
> >PA.PersonalI D INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.Personal ID 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** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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@NOyah oo.SPAMcom> wrote in message
news:uoKdMre1DH A.2572@TK2MSFTN GP12.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 & " "
> Response.Write "strGiftID contains: " & strGift & " "
> If strGiftID <> "" Then
> strWhere = "GiftID = "&strGiftID &" AND "
> End if
> Response.Write "strWhere contains: " & strWhere & " "
> Response.Write "strAreaID contains: " & strAreaID & " "
> If strAreaID <> "" Then
> strWhere = strWhere & "AreaID = "&strAreaID &" AND "
> End if
> Response.Write "strWhere contains: " & strWhere & " "
> Response.Write "strAbility ID contains: " & strAbilityID & " "
> If strAbilityID <> "" Then
> strWhere = strWhere & "AbilityID = "&strAbilityID& " AND "
> End if
> Response.Write "strWhere contains: " & strWhere & " "
> Response.Write "strPeopleI D contains: " & strPeopleID & " "
> If strPeopleID <> "" Then
> strWhere = strWhere & "PeopleID = "&strPeople ID&" AND "
> End if
> Response.Write "strWhere contains: " & strWhere & " "
>
> 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 & " "
>
> 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]

**Bob Barrows** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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.

**Bob Lehmann** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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@hto mail.com> wrote in message
news:OMoi%23Ff1 DHA.716@TK2MSFT NGP12.phx.gbl.. .[color=blue]
> Comments inline:
>
> "Brynn" <z@z.com> wrote in message
> news:3ffd0c73.2 2569042@news.co mcast.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@hto mail.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.PersonalI D "
> > >strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> > >PA.PersonalI D "
> > >strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > >PAB.Personal ID "
> > >strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> > >PP.PersonalI D "
> > >
> > > 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 = "&strPeople ID&" 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.PersonalI D = PG.PersonalID INNER JOIN PersonalArea PA ON[/color][/color][/color]
P.PersonalID[color=blue]
> =[color=green][color=darkred]
> > >PA.PersonalI D INNER JOIN PersonalAbility PAB ON P.PersonalID =
> > >PAB.Personal ID 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]

**middletree** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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@dontbot herme.zzz> wrote in message
news:e7Pah$f1DH A.4060@TK2MSFTN GP11.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]

**TomB** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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.Prefer redName,
Personal.Person alID
FROM (((Personal INNER JOIN PersonalGift ON Personal.Person alID =
PersonalGift.Pe rsonalID) INNER JOIN PersonalAbility ON Personal.Person alID =
PersonalAbility .PersonalID) INNER JOIN PersonalPeople ON Personal.Person alID
= PersonalPeople. PersonalID) INNER JOIN PersonalArea ON Personal.Person alID
= PersonalArea.Pe rsonalID

You'll notice I took out the aliases (PersonalAbilit y 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@hto mail.com> wrote in message
news:utggLva1DH A.2388@TK2MSFTN GP09.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 = "&strPeople ID&" 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]

**middletree** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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@hotmai lXXX.com> wrote in message
news:um1DrRg1DH A.1704@tk2msftn gp13.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.Prefer redName,
> Personal.Person alID
> FROM (((Personal INNER JOIN PersonalGift ON Personal.Person alID =
> PersonalGift.Pe rsonalID) INNER JOIN PersonalAbility ON Personal.Person alID[/color]
=[color=blue]
> PersonalAbility .PersonalID) INNER JOIN PersonalPeople ON[/color]
Personal.Person alID[color=blue]
> = PersonalPeople. PersonalID) INNER JOIN PersonalArea ON[/color]
Personal.Person alID[color=blue]
> = PersonalArea.Pe rsonalID
>
> You'll notice I took out the aliases (PersonalAbilit y 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@hto mail.com> wrote in message
> news:utggLva1DH A.2388@TK2MSFTN GP09.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 = "&strPeople ID&" 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]

**Bob Barrows** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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 (PersonalAbilit y 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.

**middletree** · Jul 19 '05, 09:58 AM

Re: SQL syntax--getting demoralized

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@hotmai lXXX.com> wrote in message
news:um1DrRg1DH A.1704@tk2msftn gp13.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.Prefer redName,
> Personal.Person alID
> FROM (((Personal INNER JOIN PersonalGift ON Personal.Person alID =
> PersonalGift.Pe rsonalID) INNER JOIN PersonalAbility ON Personal.Person alID[/color]
=[color=blue]
> PersonalAbility .PersonalID) INNER JOIN PersonalPeople ON[/color]
Personal.Person alID[color=blue]
> = PersonalPeople. PersonalID) INNER JOIN PersonalArea ON[/color]
Personal.Person alID[color=blue]
> = PersonalArea.Pe rsonalID
>
> You'll notice I took out the aliases (PersonalAbilit y 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@hto mail.com> wrote in message
> news:utggLva1DH A.2388@TK2MSFTN GP09.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 = "&strPeople ID&" 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]

**Brynn** · Jul 19 '05, 09:59 AM

Re: SQL syntax--getting demoralized

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@hto mail.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.PersonalI D "
>strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalI D "
>strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.Personal ID "
>strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
>PP.PersonalI D "
>
> 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 = "&strPeople ID&" 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.PersonalI D = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
>PA.PersonalI D INNER JOIN PersonalAbility PAB ON P.PersonalID =
>PAB.Personal ID 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]

**middletree** · Jul 19 '05, 09:59 AM

Re: SQL syntax--getting demoralized

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.8 908770@news.com cast.giganews.c om...[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@hto mail.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.PersonalI D "
> >strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
> >PA.PersonalI D "
> >strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.Personal ID "
> >strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
> >PP.PersonalI D "
> >
> > 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 = "&strPeople ID&" 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.PersonalI D = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID[/color][/color]
=[color=blue][color=green]
> >PA.PersonalI D INNER JOIN PersonalAbility PAB ON P.PersonalID =
> >PAB.Personal ID 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]

**Bob Barrows** · Jul 19 '05, 09:59 AM

Re: SQL syntax--getting demoralized

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.