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

[INeedAccessHelpPlease]

i HAVE A QUERY THAT IS BUILT USING A CONCATENATION ON 2 FIELDS OF A TABLE. 1 OF THE FIELDS IS TEXT. THE OTHER FIELD IS A LOOKUP FIELD (BASED ON ANOTHER TABLE) WITH A SELECTED VALUE. WHEN I CONCATENATE THESE IN THE QUERY, THE QUERY RETURNS THE ID OF THE LOOKUP VALUE AND NOT THE ACTUAL VALUE OF THE LOOKUP VALUE. WHAT CAUSES THIS? SORRY FOR ALL CAPS.

[June7]

Because the actual value of the lookup field is the ID. The alias text is only for display. If you want the text in an expression have to join tables in query.

This is one reason most experienced developers do not build lookups in table.

[INeedAccessHelpPlease]

What is the alternative that experienced developers recommends as opposed to using the lookup tables and joining tables in query. Also Thanks for you help! I'm slowly chopping away at it.

[INeedAccessHelpPlease]

Also, if you think it'd be helpful, I can upload the database so you can see what I'm trying to do and maybe be able to help easier.

[June7]

They use lookup tables but don't use lookup fields. Build combobox or listbox on form.

Joining tables in query cannot be avoided. That is nature of relational database.

[INeedAccessHelpPlease]

If i build a combobox or listbox on a form, would this allow me to update another field with the concatenated value? I really wish I knew exactly what I needed to do to get this working. It's frustrating. I have attached a sample set of the data I'm working with. I know there's got to be a way that I can get this data concatenated and only have to update wildcards in 1 location but also have the ability to edit the data on the other table as well. Ultimately, I'll be importing the updated data into another program for use. In the Wildcards/WC table I've placed test next to the values i would like to update on the data table. On the data table you will notice some columns with _original. These are the original data fields with just unintelligent text for the wildcards. I want to be able to recreate these same strings with the ability to update the wildcard table and to produce fields that match the _original fields.

[June7]

Saving calculated data requires code (macro or VBA) or an UPDATE or INSERT action SQL.

[CJ_London]

I am confused about your example data and what you are trying to achieve. Concentrate on one example and show the values from each table and the expected outcome required.

I think you are saying you have this data

ID	LOC_QRY1	LOC_QRY2	LOC_ORIGINAL	DREF_QRY	DREF_ORIGINAL	RATING1_QRY1	RATING1_QRY2	RATING1_QRY3	RATING1_QRY4	RATING1_ORIGINAL
11	$BKRX1$ TEST	PNL	$BKRX1$ PNL	$PMDC226A0$ TEST	$PMDC226A0$	52a SUPV BFIA BKR	$BKRX1$ TEST	FAIL (62BF2/52a)		52a SUPV BFIA BKR $BKRX1$ FAIL (62BF2/52a)

and want to substitute the text in red with the value column in the WC table by looking up $BKRX1$

$WC$	VALUE
$BKRX1$	$BKRX1$ TEST

so you end up with

52a SUPV BFIA BKR $BKRX1$ TEST FAIL (62BF2/52a)

Is this data 'real' in its structure? i.e can the $ character be used to identify a wildcard string in your rating text or is this how your data comes - with the value already included, just not applied?

It would be helpful if you can clarify your original data structure - this has the feeling you are patching together different solutions which are gradually moving you towards your objective rather than just meeting the objective. Your first post in the other thread says

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"?

On the face of it can be done in one query, but your data table seems to already meet that requirement without needing the lookup table

[INeedAccessHelpPlease]

I am confused about your example data and what you are trying to achieve. Concentrate on one example and show the values from each table and the expected outcome required.

I think you are saying you have this data

ID	LOC_QRY1	LOC_QRY2	LOC_ORIGINAL	DREF_QRY	DREF_ORIGINAL	RATING1_QRY1	RATING1_QRY2	RATING1_QRY3	RATING1_QRY4	RATING1_ORIGINAL
11	$BKRX1$ TEST	PNL	$BKRX1$ PNL	$PMDC226A0$ TEST	$PMDC226A0$	52a SUPV BFIA BKR	$BKRX1$ TEST	FAIL (62BF2/52a)		52a SUPV BFIA BKR $BKRX1$ FAIL (62BF2/52a)

and want to substitute the text in red with the value column in the WC table by looking up $BKRX1$

$WC$	VALUE
$BKRX1$	$BKRX1$ TEST

so you end up with

52a SUPV BFIA BKR $BKRX1$ TEST FAIL (62BF2/52a)

Is this data 'real' in its structure? i.e can the $ character be used to identify a wildcard string in your rating text or is this how your data comes - with the value already included, just not applied?

It would be helpful if you can clarify your original data structure - this has the feeling you are patching together different solutions which are gradually moving you towards your objective rather than just meeting the objective. Your first post in the other thread says

On the face of it can be done in one query, but your data table seems to already meet that requirement without needing the lookup table

Here is the real data without any changes.
Data table below:

ID			LOC_ORIGINAL		DREF_ORIGINAL					RATING1_ORIGINAL
1			$BKRX1$ PNL		$PMDC226A0$					52a SUPV BFIA BKR $BKRX1$ FAIL (62BF2/52a)

Wildcard table below.

$WC$	VALUE
$BKRX1$	$BKRX1$
$PMDC226A0$	$PMDC226A0$

here is the data after it changes:

$WC$	VALUE
$BKRX1$	NORMAN
$PMDC226A0$	JEFFERSON

The purpose is so that If I need to change this data again, like below, it will remember which wildcard is where. so i COULD SWAP 2 OF THEM IF I WANTED TO OR JUST UPDATE 1.

$WC$	VALUE
$BKRX1$	JEFFERSON
$PMDC226A0$	NORMAN

This is exactly before, after, and then a second change. The whole purpose of this is to be able to change any wildcard at any time using the wildcard table and they link to the data table so that data is updated.

[June7]

Where the heck do the names Norman and Jefferson come from?

This isn't making any more sense than in your other thread https://www.accessforums.net/showthread.php?t=73486

[INeedAccessHelpPlease]

Norman and jefferson are new values for the wildcards. Why does it matter what I'm changing data to? Ajax seems to understand what I'm trying to do and is much nicer. Its not a complicated idea. I want a find and replace based on a field but it can't be a find and replace because some of the values can be changed to the same thing.

[CJ_London]

I thought I understood but I'm still confused although at least we are seeing more realistic data..

What I expected you to indicate after the change was

$BKRX1$ PNL becomes NORMAN PNL $PMDC226A0$ becomes JEFFERSON

52a SUPV BFIA BKR $BKRX1$ FAIL (62BF2/52a) becomes 52a SUPV BFIA BKR NORMAN FAIL (62BF2/52a)

and I assumed that you or the user, having identified $BKRX1$ and $PMDC226A0$ as wildcards would be completing the WC table with NORMAN, JEFFERSON, whatever value. However your post just says 'it changes' which could mean something completely different. How does the WC table get populated and updated?

And I don't understand

but it can't be a find and replace because some of the values can be changed to the same thing.

Please clarify. What I thought you wanted to do was leave the original data as is and just have a query that substituted NORMAN (or whatever value has been assigned) wherever $BKRX1$ appears - and not update the result into the table. i.e. a bit like a word template. If you want to update a table, then NORMAN becomes the wildcard to ultimately be substituted for JEFFERSON which is something that could get really messy down the line.

You have not answered my question about whether some text surrounded by the $ character always identifies a wild card.

I'll add another question - is the wildcard in the Loc_Original field always the same as the wildcard in Rating1_Original?

You could really do with explaining what your business is and the business reason for doing this. By way of a real example from one of my clients (a pharmacy)

'New drugs appear all the time. These are input into the system by different users but despite the requirement for consistency, sometimes this does not happen. So a drug may be input as xyz drug tablets by one user, and another user not finding it enters it again as xyz drug tabs. (tabs being an abbreviation for tablets). The same issue can apply to other parts of the description (capsules/caps/cap, Pack/Pak/Pk, etc). I need a way to recognise that tablets/tabs etc are the same thing and highlight where there are duplicates.'

[INeedAccessHelpPlease]

What I thought you wanted to do was leave the original data as is and just have a query that substituted NORMAN (or whatever value has been assigned) wherever $BKRX1$ appears - and not update the result into the table. i.e. a bit like a word template. This is exactly correct.

Is there a way to create a new table with the new wildcard values after the query? I understand a query can maybe do it, but i need a database to connect to with these updated values.

[CJ_London]

Is there a way to create a new table with the new wildcard values after the query?

probably but you need to answer my questions if you want a solution - to recap

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

[INeedAccessHelpPlease]

probably but you need to answer my questions if you want a solution - to recap

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

As long as we aren't updating the existing Data Table, the wildcards will always be surrounded by $ $ and match the wildcard table Wildcard column.
The wildcards are not always the same in the fields. There's a list of them on full wildcard table (not sent with the sample.mdb). Once they are defined though, they will/may not change. For instance, the LOC_original field can will have the same value for all cells in the column, but the rating1_original field can have any of the wildcards from the wildcard list in any of it's field. For instance, the first cell of rating1_original can have $bkrx1$ wildcard, but the 2nd field can have $bkrx2$ wildcard.
The number of items in the WC table is set and the only thing that will change is the values of each wildcard. How the values of the wildcards are populated & updated is another question. I'm not sure what the best approach to this is with access.