Use a date within a query to select which monthly table to query
<p>I'm new and asking for help</p>

<p>Within ACCESS
(Amongst a tangled web of mapping tables)</p>

<p>I have a table of approvals that has the date they were set up.</p>

<p>I have a years worth of separate monthly HR staff lists that are snapshots including line mangers grade and where people were working etc.</p>

<p>I need to bring back everyone's location on the date they applied for approval.</p>

<p>Put it another way?</p>

<p>I have a range of [approval.dates] going back 3 years in one table (I'm only interested in the previous 12 months)
I need to search the [monthly reports] for each approval date and bring back a [monthlyreport.location] at that date. </p>

<p>Each approval date can only match to a month not a specific date.</p>

<p>Conditionally choosing which table to reference is the most succinct way I can put it.</p>

<p>Thank you for reading, I hope it made a bit of sense.</p>