|
 |
asp_databases thread: Altering Query/Recordset for LEFT JOIN
Message #1 by "Bruce Monroe" <bmonroe@s...> on Wed, 15 Jan 2003 20:33:34
|
|
I am creating an online report using an Access db, ASP and IIS. I'm
having some difficulty creating a left join. The database has 2 tables,
one for ratings and one for results. I've provided a simplified version
below.
tblRating (tables/qry) qryResults
text number (fields) FacNum FacName Rating
Excellent 3 1 Joe 1
Average 2 1 Joe 2
Poor 1 1 Joe 2
2 Diane 3
2 Diane 2
2 Diane 3
I loop through all the faculty members and pass the facnum to a sub where
I left join the tables on tblRating.number=qryResults.Rating where
Facname="1" to create the recordset. I then print out the table and
move.next for the next member.
There are actually 50 faculty members rated in each of 8 fields.
I'm trying to create a table in an asp page that gives the counts
for the responses for each faculty member (and returns "0" if none).
Faculty Excellent Average Poor
Diane 2 1 0
Joe 0 2 1
etc.
My problem is with the LEFT JOIN. When I use a left join and then select
for a specific faculty member I won't get the "0" counts -i.e. the left
join has already joined "Poor" on Joe so Diane won't show a "0" for Poor,
Joe won't have 0 for excellent (and it shouldn't the way the JOIN is
written).
I had a couple of ideas for solutions (which I don't know how to
implement):
1.I think I need to find a way to pass the criteria to the query so that
it persists and then run the join.
2. Is there a way to create a recordset against another recordset object?
3. Is there a way to nest select statetments to do this?
Or am I entirely off the beam?
Any help would be appreciated
Thanks
Message #2 by Colin.Montgomery@C... on Thu, 16 Jan 2003 12:00:03 -0000
|
|
i'm afraid I didn't really follow your description of the problem, but from
the 3 points/questions you make at the bottom, i'd say you need to look into
Hierarchical recordsets. I think it was 4guysfromrolla.com who had an
article that showed me how a couple of years ago.
The idea is that you have a regular recordset (RS), but one of the fields is
infact another RS. So you can loop through your main RS and then access the
fields in the sub RS, for each main record. (I think 4guysfromrolla explain
it a bit better...)
HTH,
Col
-----Original Message-----
From: Bruce Monroe [mailto:bmonroe@s...]
Sent: 15 January 2003 20:34
To: ASP Databases
Subject: [asp_databases] Altering Query/Recordset for LEFT JOIN
I am creating an online report using an Access db, ASP and IIS. I'm
having some difficulty creating a left join. The database has 2 tables,
one for ratings and one for results. I've provided a simplified version
below.
tblRating (tables/qry) qryResults
text number (fields) FacNum FacName Rating
Excellent 3 1 Joe 1
Average 2 1 Joe 2
Poor 1 1 Joe 2
2 Diane 3
2 Diane 2
2 Diane 3
I loop through all the faculty members and pass the facnum to a sub where
I left join the tables on tblRating.number=qryResults.Rating where
Facname="1" to create the recordset. I then print out the table and
move.next for the next member.
There are actually 50 faculty members rated in each of 8 fields.
I'm trying to create a table in an asp page that gives the counts
for the responses for each faculty member (and returns "0" if none).
Faculty Excellent Average Poor
Diane 2 1 0
Joe 0 2 1
etc.
My problem is with the LEFT JOIN. When I use a left join and then select
for a specific faculty member I won't get the "0" counts -i.e. the left
join has already joined "Poor" on Joe so Diane won't show a "0" for Poor,
Joe won't have 0 for excellent (and it shouldn't the way the JOIN is
written).
I had a couple of ideas for solutions (which I don't know how to
implement):
1.I think I need to find a way to pass the criteria to the query so that
it persists and then run the join.
2. Is there a way to create a recordset against another recordset object?
3. Is there a way to nest select statetments to do this?
Or am I entirely off the beam?
Any help would be appreciated
Thanks
*******
This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system. If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.
Message #3 by "Kim Iwan Hansen" <kimiwan@k...> on Thu, 16 Jan 2003 17:14:22 +0100
|
|
It's called "data shaping" (when you start googling)
-Kim
-----Original Message-----
From: Colin.Montgomery@C...
[mailto:Colin.Montgomery@C...]
Sent: 16. januar 2003 13:00
To: ASP Databases
Subject: [asp_databases] RE: Altering Query/Recordset for LEFT JOIN
i'm afraid I didn't really follow your description of the problem, but from
the 3 points/questions you make at the bottom, i'd say you need to look into
Hierarchical recordsets. I think it was 4guysfromrolla.com who had an
article that showed me how a couple of years ago.
The idea is that you have a regular recordset (RS), but one of the fields is
infact another RS. So you can loop through your main RS and then access the
fields in the sub RS, for each main record. (I think 4guysfromrolla explain
it a bit better...)
HTH,
Col
-----Original Message-----
From: Bruce Monroe [mailto:bmonroe@s...]
Sent: 15 January 2003 20:34
To: ASP Databases
Subject: [asp_databases] Altering Query/Recordset for LEFT JOIN
I am creating an online report using an Access db, ASP and IIS. I'm
having some difficulty creating a left join. The database has 2 tables,
one for ratings and one for results. I've provided a simplified version
below.
tblRating (tables/qry) qryResults
text number (fields) FacNum FacName Rating
Excellent 3 1 Joe 1
Average 2 1 Joe 2
Poor 1 1 Joe 2
2 Diane 3
2 Diane 2
2 Diane 3
I loop through all the faculty members and pass the facnum to a sub where
I left join the tables on tblRating.number=qryResults.Rating where
Facname="1" to create the recordset. I then print out the table and
move.next for the next member.
There are actually 50 faculty members rated in each of 8 fields.
I'm trying to create a table in an asp page that gives the counts
for the responses for each faculty member (and returns "0" if none).
Faculty Excellent Average Poor
Diane 2 1 0
Joe 0 2 1
etc.
My problem is with the LEFT JOIN. When I use a left join and then select
for a specific faculty member I won't get the "0" counts -i.e. the left
join has already joined "Poor" on Joe so Diane won't show a "0" for Poor,
Joe won't have 0 for excellent (and it shouldn't the way the JOIN is
written).
I had a couple of ideas for solutions (which I don't know how to
implement):
1.I think I need to find a way to pass the criteria to the query so that
it persists and then run the join.
2. Is there a way to create a recordset against another recordset object?
3. Is there a way to nest select statetments to do this?
Or am I entirely off the beam?
Any help would be appreciated
Thanks
*******
This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended recipient,
please telephone or email the sender and delete this message and any
attachment from your system. If you are not the intended recipient you must
not copy this message or attachment or disclose the contents to any other
person.
For further information about Clifford Chance please see our website at
http://www.cliffordchance.com or refer to any Clifford Chance office.
|
|
|
View Cart
