Unanswered: Help needed to generate chronological list/report
The DB I have created tracks mortgages and liens attached to a property. The primary form handles the property information. There are two forms related to the property form, one for mortgages, one for liens.
The simplified structure is as follows:
-- Property: File ID; Address; Owner; ST-County
-- Mortgage: Mtg ID; Mortgage Holder; Date of Record; Record Document Ref; Original Amount; Balance; Satisfied (check box); Satisfaction Record
-- Lien: Lien ID; Lien Holder; Amount; Record Date; Document Reference
I need to produce a report of all the mortgages (not satisfied) and liens in chronological order, listing the Holder and Amount.
Can anyone suggest a method to approach this? I have already tried several different ways. My latest attempt was to create a fourth form to hold the information needed for the report. A button script on the Property form then defined a recordset variable with, first, the related mortgages filtered for not satisfied, create variable(s) for the data items needed for the report, and push the data items into a new record created for the report form. Then repeat this for the liens. Then I defined a recordset variable with the report form records associated with the property, sort the records by date, and I print the report. Except for some reason I can not figure out when there were multiple liens, let say, the report records produced were multiple compilations of the liens. Meaning, for a property that had Lien1, RecordDate1, Amount1 and Lien2, RecordDate2, Amount2, there was created two report records both of which were: RecordDate1, Lien1Lien2, Amount1Amount2.
I have a simplified version of the DB available to share, if anyone is willing to take a look.