Concatenating indexed list (returns index instead of data)

  • Thread starter Thread starter Neuro
  • Start date Start date
N

Neuro

I am using a query to concatenate two columns from the same table. All of
the data in the latter column is drawn from a separate table to ensure data
entry fidelity (indexed-duplicates OK). In the query, I am using the
NewColumn:[ColumnA]&""&[ColumnB] coding to build the concatenation. However,
the query returns the index (primary key) of the table that ColumnB is drawn
from instead of returning the actual data. This is strange because if I just
say NewColumn:[Column2] the query recognizes the data. I'm stumped.

For example, if I were to try to concatenate the words Dog and "House", it
wold show up as Dog3.

Thank you so much for your advice!
 
Thank you for getting back to me so quickly, but I don't understand your
response in the context of my problem.

The table that holds the actual text values only contains three records,
while the table that it relates to has hundreds. How can I include that
original 3-record table in the query if the text it contains is only
meaningful if looked-up by the larger table and concatenated with another
column in that larger table? How can I get around using the combobox/lookup
fields in this situation, which are so dreaded? Should I not have that
smaller table and somehow store these three options in the larger table? If
so, how can I do this in such a way that I'll maintain data integrity during
data entry and have the option of increasing the number of the options in the
future?

Thanks again!

Duane Hookom said:
Apparently you have used the dreaded lookup fields in your table design. You
might want to review http://www.mvps.org/access/lookupfields.htm.

If you want to display an actual text value, you need to include the
table/field containing the actual text value.

--
Duane Hookom
Microsoft Access MVP


Neuro said:
I am using a query to concatenate two columns from the same table. All of
the data in the latter column is drawn from a separate table to ensure data
entry fidelity (indexed-duplicates OK). In the query, I am using the
NewColumn:[ColumnA]&""&[ColumnB] coding to build the concatenation. However,
the query returns the index (primary key) of the table that ColumnB is drawn
from instead of returning the actual data. This is strange because if I just
say NewColumn:[Column2] the query recognizes the data. I'm stumped.

For example, if I were to try to concatenate the words Dog and "House", it
wold show up as Dog3.

Thank you so much for your advice!
Click to expand...
Click to expand...
 
If I understand correctly, you have a situation where you are storing a
"code" value in a larger table and the "code" and "description" in a smaller
table. For instance you might have a table of employees with a code for status

tblStatus
===========
StatusCode StatusDescr
1 Full-Time
2 Part-Time
3 Temporary
4 Terminated

tblEmployee
=====================
EmpID StatusCode
123 1
214 1
333 2
315 4
843 3
922 2

You would use a combo box on your employee editing form to allow your users
to select the Status and store the code while displaying the description.

You want a query to actually display the StatusDescr so you must include the
tblStatus table in your query and join the StatusCode fields. You can then
use StatusDescr in any column expression in the query.
--
Duane Hookom
Microsoft Access MVP


Neuro said:
Thank you for getting back to me so quickly, but I don't understand your
response in the context of my problem.

The table that holds the actual text values only contains three records,
while the table that it relates to has hundreds. How can I include that
original 3-record table in the query if the text it contains is only
meaningful if looked-up by the larger table and concatenated with another
column in that larger table? How can I get around using the combobox/lookup
fields in this situation, which are so dreaded? Should I not have that
smaller table and somehow store these three options in the larger table? If
so, how can I do this in such a way that I'll maintain data integrity during
data entry and have the option of increasing the number of the options in the
future?

Thanks again!

Duane Hookom said:
Apparently you have used the dreaded lookup fields in your table design. You
might want to review http://www.mvps.org/access/lookupfields.htm.

If you want to display an actual text value, you need to include the
table/field containing the actual text value.

--
Duane Hookom
Microsoft Access MVP


Neuro said:
I am using a query to concatenate two columns from the same table. All of
the data in the latter column is drawn from a separate table to ensure data
entry fidelity (indexed-duplicates OK). In the query, I am using the
NewColumn:[ColumnA]&""&[ColumnB] coding to build the concatenation. However,
the query returns the index (primary key) of the table that ColumnB is drawn
from instead of returning the actual data. This is strange because if I just
say NewColumn:[Column2] the query recognizes the data. I'm stumped.

For example, if I were to try to concatenate the words Dog and "House", it
wold show up as Dog3.

Thank you so much for your advice!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top