Query concatenates 2 fields, 1 field is lookup, the other is text, returns incorrect value

[CJ_London]

so to interpret what you are saying

whether some text surrounded by the $ character always identifies a wild card. - No
is the wildcard in the Loc_Original field always the same as the wildcard in Rating1_Original? - No
How does the WC table get populated and updated? - I don't know



On the basis of these answers, then I don't believe what you want to do can be done

Sorry I can't help. On the face of it, it seemed a straightforward requirement, however there are too many fuzzy issues for me to have any confidence in providing a solution that works for you. Your answers just raise more questions

[INeedAccessHelpPlease]

so to interpret what you are saying

whether some text surrounded by the $ character always identifies a wild card. - No
is the wildcard in the Loc_Original field always the same as the wildcard in Rating1_Original? - No
How does the WC table get populated and updated? - I don't know

On the basis of these answers, then I don't believe what you want to do can be done

Sorry I can't help. On the face of it, it seemed a straightforward requirement, however there are too many fuzzy issues for me to have any confidence in providing a solution that works for you. Your answers just raise more questions

It may help if I provide the full database. See attached.FUll DB.mdb

I want to give the user the ability to update the wildcard values and access produce a table with the values. I think a replace would work for this. I'm just not sure how to do this.

[June7]

This is the second time I have downloaded your db and I am no closer to understanding what you want to accomplish. Seems you want to replace data in Value field of WC table yet you cannot clarify where the replacement data would come from. Certainly a form bound to WC table will allow users to type whatever they want into Value field. I am with Ajax, also don't see any solution.

Also, if this is a multi-user database, user edits will likely conflict.

[CJ_London]

If we are to be able to help we need to understand what you are trying to achieve and (for me anyway) I need to be able to visualise what this is. At the moment I have no idea. I asked to you explain your business and what part this plays in it. I even provided an example, but you have not responded. I ask a simple question and I get an obscure response. I summarised what I thought your were saying and you have neither confirmed nor corrected me. I fear we have a severe communication problem which appears to be insurmountable. You are now saying a replace would work but in an earlier post you said 'but it can't be a find and replace because some of the values can be changed to the same thing.' .

[INeedAccessHelpPlease]

If we are to be able to help we need to understand what you are trying to achieve and (for me anyway) I need to be able to visualise what this is. At the moment I have no idea. I asked to you explain your business and what part this plays in it. I even provided an example, but you have not responded. I ask a simple question and I get an obscure response. I summarised what I thought your were saying and you have neither confirmed nor corrected me. I fear we have a severe communication problem which appears to be insurmountable. You are now saying a replace would work but in an earlier post you said 'but it can't be a find and replace because some of the values can be changed to the same thing.' .

I'm not sure that the nature of the business will help you understand any more than what I've typed. It's just design using databases.


You are correct about me saying "but it can't be a find and replace because some of the values can be changed to the same thing." That was before I started to realize that I could query the data table and the newest wildcard values, perform a replace in the table data, then create a new table with these new values. It's starting to make sense now (for me) maybe on how I can approach this with access.

1) query the Wildcard Table for the newest user entered $value$ (not sure what sure what the best approach is for this, userform maybe?)
2) query the Data Table
3) perform find & replace on all $value$ found in Data Table with the newest user entered values from the Wildcard Table
4) save the query/find/replace into a new table named New Data

[CJ_London]

I'm not sure that the nature of the business will help you understand any more than what I've typed. It's just design using databases.

Do you not think a vehicle designer might want to know what terrain the vehicle is required to cross, the number of passengers to be carried, etc before designing the vehicle? after all it's just a vehicle...

I suspect that just as I don't understand you business, you don't understand how databases work and are making assumptions about how it might work. From your latest description the best I can suggest is you need an append query utilising multiple left joins from the data table to the wildcard table. But I'm only guessing and not prepared to put more time in. Since I am unable to understand the context of what you require (which again seems to have changed - point 3 is different to what you have said before), you still have not answered my questions to seek clarification, I regret I cannot help any further.

Good luck with your project

[June7]

Item 3 is what I thought you wanted in your other thread where I offered a suggestion.