How to make access record same date for different records, but with same reference?

[rabbit2323]

My problem: I got 2 suppliers to fulfill one order. Which means I have 2 Purchase Order numbers and one invoice number. When customer sends payment, I need to record the payment receive date against both Purchase Order numbers. Sometimes I can have lots of purchases to fulfill one order and I don't want to manually copy the same payment receive date for each one.

My question: How do I get the database to automatically record the same paid date when I record the date for one of the Purchase Orders? Please note I'm new to making a database and I only took a beginner's course on it.

Below is an example of what my table looks like:

PURCHASE ORDER NUMBER	INVOICE NUMBER	SALE AMOUNT	PAYMENT RECEIVE DATE
P20210001	1001	$100.00	9/9/2021
P20210002	1001	$200.00	9/9/2021
P20210003	1001	$500.00	9/9/2021
P20210004	1002	$100.00
P20210005	1002	$100.00

 

[June7]

Could run an UPDATE sql action with code in the PAYMENT RECEIVE DATE textbox AfterUpdate event.

Or change data structure so date is in Invoices table instead.

Strongly advise not to use spaces in naming convention, all caps is harder to read and is 'screaming'. Camel Case would be better, with or without underscore: PurchaseOrderNumber, Purchase_Order_Number.

 

[plog]

With what you described, [PAY RECEIVE DATE] should not be in that table. Instead it should go into the Invoice table. If you don't have one, you now need one.

Then when you need all the data together as above, you build a query and JOIN the above table with the Invoice table and have everything you need.