Search code examples
ms-access

Search query MS Access

I have a database as follows The first column is the name. The second column bears the number of the son in the family. The third column is the family Code.

-Explanatory information: -The first column in the database. Contains the name of the father and children and they are not arranged -The second column in the database. It contains the number of fathers and children in the database where the parents take nothing and the children take numbers. -The third column in the database. It contains a code that is specific to each family and does not subscribe to any other family.

What is required when searching in query as follows:

The query is made for a specific name, where the search is for displaying all fields containing the same name if it is for a father or children. Provided that the final form of the offer is to collect parents only and arrange them in ascending order.

  • The data in the images is for illustration and the actual column names of the query

enter image description here

If you search for a name, let it be Ali, the result will be as follows 2 image:

enter image description here 2- The final result With the order of the parents in ascending name order.

enter image description here

MY TRY NOT WORK

SELECT PARENT_NAME, PARENT_NUMBER,PARENT_CODE
FROM PARENT_TB
HAVING (((PARENT_NUMBER) Is Null));
Solution

  • Try using a subquery:

    SELECT 
    IIF(PARENT_NUMBER Is Null, 
        PARENT_NAME, 
        (Select T.PARENT_NAME 
        From PARENT_TB As T 
        Where T.PARENT_CODE = PARENT_TB.PARENT_CODE And T.PARENT_NUMBER Is Null)) As ParentName,
    PARENT_CODE
FROM 
    PARENT_TB
WHERE 
    PARENT_NUMBER = "Ali"