Re: How to trim a record when using DB_OPEN_TABLE

"GHawkins" <GHawkins@xxxxxxxxxxxxxxx> wrote in message news:F85F73DD-42E4-48EC-8CA5-A02E7DD16AD7@xxxxxxxxxxxxxxxx
I'm working with an Access database (Access 2007). TableA is my main table
with data, and TableB contains some items that need to be updated in TableA.
The tables are set up as follows:

TableA:
EmplID (primary key)
QtrDate (primary key)
Code

TableB:
EmplID (primary key)
ChangeDate
Code

Right now, I am taking the first record in TableB, searching TableA for a
match, then comparing the dates. When TableA.ID = TableB.EmplID and
TableA.Date = TableB.ChangeDate, I update TableA.Code with the value in
TableB. This all works fine, but recently we've run into a problem where
there are extra spaces at the end of some data in the TableA.EmplID. Employee
1234 might show up as '1234' in some records, but as '1234 ' in others. When
there are extra spaces in TableA.ID, it is not seen as a match when I compare
it to TableB, so the Code is not updated. Is there a way to Trim the ID in
TableA before comparing it to TableB?

It seems to me that you should first address the issue that you have records in TableA that are supposed to represent the same entity, but that have different keys; e.g., '1234' vs. '1234 '. If that extra space is meaningful, these don't represent the same entity, but what you're trying to do suggests that '1234' is the *correct* key for both records, and '1234 ' is an error. It would be unusual for trailing spaces to be significant in a text field, especially if that field is a key. Is there somewhere a TableC whose primary key is EmplID, that contains the "master" data for these records? If so, is there a record in that table for both '1234' and '1234 '?

You can remove all the existing leading and trailing spaces in the key field by executing an update query with SQL like this:

UPDATE TableA SET EmplID = Trim(EmplID)

If you only need to remove the trailing spaces and need to leave leading spaces intact, you can use RTrim() instead of Trim().

One possible issue that could arise with that update is that you might conceivable end up with a key violation, if the update would result in two records wih the same primary key. For example, if you had these records:

EmplID='1234', QtrDate = #9/4/2008#
EmplID='1234 ', QtrDate = #9/4/2008#

.... the update would result in these records:

EmplID='1234', QtrDate = #9/4/2008#
EmplID='1234', QtrDate = #9/4/2008#

.... and that would not be allowed by the primary key constraint. However, if '1234' and '1234 ' really represent the same entity, then that shouldn't happen.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

.

Relevant Pages

  • Re: sql minus
    ... What do you do in the case of a composite primary key? ... FROM TableA LEFT JOIN TableB ... SELECT * FROM TableA MINUS SELECT * FROM TableB; ...
    (microsoft.public.access.queries)
  • Re: Subselect Query Problem
    ... tableA LEFT JOIN tableB ... the result (of the inner join), it is logically re-introduced into the ... result, but since nothing match in tableB, whatever comes from tableB is ... > is Table B that has the first three fields as the primary key, ...
    (microsoft.public.access.queries)
  • Re: How to trim a record when using DB_OPEN_TABLE
    ... QtrDate (primary key) ... Right now, I am taking the first record in TableB, searching TableA for a ... there are extra spaces at the end of some data in the TableA.EmplID. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Which column is taken in JOIN with multiple columns and different content ?
    ... This column ist not he primary key. ... FROM tablea a, tableb b ... Which values will be for the questions marks? ...
    (comp.databases.oracle.misc)
  • Re: Cursors (again)
    ... What does it matter if TableB is temporary? ... or tableA. ... open cTableB ...
    (microsoft.public.sqlserver.programming)
Quantcast