#Deleted returned by query against linked SQL tables

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.

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)


"Dale Fye" wrote in message
...
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.

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.



"Sylvain Lafontaine" wrote:

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)


"Dale Fye" wrote in message
...
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.

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)


"Dale Fye" wrote in message
...
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.



"Sylvain Lafontaine" wrote:

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)


"Dale Fye" wrote in message
...
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.

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" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
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)


"Dale Fye" wrote in message
...
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.



"Sylvain Lafontaine" wrote:

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)


"Dale Fye" wrote in message
...
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.

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" wrote in message
...
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" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
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)


"Dale Fye" wrote in message
...
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.



"Sylvain Lafontaine" wrote:

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)


"Dale Fye" wrote in message
...
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.

Sylvain,

I'll give it a try, thanks for sticking with me on this. I probably should
have included the example in my original post.

Dale

"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
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" wrote in message
...
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" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
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)


"Dale Fye" wrote in message
...
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.



"Sylvain Lafontaine" wrote:

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)


"Dale Fye" wrote in message
...
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.

"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) дÈëÏûÏ¢ ...
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)


"Dale Fye" wrote in message
...
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.



"Sylvain Lafontaine" wrote:

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)


"Dale Fye" wrote in message
...
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.

I've seen this with my ODBC tables to Cobol code. I find I cannot use select
queries with some of my tables....the only thing I can do is make-table.
Once the table is made then further filtering is done.

.....also, see if there are new ODBC drivers.

.....also restart the ODBC server service and possible reboot the ODBC client.

......also verify the linked table has a valid primary key and that your ODBC
client digested table with the key the ODBC table developer intended.

"Dale Fye" wrote:

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.