Calculated Field? Relationship?

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I have a table with a field that contains 12 character data. I would like to
link this table to a table containing only the first 4 characters of the
data.
Can I create a field in the first table that calculates only the first 4
characters of data and use it to link to the second table?
Do I have to perform the calculation in Excel before putting the data into
access?
 
Add your new field into your table and then create an update query on your
table with just the new field selected. In the 'update to' row put
Left([12charFieldName],4)
 
Heather said:
I have a table with a field that contains 12 character data. I would like
to
link this table to a table containing only the first 4 characters of the
data.
Can I create a field in the first table that calculates only the first 4
characters of data and use it to link to the second table?
Do I have to perform the calculation in Excel before putting the data into
access?
Click to expand...


You can join the tables in a query like so ...

SELECT tblOne.*, tblTwo.* FROM tblOne INNER JOIN tblTwo ON
Left(tblOne.OneText, 4) = Left(tblTwo.TwoText,4)

This kind of join can't be created or displayed in the graphical query
designer, you'll have to use SQL View. The database engine won't be able to
make use of any indexes that might exist on the fields involved in the join,
so it could be slow with large numbers of records.

Or you could use this alternative syntax, which can be displayed in the
graphical designer (but suffers from the same potential performance
impliations) ...

SELECT tblOne.*, tblTwo.*
FROM tblOne, tblTwo
WHERE (((Left([tblOne].[OneText],4))=Left([tblTwo].[TwoText],4)));
 
Use a select query with added calculated field - Link_Field:
Left([YourField], 4) and then use it to link with the other table.
 
in SQL Server, you can create a calculated column (in the table) that
would give you the first four letters of the other column.

I don't think that this feature is in Access, and I think that it's a
great shame-- it's one of those small things that I think would really
help MS Access.

For example; this allows me to do certain calculations in ONE PLACE
(in the table) and then I can reuse the computation in a hundred
different queries without rewriting the equation.

It's just an order of magnitude better structure (to use SQL Server
for this).
 
Back
Top