By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Why does Access get confused with the result set?

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.