Wildcard Table data fields linked in various placed on another table

[INeedAccessHelpPlease]

I have 2 tables in access. 1 table are my wildcards. These are data points that need to be updated by a user


[code]
id value
wildcard value = $test$

id	value
$test$	value of test

The other table contains a lot of data. All of this data has the same column headers, but the data itself can be anything. In a lot of cases, the data needs to be updated using the wildcard table.

id	value1	value2
1	$test$	some text $test$ here
2	$test$	some more text here $test$
	$test$	$test$ here and here

Is there a way to link table 2 with table 1 so that when i update $test$ all of the $test$ values in tables 2 will update to "value of test"?

[orange]

Please describe what you are trying to accomplish in simple, plain English.
If you show us :
-the original data, and
-a description of the process logic; and
-a sample of the final data, that may help readers.

[INeedAccessHelpPlease]

Please see the attached .mdb file. The WC Table has the data I want sprinkled in and updated in the Data Table. Is there a way to insert individual relationships between the values in the WC Table with the values in the Data Table? Sort of sprinkled in if you know what I mean as every value in the Data column will not need to be connected to the WC Table. Just where you see the $STRING$. There's many more wildcard values that I didn't include that will be sprinkled in the Data Table.

[orange]

Hmmmm?? What does this represent?
Lead us through your proposed logic.
It's just some fields with red lines until we understand what it means in context.

[INeedAccessHelpPlease]

Did you read what i wrote above the 2 table pictures? That is what i want to do.

[June7]

Doesn't make sense to me either.

I see value in DESC2 within text of RATING1 and RATING2. So what exactly do you want to do? Show example of desired result. Why do the WCTable fields have same data?

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Also should not use reserved words as names.

[orange]

Yes I did -several times. I suggest you think about what you want to do and help us understand the logic of

Sort of sprinkled in if you know what I mean as every value in the Data column will not need to be connected to the WC Table

by using simple terms and a couple of examples.
Examples should show the starting values and the final result clearly.

It always helps you and readers if you provide a description of your "business" in simple English without jargon or database terms.
Clearly understanding what you have and need can help focus responses/solutions.

[INeedAccessHelpPlease]

The Data Table should pull in the WC Table Values where they are referenced. So the text that is circled in red would be replaced with the value of that wildcard in the WC Table. If the value of $BKRX1$ wildcard changes to "Texas" in the WC Table, then everywhere the $BKRX1$ wildcard value exists in the Data Table, it will update to "TEXAS". Does that make sense now? Also, the WILDCARD column in the WC Table does not change, only the VALUE column will change therefore updating the DATA table references to the WC TABLE VALUES column for the appropriate WILDCARD. I've included before and after pictures of the WC TABLE and the DATA Table.

[orange]

Yes. You have records with identified placeholders/marks and a table of values to be used to substitute the placeholders/marks with the appropriate value.
This technique was/is often used with templates (contracts, letters...) where the body of the document has named placeholders and you substitute these with appropriate data. sometimes referred to as boiler-plate. Same document but the variables get the proper values for the current context.

[June7]

If there was only one wildcard in RATING1 and it was the same as in DESC2, this would be a simple JOIN and use Replace() function.

SELECT DATA.*, Replace([RATING1], [WILDCARD], [VALUE]) AS AdjRating FROM WC INNER JOIN DATA ON WC.WILDCARD = DATA.DESC2;

Complication arises when RATING1 has multiple wildcards. I expect a VBA custom function is needed.

[INeedAccessHelpPlease]

If there was only one wildcard in RATING1 and it was the same as in DESC2, this would be a simple JOIN and use Replace() function.

SELECT DATA.*, Replace([RATING1], [WILDCARD], [VALUE]) AS AdjRating FROM WC INNER JOIN DATA ON WC.WILDCARD = DATA.DESC2;

Complication arises when RATING1 has multiple wildcards. I expect a VBA custom function is needed.

I'll echo what you requested, please provide clear and concise details about what you are suggesting. WHere do you use the join/replace? What is SELECT DATA.*, Replace([RATING1], [WILDCARD], [VALUE]) AS AdjRating FROM WC INNER JOIN DATA ON WC.WILDCARD = DATA.DESC2;??? Is this VBA Code?

[June7]

It is the SQL statement of a query. Use Query Designer to build a query then switch to SQLView to see statement. Basic Access functionality.

[INeedAccessHelpPlease]

It is the SQL statement of a query. Use Query Designer to build a query then switch to SQLView to see statement. Basic Access functionality.

Will using this sql statement allow me to update the value of the wildcard in the wildcard table multiple times. And each time I update the wildcard it will replace values in the other table? What if 2 wildcard values are the same? I suspect that will create an issue an thus why I do not want to use a replace function. I'd prefer a data field inside a field.

[June7]

It does not UPDATE anything. No data in tables is altered. It merely substitutes text in query. Not sure what you mean by 'a data field inside a field' but it does not sound like something intrinsic to Access.

If you manually edit a record in WC to change a Value it will be reflected in the query.

What exactly are you trying to accomplish?