The record source for a report has several records per person.
The number of records depends on the person.

For example
Employee number, announcement date, transfer department
123 March 1, 2012 Sales Section 3
123, March 1, 2018, Sales Section 2
123, March 1, 2020, Sales Section 1

There is data like.

When I want to give a resignation in a report
I will list the original department and the new department, but in the above example, I would like to output the original department as the sales department 2 and the new department as the sales department 1.

As a document example,
As of March 1, 2020, we will order the work of Sales Division 2 to Sales Division 1.

I want to write it in the resignation like this.

How can I bring up the new department and the previous record in which the new department is registered in the report?

I would appreciate it if you could tell me.

  • Answer # 1

    Place three text boxes on the report whose record source is the table and set as follows.
    The table name is assumed to be "Movement history".

    namePrevious announcement date
    Control source
    = DMax ("Announcement date", "Transfer history", "Employee number ="&[Employee number]&"AND Announcement date<#"&[Announcement date]&"#")

    namePrevious department
    Control source
    = DLookUp ("Transfer Department", "Transfer History", "Employee Number ="&[Employee Number]&"AND Announcement Date = #"&Nz ([Previous Announcement Date], "1000/1/1")&" # ")

    Control source
    = Format ([announcement date], "yyyy year m month d day")&"with"&[previous department]&IIf ([previous department] Is Null, "", "from")&[transfer department]&" Order to work. "

    that's all.

    If you want to freely enter and edit the letter of appointment

    Create a table for storing the letter of appointment. Table name "Resignation Master"
    The field type should be "long text". Field name "Resignation"

    Create a form that uses this table as the record source, place a text box, and make the control source "resignation".
    Save this form. The name is "F_Resignation".

    Open the form and enter the letter of appointment.
    Input example

    We will order the work of the {transfer department} from the {previous department} on the {announcement date}.

    You can also enter line breaks. The point is to enclose the referenced items in {}, such as {announcement date} {previous department} {transfer department}.
    It doesn't have to be {}, but it can be [] or [].

    Add this table to the report's record source query. It will not be connected.

    Set the control source of the text box "Resignation" as follows, leaving the text boxes "Previous announcement date" and "Previous department" as described above.

    = Replace (Replace (Replace ([Resignation], "{Announcement Date}", Format ([Announcement Date], "yyyy Year m Month d Day")), "{Previous Department}", [Previous Department]), "{Transfer Department}", [Transfer Department])

    By the way, in the above, the DMax function and DLOokup function were used to get the previous department, but you can also get it by using a subquery. If you can understand SQL, this is fine.

        a. *,
        Transfer history. Transfer department AS Previous department
            SELECT b. Employee number, b. Transfer department, b. Announcement date, Max (c. Announcement date) AS Previous announcement date
            FROM Transfer history AS b INNER JOIN Transfer history AS c ON b. Employee number = c. Employee number
            WHERE c. Announcement date<b. Announcement date
            GROUP BY b. Employee number, b. Transfer department, b. Date of issuance
        ) AS a
        INNER JOIN transfer history As d
        ON a. Previous announcement date = d. Announcement date AND a. Employee number = d. Employee number;


     b. *,
     (SELECT TOP 1 Transfer Department FROM Transfer History AS b
       WHERE a. Employee number = b. Employee number AND a. Date of issuance<b. Date of issuance
       ORDER BY announcement date DESC
     ) AS previous department
    FROM transfer history AS a;