Concantenate in Table

[Guest]

Can a Field be made in a table that will concantenate two other fields in
that same table?

 

[Rick B]

Yes, but you would not need to store the concatenation in the table. That
would be redundant. Store the various items in the table, then perform the
concatenation in your queries, reports, and forms.

 

[Guest]

I want to run an unmatched query wizard comparing two concantenated items
against this table so would like to concantenate in this table as well. Can
you tell me how?

 

[Rick B]

You can do that in your query. Create the concatenation in your unmatched
query. Post the exact field information and perhaps someone can give you
the exact syntax of the unmatched query using your examples.

 

[John Vinson]

I want to run an unmatched query wizard comparing two concantenated items
against this table so would like to concantenate in this table as well. Can
you tell me how?

You don't NEED to concatenate in order to do this.

Create a Query with the two tables. Join the first field in one table
to the first field in the other, and likewise for the second field.
(You can do this with up to ten fields if needed).

Select each Join line and choose Option 2 - "Show all records in
<master table> and matching records in <other table>".

Put a criterion on either or both join fields of

IS NULL

This query will show all records in the first table which lack any
match on these two fields in the second table.

John W. Vinson[MVP]

 

[Larry Linson]

Can a Field be made in a table that will concantenate
two other fields in that same table?

You cannot define a Field in a Table to be a concatenation of anything --
that would be, in relational DB terms, trying to "commit spreadsheet". A
database is not just a bigger version of a spreadsheet. You can set the
value of a Field with a calculation in a Query, but all you store is the
result, and you'd have to rerun the Query if one of the Factor Fields were
changed.

As Rick has pointed out, that would be redundant storage of information. In
these days of inexpensive storage, the extra cost of the storage is not a
big deal, but the need for those recalculations certainly could be. My
experience is that it is not a matter of IF, but how soon the factors and
the result will not match and then you will have to determine which is
correct.

If you intend, as you say, to use a Query to compare a concatenation of two
Fields, you can, as easily, either compare the two Fields individually, or
include a calculated Field in the Query that concatenates them. The latter
is the "relational database way" -- don't store duplicated or redundant
data, and calculate values when you need them (even if the 'calculation' is
just a concatenation).

Larry Linson
Microsoft Access MVP