0

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.

dirty2k9
  • 35
  • 1
  • 11
  • You shouldn't. Just pull latest note by query when needed. https://stackoverflow.com/questions/10999522/how-to-get-the-latest-record-in-each-group-using-group-by. Saving would require VBA or use DLookup() and DMax() in query. – June7 May 03 '22 at 19:22
  • If you have an OpenBills table, does that mean you have a ClosedBills table? – June7 May 03 '22 at 20:07
  • And that WHERE clause really makes no sense. When executed it becomes: `WHERE 4/3/2022`. Need a field reference to equate to that calculated value. And if every bill does not have the same Max() date, will not return record. – June7 May 03 '22 at 20:14
  • So the latest note in OpenBills in the table above would be pulled from BillNotes. After the Update runs the result should look like OpenBills. – dirty2k9 May 03 '22 at 20:33
  • No ClosedBills table. Lol. Anyways, this Access database is strictly for manipulating data and not used to store transactions. Without the Update Query I am looking for, the LatestNote in the OpenBills table would be empty for each BillNumber. – dirty2k9 May 03 '22 at 20:36
  • Does every bill always have the same note dates as other bills as shown in sample? Your comments don't alter mine. – June7 May 03 '22 at 20:55
  • No, not every Bill will have the same NoteDate or BillNote. This database is designed this way to allow the user to manipulate and update some data using forms with tables instead of queries so that the data can be manipulated similar to the way you would manipulate it in Excel. Queries would just refresh constantly and slow the user down. I know, odd use of Access but it helps this group work collaboratively and a lot faster. – dirty2k9 May 03 '22 at 21:04
  • More representative sample data would be helpful. I have already described options available: 1. calculate when needed; 2. VBA looping a recordset; 3. domain aggregate functions in UPDATE query object. I'll throw in a 4th: saving Max() record for each bill to a temp table and use temp table as source for UPDATE. – June7 May 03 '22 at 21:58
  • Another syntax for selecting latest record for each bill http://allenbrowne.com/subquery-01.html#TopN – June7 May 03 '22 at 22:40

1 Answers1

0

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

enter image description here

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 |

mazoula
  • 1,221
  • 2
  • 11
  • 20