Querying two tables to find matching records


#1

Apologies for using the query reference, but more used to access.

Anyway my issue is that I have two tables proj hours and proj payments. and Im trying to find a set of records where hours have been worked but not paid. Below is the basic structure of the two tables
Proj Hours

  • Client name
  • Employee name
  • Date worked (This is the date the service was performed on)
  • hours worked
  • bill rate
  • ProjNameDateWorked = Client name &" "& Date wokred

Proj Pymts

  • Client name
  • payment amount
  • Payment method
  • Service Date (this is the date the service was performed on)
  • payment date
  • ProjNameSvcDate = Client name &" "& Service date

I’m looking for an easy way to query these two tables to find missing payments by comparing the fields ProjNameDateWorked and ProjNameSvcDate.

So logically it would be select all records from proj hours and related records from proj payments where the two fields above are equal.

Is there a way to accomplish this with Airtable? Maybe a junction table or some other approach.

Would love any insight anyone can give.

Thanks.