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?
kerrybennoConnect With a Mentor Author 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.
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?
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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
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 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...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.