What's the Access equivalent for this SQL Server query?

[Oscar]

What's the Access equivalent for the SQL Server query :

"SELECT cancelled FROM (SELECT cancelled FROM tblOrders WHERE ID_emp=20
ORDER BY ID DESC) WHERE cancelled=0"

The compiler fires an 'Error in FROM clause'

 

[MH]

SELECT cancelled
FROM tblOrders
WHERE ID_emp=20 AND cancelled=0

Not sure what the subquery is all about, unless I'm missing something?

MH

 

[Oscar]

MH,

You're missing a lot man.

First of all, I need to find the latest entry in the table (ORDER BY ID
DESC) for the specific employee.
After that I need to verify whether the value of the field cancelled=0. This
all needs to be done within one query.

Oscar

 

[Jason Lepack]

Umm, then maybe you should include all the information in your post??
I see that you've posted on Google Groups or some other forum and
you've made the assumption that everyone does the same. Most people
receive these messages through a news reader and therefore don't even
look at your original post because it's in another thread. Keep your
posts about one topic in one thread and that'll keep people all on the
same page.

Now, to your question.

SELECT cancelled
FROM (
SELECT TOP 1 cancelled
FROM tbl_orders
WHERE id_emp = 20
ORDER BY ID DESC)
WHERE cancelled = 0

The subquery selects the most recent cancelled and the query will only
return one record if the subquery returns a true value. I wouldn't
waste the server's time with this though. I would only use the
subquery and let the client handle that itty-bitty check.

Cheers,
Jason Lepack

 

[Oscar]

Hi Jason,

I've posted this subject only with Outlook Express and it was a new thread
and not an addition to an existing thread.

After that I enter your query, the compiler directly fires a 'Syntax error
in from clause'

This is how I entered the query:

"SELECT cancelled FROM (SELECT TOP 1 cancelled FROM tblOrders WHERE
ID_emp=20 ORDER BY ID DESC) WHERE cancelled=0"

Did I or you miss something ?

Oscar

 

[Jason Lepack]

It works fine for me. I tried it using a table in Access with this
structure:

tblOrders:
ID - AutoNumber
cancelled - yes/no
ID_emp - number

Compare that with the structure of your table. Or is your table still
in SQL Server? If it is then post the DDL of it. Right click on the
table and click "Script Table As" -> "Create To" -> "New Query Editor
Window" and paste it here.

If you're trying to run this from SQL Server you will need to put an
alias on the sbuquery.

SELECT cancelled
FROM (
SELECT TOP 1 cancelled
FROM tblOrders
WHERE ID_emp=20
ORDER BY ID DESC) A
WHERE cancelled=0

Cheers,
Jason Lepack

 

[MH]

Hi Jason,

I've posted this subject only with Outlook Express and it was a new thread
and not an addition to an existing thread.

But you did not give a full and complete question, what you postsed is:

- What's the Access equivalent for the SQL Server query :
-
- "SELECT cancelled FROM (SELECT cancelled FROM tblOrders WHERE ID_emp=20
- ORDER BY ID DESC) WHERE cancelled=0"
-
- The compiler fires an 'Error in FROM clause'

Which does not include the "TOP 1" in the select clause and makes the
subquery pointless in the context of the question.

MH

 

[Oscar]

Hi Jason,

I'm running it from the Visual Basic IDE. So I use Access 97 as a back-end
database while coding is done in VB.
Even after I added the alias it still fires this error.

Oscar

 

[Jason Lepack]

What is the structure of your table?

Hi Jason,

I'm running it from the Visual Basic IDE. So I use Access 97 as a back-end
database while coding is done in VB.
Even after I added the alias it still fires this error.

Oscar

"Jason Lepack" <[email protected]> schreef in bericht









- Show quoted text -

 

[Oscar]

It doesn't differ either TOP 1 is added or not for the SQL Server situation.
In Access, I still have this annoying 'Error in FROM clause'

I didn't include all details since the original query is quite large and
doesn't clarify the problem. Therefore I am investigating it part by part.

Oscar

 

[Oscar]

exactly the same as the structure of your table.

Oscar

 

[Jason Lepack]

I'm perplexed, let's break it down a bit more.

Does this query work?

SELECT TOP 1 cancelled
FROM tblOrders
WHERE ID_emp = 20
ORDER BY ID DESC

 

[Oscar]

Yes that query works good.

I'm perplexed, let's break it down a bit more.

Does this query work?

SELECT TOP 1 cancelled
FROM tblOrders
WHERE ID_emp = 20
ORDER BY ID DESC