Home>
I want to achieve

There are two tables:

〇Table A (master table)
-Column 1: Certification number (char type, primary key)
-Column 2: Type (char type)

(Sample data)

Certification number type
NINTEI01 TYPE01
NINTEI02 TYPE02
NINTEI03 TYPE03

〇 Table B (transaction table)
-Column 1: Number (char type, primary key)
-Column 2: Certification number (char type, foreign key)
-Column 3: Discrimination flag (char type)
-Column 4: Date and time (date type)

(Sample data)

number Certification number Discrimination flag Date and time
0001 NINTEI01 --- 2020/01/01 12:34:56
0002 NINTEI01 --- 2020/01/02 00:00:00
0003 NINTEI01 --- 2020/01/02 00:00:00
0004 NINTEI02 --- 2020/01/01 12:34:56
0005 NINTEI02 --- 2020/01/02 00:00:00
0006 NINTEI02 --- 2020/01/02 00:00:00
0007 NINTEI02 x 2020/01/03 00:00:00
0008 NINTEI03 x 2020/01/01 12:34:56
0009 NINTEI03 x 2020/01/02 00:00:00
0010 NINTEI03 x 2020/01/02 00:00:00

About these
I want to create the following view by internally joining table A to table B using the certification number as a key.
The columns are as follows.
-Column 1: Table B. Number
-Column 2: Table B. Certification number
-Column 3: Table A. Type
-Column 4: Table B. Discrimination flag

Included is one record for each certification number under the following conditions:
-If there is a record with the same certification number and a discrimination flag of "-", the one with the certification flag of "-" and the latest date and time ... ①
-If there is no record with the same certification number and the discrimination flag "-", the date and time is the latest with that certification number ... ②
-If there are multiple records in (1) and (2) above (same date and time), the one with the largest table B. number among the corresponding records (1) and (2).

(Sample data)

number Certification number type Discrimination flag
0003 NINTEI01 TYPE01 ---
0006 NINTEI02 TYPE02 ---
0010 NINTEI03 TYPE03 x
What I tried

Although it is incomplete, I tried the following SQL.
(To execute a query, define the query on VBA, define the SQL statement, apply the SQL statement to the query, and execute it with OpenQuery.)

SELECT table B. number, table B. certification number, table A. type, table B. discrimination flag,
       switch (table B. discrimination flag = "-", dateadd ("yyyy", 5000, table B. date and time), true, table B. date and time) as weighting
FROM table B INNER JOIN table A
ON Table A. Certification Number = Table B. Certification Number
ORDER BY table B. certification number, dateadd ("yyyy", 5000, table B. date and time), true, table B. date and time) DESC

Since there are two conditions, date and time and discrimination flag, a date type column "weighting" that can be judged by one column is added. (If the discriminant flag contains "-", add 5000 years to the column "weighting")

("ORDER BY table B. certification number, dateadd (" yyyy ", 5000, table B. date and time), true, table B. date and time) DESC", so the first record for each certification number is displayed in the view. If i display it, you will get the desired view. )

from here,
Prepare a column "rank" that converts the weighting to rank, and think about getting only "rank" = 1.
I've considered using the dcount function, but I'm not sure how to pass the join table as a table to the arguments of the dcount function.

Are there any other good ways, including smarter ones?
Thanks for your cooperation.

Supplementary information (FW/tool version, etc.)

OS: Windows10 Professional (x64)
Access: Version 1803 (Office 365)

  • Answer # 1

    As a requirement, we only need to limit the number in table B, so consider a query that asks for the required number.
    First of all, the basic part.

    select certification number
         , CDate (Nz (max (iif (discrimination flag = "-", date and time, Null)), max (iif (discrimination flag = "-", Null, date and time)))) as discrimination date and time
         , min (iif (discrimination flag = "-", True, False)) as discrimination
    from table B
    group by certification number


    ·Explanation
    Discrimination date and time: The date and time of the result of finding the maximum of the date and time for each of the "-" of the discrimination flag and the "-" minutes
    Discrimination: Information on whether the discrimination date and time is the discrimination flag "-" or other than that.

    Next, from the above results, find the number considering the case where the date and time are the same.

    select max (tb. number) as discrimination number
    from table B as TB
          inner join (
              select certification number
                   , CDate (Nz (max (iif (discrimination flag = "-", date and time, Null)), max (iif (discrimination flag = "-", Null, date and time)))) as discrimination date and time
                   , min (iif (discrimination flag = "-", True, False)) as discrimination
              from table B
              group by certification number
          ) as cond
          on cond. Certification number = TB. Certification number
            and cond. Discrimination date and time = TB. Date and time
            and cond. Discrimination = iif (TB. Discrimination flag = "-", True, False)
    group by tb. Certification number

    After that, add the above conditions.

    SELECT table B. number, table B. certification number, table A. type, table B. discrimination flag
    FROM table B INNER JOIN table A
          ON Table A. Certification Number = Table B. Certification Number
    where table B. number in (
              select max (tb. number) as discrimination number
              from table B as TB
                    inner join (
                        select certification number
                             , CDate (Nz (max (iif (discrimination flag = "-", date and time, Null)), max (iif (discrimination flag = "-", Null, date and time)))) as discrimination date and time
                             , min (iif (discrimination flag = "-", True, False)) as discrimination
                        from table B
                        group by certification number
                    ) as cond
                    on cond. Certification number = TB. Certification number
                      and cond. Discrimination date and time = TB. Date and time
                      and cond. Discrimination = iif (TB. Discrimination flag = "-", True, False)
              group by tb. Certification number
          )


    It may be faster to combine as an inline view than to make it a where condition.

  • Answer # 2

    If there is one for each certification number, it seems faster to write a subquery for table A.
    It may be confusing on the query design GUI.

    select A. *
      , (select top 1 B. number from table B as B
        where B. Certification number = A. Certification number
        order by IIF (B. Discrimination flag = "-", 0,1), B. Date and time desc, B. Number desc) as number
    from table A as A

    If you can get the primary key of table B, you can get the flag by writing it in another view.
    There is no way to get it in one go by marking it with a minus inversion when pulling the number by pushing it.

    select A. *
      , (select top 1 IIF (B. Discrimination flag = "-", "-", "")&B. Number from Table B as B
        where B. Certification number = A. Certification number
        order by IIF (B. Discrimination flag = "-", 0,1), B. Date and time desc, B. Number desc) as number
    from table A as A