kerrybenno
asked on
Access Query
Hi
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.
HR-TEST-DB.mdb
Please help.
Thanks in advance.
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.
HR-TEST-DB.mdb
Please help.
Thanks in advance.
ASKER
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?
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?
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
i don't know why you should have wildcard values in a field
ASKER
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 ok...it makes sense to me of course, but I know what i'm trying to do.
HR-TEST-DB.mdb
Thanks for your help.
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 ok...it makes sense to me of course, but I know what i'm trying to do.
HR-TEST-DB.mdb
Thanks for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I came up with a different way of achieving what I needed.
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...
JeffCoachman
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...
JeffCoachman
click on Show Table and add the table tbl_FormOptions
create a join on City field with table "tbl_Postcode_lookup