Multiple criteria lookup - dlookup from record source?

[hoffmann]

I might be overthinking this one but could use a fresh point of view...

Would it be possible to perform a lookup similar to a DLookup within the record source of a form? This particular form must choose between 4 different tables depending on the "Detail Type" that the user chooses. I haven't had success with DLookup as it looks for one particular table and wouldn't notice the change in "detail type". I looked into the idea of using pre-queries but I am looking at almost 64 queries to make this work if I go that route.

I need the lookup to find a particular month and then place each of the metrics from that row into its' respective spot on the chart.

Background info : I currently have a table with 1 entry per employee monthly with approximately 15 fields of various performance metrics. I need to create a form that shows the last rolling 12 months for each metrics.

Thanks in advance!

[June7]

What do you mean by 'respective spot on the chart'? What is chart - just a table matrix?

Why are there 4 tables with similar data? I presume the data is similar otherwise why would it be displayed in the same field? Data structure doesn't seem normalized. Do these 4 tables have the same fields?

If you want to provide db for analysis, follow instructions at bottom of my post.