Click here to monitor SSC
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-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 365
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
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8258
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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 Moden's 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
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8258
Oooooppsss, I see I missed the extra from in the upper query.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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 Moden's 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