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
23  1782 
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"
<mi********@htomail.com> wrote: 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
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:3f***************@news.comcast.giganews.com.. .
Have you tried putting single quotes around your where values?
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.
I can't remember ... do you need to put the table aliases before the
column names in the where?
Not sure what you mean
Have you tried this sql statement directly against the database?
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.
Have you tried making a stored query in Access?
Not sure how, and not sure what difference it could make. What do you have
in mind?
On Thu, 8 Jan 2004 00:03:36 -0600, "middletree"
<mi********@htomail.com> wrote:
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
ofsubject line isn't descriptive, but I sure can relate right now. I've
beenstruggling 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
anyor 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,
thenext page grabs the values and puts them into variables (strGiftID,
strAbilityID, strAreaID, and strPeopleID). This is where I am running
intoproblems. 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
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" <re******@NOyahoo.SPAMcom> wrote in message
news:uo**************@TK2MSFTNGP12.phx.gbl... 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.
middletree wrote: 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.
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" <mi********@htomail.com> wrote in message
news:OM***************@TK2MSFTNGP12.phx.gbl... Comments inline:
"Brynn" <z@z.com> wrote in message
news:3f***************@news.comcast.giganews.com.. .
Have you tried putting single quotes around your where values?
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.
I can't remember ... do you need to put the table aliases before the
column names in the where?
Not sure what you mean
Have you tried this sql statement directly against the database?
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.
Have you tried making a stored query in Access?
Not sure how, and not sure what difference it could make. What do you have
in mind?
On Thu, 8 Jan 2004 00:03:36 -0600, "middletree"
<mi********@htomail.com> wrote:
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 ofsubject line isn't descriptive, but I sure can relate right now. I've beenstruggling for days--days!!-- on this one simple query. I really need
toget past this thing and move on. Please help.
I have a classic ASP page, and it gives you 4 dropdowns. You can select anyor none of them. Each of them is built by an ACCESS table. These 4
tablesare 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, thenext page grabs the values and puts them into variables (strGiftID,
strAbilityID, strAreaID, and strPeopleID). This is where I am running intoproblems. 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
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" <no****@dontbotherme.zzz> wrote in message
news:e7**************@TK2MSFTNGP11.phx.gbl... 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
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" <mi********@htomail.com> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl... 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
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" <sh*****@hotmailXXX.com> wrote in message
news:um**************@tk2msftngp13.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl... 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
TomB wrote: I have a feeling all of those inner joins aren't nesting the way you
want them to.
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!
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.
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" <sh*****@hotmailXXX.com> wrote in message
news:um**************@tk2msftngp13.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl... 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
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"
<mi********@htomail.com> wrote: 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
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:3f**************@news.comcast.giganews.com...
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"
<mi********@htomail.com> wrote:
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
ofsubject line isn't descriptive, but I sure can relate right now. I've
beenstruggling 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
anyor 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,
thenext page grabs the values and puts them into variables (strGiftID,
strAbilityID, strAreaID, and strPeopleID). This is where I am running
intoproblems. 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
middletree wrote: 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!
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" <mi********@htomail.com> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl... 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" <sh*****@hotmailXXX.com> wrote in message
news:um**************@tk2msftngp13.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl... 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
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" <sh*****@hotmailXXX.com> wrote in message
news:#o**************@TK2MSFTNGP11.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl... 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" <sh*****@hotmailXXX.com> wrote in message
news:um**************@tk2msftngp13.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:ut**************@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
>
>
>
>
>
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" <mi********@htomail.com> wrote in message
news:uC*************@TK2MSFTNGP12.phx.gbl... 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" <sh*****@hotmailXXX.com> wrote in message
news:#o**************@TK2MSFTNGP11.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl... 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" <sh*****@hotmailXXX.com> wrote in message
news:um**************@tk2msftngp13.phx.gbl...
> 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" <mi********@htomail.com> wrote in message
> news:ut**************@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
>
>
Yes, I understand that. That definitely solves part of the problem. I'm
glad you caught that.
"TomB" <sh*****@hotmailXXX.com> wrote in message
news:#L**************@TK2MSFTNGP12.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:uC*************@TK2MSFTNGP12.phx.gbl... 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" <sh*****@hotmailXXX.com> wrote in message
news:#o**************@TK2MSFTNGP11.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:u3**************@TK2MSFTNGP10.phx.gbl...
> 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" <sh*****@hotmailXXX.com> wrote in message
> news:um**************@tk2msftngp13.phx.gbl...
> > 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" <mi********@htomail.com> wrote in message
> > news:ut**************@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
Now you need to put your IFs back in there to limit the results.
"middletree" <mi********@htomail.com> wrote in message
news:uq**************@tk2msftngp13.phx.gbl... Yes, I understand that. That definitely solves part of the problem. I'm
glad you caught that.
"TomB" <sh*****@hotmailXXX.com> wrote in message
news:#L**************@TK2MSFTNGP12.phx.gbl... 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" <mi********@htomail.com> wrote in message
news:uC*************@TK2MSFTNGP12.phx.gbl... 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" <sh*****@hotmailXXX.com> wrote in message
news:#o**************@TK2MSFTNGP11.phx.gbl...
> 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" <mi********@htomail.com> wrote in message
> news:u3**************@TK2MSFTNGP10.phx.gbl...
> > 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" <sh*****@hotmailXXX.com> wrote in message
> > news:um**************@tk2msftngp13.phx.gbl...
> > > 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" <mi********@htomail.com> wrote in message
> > > news:ut**************@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Where? Around the INNER JOINs? I don't see how that is possible, as the
parentheses will be uneven, depending on what was selected.
(watch the wrap)
strSQL = "SELECT DISTINCT Personal.FName, Personal.LName,
Personal.PreferredName, Personal.PersonalID "
strSQL = strSQL & "FROM (((Personal "
If strGiftID <> "" then
strSQL = strSQL &"INNER JOIN PersonalGift ON Personal.PersonalID
=PersonalGift.PersonalID) "
End if
If strAbilityID <> "" then
strSQL = strSQL & "INNER JOIN PersonalAbility ON Personal.PersonalID
=PersonalAbility.PersonalID) "
etc. See what I mean?
"TomB" <sh*****@hotmailXXX.com> wrote in message
news:e2**************@tk2msftngp13.phx.gbl... Now you need to put your IFs back in there to limit the results.
No, sorry. The way you originally had it.....
if len(strGiftID)>0 then
sWhere="PersonalGift.GiftID=" & strGiftID & " AND "
end if
etc.
strSQL=strSQL & sWhere
"middletree" <mi********@htomail.com> wrote in message
news:Oc**************@TK2MSFTNGP09.phx.gbl... Where? Around the INNER JOINs? I don't see how that is possible, as the
parentheses will be uneven, depending on what was selected.
(watch the wrap)
strSQL = "SELECT DISTINCT Personal.FName, Personal.LName,
Personal.PreferredName, Personal.PersonalID "
strSQL = strSQL & "FROM (((Personal "
If strGiftID <> "" then
strSQL = strSQL &"INNER JOIN PersonalGift ON Personal.PersonalID
=PersonalGift.PersonalID) "
End if
If strAbilityID <> "" then
strSQL = strSQL & "INNER JOIN PersonalAbility ON Personal.PersonalID
=PersonalAbility.PersonalID) "
etc. See what I mean?
"TomB" <sh*****@hotmailXXX.com> wrote in message
news:e2**************@tk2msftngp13.phx.gbl... Now you need to put your IFs back in there to limit the results.
Oh, gosh. Dont' even know if I have that code; it's been changed so many
times.
"TomB" <sh*****@hotmailXXX.com> wrote in message
news:Og**************@TK2MSFTNGP10.phx.gbl... No, sorry. The way you originally had it.....
if len(strGiftID)>0 then
sWhere="PersonalGift.GiftID=" & strGiftID & " AND "
end if
etc.
strSQL=strSQL & sWhere
"middletree" <mi********@htomail.com> wrote in message
news:Oc**************@TK2MSFTNGP09.phx.gbl... Where? Around the INNER JOINs? I don't see how that is possible, as the
parentheses will be uneven, depending on what was selected.
(watch the wrap)
strSQL = "SELECT DISTINCT Personal.FName, Personal.LName,
Personal.PreferredName, Personal.PersonalID "
strSQL = strSQL & "FROM (((Personal "
If strGiftID <> "" then
strSQL = strSQL &"INNER JOIN PersonalGift ON Personal.PersonalID
=PersonalGift.PersonalID) "
End if
If strAbilityID <> "" then
strSQL = strSQL & "INNER JOIN PersonalAbility ON Personal.PersonalID
=PersonalAbility.PersonalID) "
etc. See what I mean?
"TomB" <sh*****@hotmailXXX.com> wrote in message
news:e2**************@tk2msftngp13.phx.gbl... Now you need to put your IFs back in there to limit the results.
Well, the problem got fixed. I have a firend, a former co-worker, and he
added some code fairly quickly. In anyone cares, here it is:
strSQL = "SELECT DISTINCT Personal.FName, Personal.LName,
Personal.PreferredName, Personal.PersonalID "
strSQL = strSQL & "FROM (((Personal LEFT OUTER JOIN PersonalGift ON " strSQL
= strSQL & "Personal.PersonalID =PersonalGift.PersonalID) "
strSQL = strSQL & "LEFT OUTER JOIN PersonalAbility ON Personal.PersonalID
=PersonalAbility.PersonalID) "
strSQL = strSQL & "LEFT OUTER JOIN PersonalPeople ON Personal.PersonalID=
PersonalPeople.PersonalID) "
strSQL = strSQL & "LEFT OUTER JOIN PersonalArea ON Personal.PersonalID=
PersonalArea.PersonalID"
If Len(strGiftID) Or Len(strAreaID) > 0 Or Len(strPeopleID) > 0 Or
Len(strAbility) > 0 Then
strSQL = strSQL & " Where Personal.FName = Personal.FName "
If Len(strGiftID) > 0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalGift where GiftID = " & strGiftID & ")"
If Len(strAreaID) > 0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalArea where AreaID = " & strAreaID & ")"
If Len(strPeopleID) > 0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalPeople where PeopleID = " & strPeopleID & ")" If Len(strAbilityID) >
0 Then
strSQL = strSQL & " And Personal.PersonalID In (select PersonalID from
PersonalAbility where AbilityID = " & strAbilityID & ")"
Thanks, everybody, for your time!
This discussion thread is closed Replies have been disabled for this discussion. |
