SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Syntax in comparison with MS Access


SQL Syntax in comparison with MS Access

Author
Message
crazy_new
crazy_new
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 375
Hi, Could anyone please assist me with the following:

SELECT InvNo,InDate,CaseNo,ClientRef,Curr,InvFullAmount,InvOutstAmount From DebtTran
FROM DebtTran
WHERE NameNo = (SELECT TOP 1 (NameNo) FROM DebtTran).

How would this be written in MS Access.

Thanks in advance.
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1614 Visits: 8496
Assuming all your tables are the same SQL to Access, you should be able paste into a new query in SQL view and run it.
TOP 1 with no Order By is questionable.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26886 Visits: 17557
crazy_new (2/12/2014)
Hi, Could anyone please assist me with the following:

SELECT InvNo,InDate,CaseNo,ClientRef,Curr,InvFullAmount,InvOutstAmount From DebtTran
FROM DebtTran
WHERE NameNo = (SELECT TOP 1 (NameNo) FROM DebtTran).

How would this be written in MS Access.

Thanks in advance.



This query won't work in any DBMS as written. There are two FROM clauses.

Why are you using a subquery here? Your subquery is the same table as the base query. The way you have this written it is possible to return more than 1 row. If you have two rows in your table with the NameNo that is returned by your subquery you will get two rows.

Also, as Greg eluded to, you should not use a TOP without and ORDER BY. When you don't include an order by you have no way of knowing which row you will get back.

You could greatly simplify this like this:


SELECT TOP 1 InvNo, InDate, CaseNo, ClientRef, Curr, InvFullAmount, InvOutstAmount
From DebtTran
order by NameNo --or whatever column makes sense



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1614 Visits: 8496
Oooooppsss, I see I missed the extra from in the upper query.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26886 Visits: 17557
Greg Edwards-268690 (2/14/2014)
Oooooppsss, I see I missed the extra from in the upper query.


Easy to miss with poor formatting. ;-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search