Access Query

I have attached an example so it's easier to see what i'm trying to achieve.

The tables tbl_CrosstabHeaders and tbl_FormOptions are updated when the user completes fields on a form.
I want to use this information in the query 'qry_HR_TESTING' for the parameters.
I cannot work out how to join the the table tbl_FormOptions to the query.

Not sure if it will make a difference, but in the table tbl_FormOptions only 1 of the fields will have a string in, the other 4 fields will be a wildcard.


Please help.
Thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
open the query in design view
click on Show Table and add the table tbl_FormOptions
create a join on City field with table "tbl_Postcode_lookup
kerrybennoAuthor Commented:
What if the City field in tbl_FormOptions is wildcard and it is the County that has been completed?
I don't want to have to go in and manually change it each time.

Is there a way to join the tables so it doesn't matter which field is completed in tbl_FormOptions?
Rey Obrero (Capricorn1)Commented:
you can have records returned in a query if the value in the joined field are equal.

i don't know why you should have wildcard values in a field
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

kerrybennoAuthor Commented:
If I take the wildcard values out I can't get the query to work at all.

In the form the user completes, they can choose EITHER Postcode_Sector, Postcode_District, City, Constituency or County.  Whatever they choose gets put in to the table tbl_FormOptions and the other fields I put in a wildcard.

So, in my attached (updated) example the tbl_FormOptions has the City populated and in the query I have joined the City fields.  However, I don't know what to do with the query so whatever field they choose to populate that the query works.
In the attached db the query is working, but what if the next time the user chooses County and enters Aberdeenshire, this query then won't work.

I hope I have explained this makes sense to me of course, but I know what i'm trying to do.

Thanks for your help.
kerrybennoAuthor Commented:
I have sorted this out another way.

I put the form values in to another table and all of my queries link to that.
Much easier for me.
Thanks anyway.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kerrybennoAuthor Commented:
I came up with a different way of achieving what I needed.
Jeffrey CoachmanMIS LiasonCommented:
It is not clear (to me at least) why your query is designed in this way...

I seems like you are trying to use a values from a joined table as the criteria for the same query.

Typically a *form* value is referenced:
Like Forms!SomeForm!SomeControl
...this way it may be different for each form record.

In your query the results are the same whether the criteria is there or not.
Also a table may have many records so I can see how your criteria would "Look Up" the correct record.

Perhaps if you first told us a little about what you are trying to do, this would be clearer.

For example, your query returns 2 records.
What results are you ultimately looking for?

Perhaps your query (or your interface) needs to be redesigned...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.