Display result based on multiple criteria

[cottrera]

Hi I need to diplay the maximun date based on 2 other criterias . 1 criteria is on Table 1 the other criteria is on table 2

For eample look for 'PLUM' in column called Trade (Table 1) and look for 'OUTSTANDING; in column called Status (Table 2) and diplay the MAX date for column called Appointment Date (Table 2)

Exampl of both tables and field for which I need the criteria applied to

Repair Table 1		Appointments Table 2
Trade		Status	Appointment date
PLUMBING		OUTSTANDING	= MAX DATE

hope this makes sense

Richard

[kentyler · Accepted solution]

Repair Table 1			Appointments Table 2
Trade	repair_id		repair_id	Status	Appointment date
PLUMBING	4		4	OUTSTANDING	3/3/2019

you need to be able to have a relationship between your tables. I've added a sample field, repair_id, where all the appointments for a give repair would have the same repair id that the repair does. repair Id must be unique in table 1, and can appear many times in table 2.

Once you have this link you will be able to find the max date in table 2 for a given repair.