How to Use Sub-Queries in Expression?

[prabha_friend]

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.

 

[apr pillai]

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.

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:

LastOrder: [OrderNumber]-5

 

[Rabbie]

This link will help you get to grips with sub-queries.

This link has some more advanced information