How to Use Sub-Queries in Expression?

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 04:53
Joined
Mar 22, 2009
Messages
987
Scenario:
Statement1 is having the Category of an account number (PK:Account Number)
Statement2 is having the Code of the Account Category (PK:Account Number)
Reconstatement is having the Category from Statement1 and Code from the Statement2
Table1 is having the list of Account Categories and Corresponding Codes (Many Codes for a Single Category)

Necessity:
Need to display True/False in the Recon Statement Validating Correct Code is given to Current Category. How to do this?

P.S:
No DLookups please. I believe we can achieve using sub-queries but how to use it in expression for this scenario. Work at stake. Help Needed.
 
Here is an example for a Sub-Query in an expression. You may try it out with Employess and Orders Tables from the Northwind.mdb sample database.

Code:
SELECT Employees.FirstName, (SELECT MAX(Orderid) FROM Orders AS NewOrders WHERE ([NewOrders.EmployeeID]=[Employees.EmployeeID])) AS OrderNumber
FROM Employees;

The new OrderNumber column added with the Sub-Query can be part of other expressions in new columns in the Query.

To Try out an example write the following expression in a new Column:

Code:
LastOrder: [OrderNumber]-5
 
Last edited:
This link will help you get to grips with sub-queries.

This link has some more advanced information
 

Users who are viewing this thread

Back
Top Bottom