Ah, now you are telling us why you want to use a subquery: it's for
filtering out the GapId other than 29 before making the Left Join. The
obvious solution would be to put this right on the ON statement:
SELECT U.User_ID, IsNull([Org_Abbr], [Last_Name]) as Org,
IsNull(GV.Gap_Likelihood, 0) as Gap_Likelihood,
IsNull(GV.Gap_Impact, 0) as Gap_Impact
FROM tbl_Users U
LEFT JOIN tbl_Gap_Voting as GV on (U.User_ID = GV.UserID AND GV.GapID =
29)
Where U.IsSelected <> 0
ORDER BY U.Org
This query work perfectly on SQL-Server. However, when I try this on
Access with ODBC Linked Tables, Access core-dump (but there is no
core-dump if using regular Access tables); so I modified it in order to
directly filter the table tbl_Gap_Voting by using - guess what? - a
sub-query but now we put on where we really want it:
SELECT U.User_ID, Nz([Org_Abbr], [Last_Name]) AS Org,
Nz(GV.Gap_Likelihood, 0) AS Gap_Likelihood, Nz(GV.Gap_Impact, 0) AS
Gap_Impact
FROM dbo_tbl_Users AS U LEFT JOIN [Select * from dbo_tbl_Gap_Voting where
GapId=29]. AS GV ON U.User_ID=GV.UserId
Where U.IsSelected <> 0
WITH OWNERACCESS OPTION;
Notice that the primary key for the main table is no longer hidden in a
subquery and that there is no more #deleting. Notice also that this is
patch that we must use only in the case of ODBC linked tables and that
again, we see that using ODBC linked tables to address a Sql-server is
only, at it's best, a kludge.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Dale Fye said:
Without being rude, I strongly disagree.
My users table looks something like:
User_ID IsSelected Org Last_Name
Dale -1 1 XXX
Ted -1 2 YYY
Steve -1 3 ZZZ
George 0 4 AAA
My Gap_Votes table looks something like:
UserID GapID Gap_Likelihood Gap_Impact
Dale 29 5 5
Dale 30 6 6
Ted 29 NULL NULL
Ted 30 7 6
With this data, the dataset I would expect to get back is:
User_ID Org Gap_Likelihood Gap_Impact
Dale 1 5 5
Ted 2 NULL NULL
Steve 3 NULL NULL
You are mistaken for several reasons.
1. As you can see, my Users table does not contain a GapID, so I cannot
possibly remove the sub-query without adding WHERE clause that restricts
the return recordset to GapID = 29. But as soon as I add that WHERE
clause, it would restrict the recordset to only those records where
people in the Users table have a matching record in the Gap_Votes table.
2. Likewise, I cannot do as you suggest because to ensure that I get the
above recordset I cannot put a WHERE clause at the end of the query to
restrict it to users where IsSelected = True, for exactly the same
reason.
If you think you see a way to get the recordset I've indicated, without
the sub-query and without the LEFT JOIN, please provide it.
Dale
Sylvain Lafontaine said:
I need the sub-query because I want the result set to return ALL of the
users where IsSelected = True, and the matching (or not) from
tbl_Gap_Voting
Without beeing any rude, you seem to make a confusion between a
sub-query and a Left Join. What you are describing here is a Left Join,
not a sub-query. Remove the sub-query and keep the Left Join and your
problem will be solved.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Sylvain,
I need the sub-query because I want the result set to return ALL of the
users where IsSelected = True, and the matching (or not) from
tbl_Gap_Voting
I've created another query that only returns the matching records, for
now,
but would really like this result set to display all of the Selected
Users,
and the values from Gap_Voting, with NULLs where there is no match).
--
Dale
email address is invalid
Please reply to newsgroup only.
:
Probably because Access doesn't find a main primary key for the
resultset
because you have hidden it in a subquery. You don't need a subquery
here,
so remove it and make a regular LEFT JOIN.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
The following query:
SELECT U.User_ID, U.Org,
NZ(GV.Gap_Likelihood, 0) as Gap_Likelihood,
NZ(GV.Gap_Impact, 0) as Gap_Impact
FROM (SELECT User_ID, NZ([Org_Abbr], [Last_Name]) as Org, 29 as
GapID
FROM tbl_Users
WHERE IsSelected <> 0) as U
LEFT JOIN tbl_Gap_Voting as GV
ON U.User_ID = GV.UserID AND U.GapID = GV.GapID ORDER BY U.Org
returrns values of '#DELETED' for those records in the subquery (U)
that
do
not have matching values in tbl_Gap_Voting. Any ideas why, or how
to deal
with these results
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
