MS Access - Update Latest Note Using Joined Tables [duplicate]

Question

First, thank you in advance for your assistance.

I have two tables in MS Access: [BillNotes] which holds notes pertaining to Bill #s and [OpenBills] which holds Bill information.

Question: How can I update [OpenBills] so that the latest note is obtained from [BillNotes] as shown below.

Here are examples of my two tables:

[BillNotes]     
BillNumber  NoteDate    BillNote
1           4/1/2022    Note 1
1           4/2/2022    Note 2
1           4/3/2022    Note 3
2           4/1/2022    Note 1
2           4/2/2022    Note 2
2           4/3/2022    Note 3
3           4/1/2022    Note 1
3           4/2/2022    Note 2
3           4/3/2022    Note 3

[OpenBills]     
BillNumber  OrderNumber Amount  LatestNote
1           101         1000    4/3/2022 - Note 3
2           105         10500   4/3/2022 - Note 3
3           107         200     4/3/2022 - Note 3

Here is the query I have which partially works. It doesn't always get the latest note.

UPDATE [OpenBills] INNER JOIN [BillNotes] ON 
[OpenBills].BillNumber = [BillNotes].BillNumber
SET [OpenBills].LatestNote = [BillNotes].[NoteDate] & " - " & [BillNotes].BillNote
WHERE (SELECT MAX([BillNotes].NoteDate) FROM [BillNotes]);

Note: The Update statement I am needing would populate LatestNote in OpenBills. Otherwise, there wouldn't be a value for LatestNote in OpenBills.

NoteDate in BillNotes is a ShortDate

Any help is appreciated. Thank you.

Answer 1

As always start with some sort of normalized table Structure like:

Normally whether a Bill was open and the amount would be calculated from some orderdetails and paymentdetails tables, but for simplicity we add isOpen and Amount. Please note that once you have IsOpen there is no need for an OpenBills table. OpenBills can be generated whenever it is needed. If you must make an OpenBills Table change the query from select to make table. We need a totals query because for each bill there is a group of notes from which we are calculating & formating the Latest Note.

For each bill we calculate the expression:

LatestNote: Max([BillsNotes].[NoteDate]) & "-" & Last([BillsNotes].[BillNote])

resulting in the SQL:

'Sorry, to get the having clause requires the isOpen column is added with a criteria of true then only open bills will be shown

SELECT Bills.BillNumber, Orders.OrderNumber, Orders.Amount, Max([BillsNotes].[NoteDate]) & "-" & Last([BillsNotes].[BillNote]) AS LatestNote
FROM Orders INNER JOIN (Bills INNER JOIN BillsNotes ON Bills.BillNumber = BillsNotes.BillNumber) ON Orders.OrderID = Bills.OrderID
GROUP BY Bills.BillNumber, Orders.OrderNumber, Orders.Amount
HAVING (((Bills.isOpen)=True))

Giving:

---

| BillNumber | OrderNumber | Amount | LatestNote |

| 1 | 101 | $1,000.00 | 4/3/2022-note 3 |

| 2 | 105 | $10,500.00 | 4/3/2022-note 3 |

| 3 | 107 | $2,000.00 | 4/3/2022-note 3 |