Why does Access get confused with the result set?

[Robert]

I built a query in Query Analyzer and mapped it to Access 2002. The result
set returned is identical except cells in some records in the Access result
have #Deleted. This cast a shadow of doubt on the reliability of Access.
Am I doing something incorrect? Comments?

Access query

SELECT [dbo_Accounts$].Account, [dbo_Regions$].Region_nm, [dbo_Sites$].Site,
dbo_Results.[ELAPSED TIME]
FROM [dbo_Sites$] INNER JOIN ([dbo_Regions$] INNER JOIN ([dbo_Accounts$]
INNER JOIN dbo_Results ON [dbo_Accounts$].Account_ID =
dbo_Results.ACCOUNT_ID) ON [dbo_Regions$].Region_ID = dbo_Results.REGION_ID)
ON [dbo_Sites$].SITE_ID = dbo_Results.SITE_ID
WHERE (((dbo_Results.[ELAPSED TIME])>=30));

Result set
Query2 Account Region_nm Site ELAPSED TIME
MIS17570 Southwest Mesa #Deleted
MIS17570 Southwest Mesa #Deleted
MIS13640 Northwest Everett 77
MIS17570 Southwest Mesa #Deleted
davec Northwest Renton 75
davec Northwest Renton 73
MIS13640 Northwest Everett 73
Query Analyzer

select acc.account, reg.region_nm, sit.site, res.[elapsed time]
from accounts$ as acc
join results as res on res.account_id = acc.account_id
join regions$ as reg on reg.region_id = res.region_id
join sites$ as sit on sit.site_id = res.site_id
where res.[elapsed time] >= 30
order by res.[elapsed time] desc

Result set

MIS17570 Southwest Mesa 80
MIS17570 Southwest Mesa 79
MIS13640 Northwest Everett 77
MIS17570 Southwest Mesa 76
davec Northwest Renton 75
davec Northwest Renton 73
MIS13640 Northwest Everett 73

[Larry Linson]

It's in the documentation and the Help. Access' dialect of SQL has some
differences from SQL Server. You might well get some anomalies if you tried
to use SQL Server's flavor of SQL in Oracle, or Informix, or DB2, or even
Sybase SQL Server.

Larry Linson
Microsoft Access MVP

"Robert" <st*******@boeing.com> wrote in message
news:Hs********@news.boeing.com...

I built a query in Query Analyzer and mapped it to Access 2002. The result set returned is identical except cells in some records in the Access result have #Deleted. This cast a shadow of doubt on the reliability of Access.
Am I doing something incorrect? Comments?

Access query

SELECT [dbo_Accounts$].Account, [dbo_Regions$].Region_nm, [dbo_Sites$].Site, dbo_Results.[ELAPSED TIME]
FROM [dbo_Sites$] INNER JOIN ([dbo_Regions$] INNER JOIN ([dbo_Accounts$]
INNER JOIN dbo_Results ON [dbo_Accounts$].Account_ID =
dbo_Results.ACCOUNT_ID) ON [dbo_Regions$].Region_ID = dbo_Results.REGION_ID) ON [dbo_Sites$].SITE_ID = dbo_Results.SITE_ID
WHERE (((dbo_Results.[ELAPSED TIME])>=30));

Result set
Query2 Account Region_nm Site ELAPSED TIME
MIS17570 Southwest Mesa #Deleted
MIS17570 Southwest Mesa #Deleted
MIS13640 Northwest Everett 77
MIS17570 Southwest Mesa #Deleted
davec Northwest Renton 75
davec Northwest Renton 73
MIS13640 Northwest Everett 73
Query Analyzer

select acc.account, reg.region_nm, sit.site, res.[elapsed time]
from accounts$ as acc
join results as res on res.account_id = acc.account_id
join regions$ as reg on reg.region_id = res.region_id
join sites$ as sit on sit.site_id = res.site_id
where res.[elapsed time] >= 30
order by res.[elapsed time] desc

Result set

MIS17570 Southwest Mesa 80
MIS17570 Southwest Mesa 79
MIS13640 Northwest Everett 77
MIS17570 Southwest Mesa 76
davec Northwest Renton 75
davec Northwest Renton 73
MIS13640 Northwest Everett 73

[Robert]

I think I was not clear. When I indicated I mapped the SQL query to Access
I did not copy the SQL code. I built the query from the Design View. Both
queries act/react the same way. The problem is Access is not returning a
consistent result set. It is returning cells that are undefined or deleted.
If Accsss is acting this way there is a problem in the way it works or my
query is doing something expected. Where in the documentation are you
refering? thanx
"Larry Linson" <bo*****@localhost.not> wrote in message
news:pK*****************@nwrddc01.gnilink.net...

It's in the documentation and the Help. Access' dialect of SQL has some
differences from SQL Server. You might well get some anomalies if you tried to use SQL Server's flavor of SQL in Oracle, or Informix, or DB2, or even
Sybase SQL Server.

Larry Linson
Microsoft Access MVP

"Robert" <st*******@boeing.com> wrote in message
news:Hs********@news.boeing.com...

I built a query in Query Analyzer and mapped it to Access 2002. The

result

set returned is identical except cells in some records in the Access

result

have #Deleted. This cast a shadow of doubt on the reliability of Access. Am I doing something incorrect? Comments?

Access query

SELECT [dbo_Accounts$].Account, [dbo_Regions$].Region_nm,

[dbo_Sites$].Site,

dbo_Results.[ELAPSED TIME]
FROM [dbo_Sites$] INNER JOIN ([dbo_Regions$] INNER JOIN ([dbo_Accounts$]
INNER JOIN dbo_Results ON [dbo_Accounts$].Account_ID =
dbo_Results.ACCOUNT_ID) ON [dbo_Regions$].Region_ID =

dbo_Results.REGION_ID)

ON [dbo_Sites$].SITE_ID = dbo_Results.SITE_ID
WHERE (((dbo_Results.[ELAPSED TIME])>=30));

Result set
Query2 Account Region_nm Site ELAPSED TIME
MIS17570 Southwest Mesa #Deleted
MIS17570 Southwest Mesa #Deleted
MIS13640 Northwest Everett 77
MIS17570 Southwest Mesa #Deleted
davec Northwest Renton 75
davec Northwest Renton 73
MIS13640 Northwest Everett 73
Query Analyzer

select acc.account, reg.region_nm, sit.site, res.[elapsed time]
from accounts$ as acc
join results as res on res.account_id = acc.account_id
join regions$ as reg on reg.region_id = res.region_id
join sites$ as sit on sit.site_id = res.site_id
where res.[elapsed time] >= 30
order by res.[elapsed time] desc

Result set

MIS17570 Southwest Mesa 80
MIS17570 Southwest Mesa 79
MIS13640 Northwest Everett 77
MIS17570 Southwest Mesa 76
davec Northwest Renton 75
davec Northwest Renton 73
MIS13640 Northwest Everett 73