Lookup Question

[sseniour]

What is best way to accomplish result?

Table A	SUPPLIER	DOCK	ORDER NO
	12345	XX	20250606
Table B	SUPPLIER	DOCK	ORDER NO	LOG_PT	LOCAL_DT
	12345	XX	20250606	8888PYD	5/30/25 16:34
	12345	XX	20250606	8888P	6/4/25 9:15
Result
	SUPPLIER	DOCK	ORDER NO	ARR	UNL
	12345	XX	20250606	5/30/25 16:34	6/4/25 9:15

[mike60smart]

Hi
Can you upload a screenshot of your relationships?

Or better still the actual database.

[xps35]

How do you know which record in Table B holds the "ARR" and which holds the "UNL"?
Is it the "PYD" part of LOG_PT?

[June7]

Will there be only 2 Table B records for each order?

From the example data, you want Min(LOCAL_DT) AS ARR and Max(LOCAL_DT) AS UNL for each order. This would be accomplished in an aggregate (GROUP BY) query.

Are you really duplicating Suppler, Dock, Order No into Table B?

[jojowhite]

as suggested you create a Query against Table B:

SELECT SUPPLIER, DOCK [ORDER NO], MIN(LOG__PT) AS LOGPT,  MAX(LOCAL_DT) AS LOCALDT FROM [TABLE B] ORDER BY SUPPLIER, DOCK, [ORDER NO];

[June7]

jojo, almost - consider:

SELECT SUPPLIER, DOCK, [ORDER NO], MIN(LOCAL_DT) AS ARR, MAX(LOCAL_DT) AS UNL FROM [TABLE B] GROUP BY SUPPLIER, DOCK, [ORDER NO];

[sseniour]

Yes "8888PYD" would be the ARR time.

[CJ_London]

On my phone so can’t provide the sql but you could join table a to table b on order no or perhaps supplier and order no. Otherwise, what is the purpose of table a?