I wondered whether something is possible and how I'd go about it.
I have a reference number on one table, and then in an audit trail I will have a description (something like 'Reference 12345 is complete') and a date.
I'm trying to link the date of the audit trail post [where it states complete] to the reference number of the other table (because this tables holds loads of other info I need).
I'm much better on Excel and I would simply do
Index(AuditTrailDate, match ("Reference "&ReferenceNumber&" is complete", AuditTrailDescription ,0))
to give me:
Col 1 Col 2
Reference Number AuditTrailDate
Is there a way of doing this in the query itself? I know I can do it as an expression in the table but for other reasons it'd be better to have it this way round instead.
Lookup("Reference "&ReferenceNumebr&" is Complete",AuditTrail.Description, AuditTrail.DateAdded)
If this doesn't make sense let me know and I'll try and send some more info.