how to get relation links to match when they dont match

[Leslie Porter OHV]

<sigh>

ive inherited a database that is a little broken with my new job. =]

it is relational but its not. i could go into depth and detail but wont
unless its nessesary.

i have to tables one linedata the other log data.

log data is where the main information of the logs (tree's) are stored tag
number leng/diam cust and so on.

linedata is where clipping information is stored after the log is all cut
up. this is a hodge podge of machines and importing. whe it gets to the table
the tag field is soposed to be directly linked to the tag field in log data
but because of the customers there is sometimes an a b or c at the end thus
any queries i do dont link/match up. but i cant take the letters off the end
because the customer wants it that way. <ugh>

i guess the best idea that i could come up with is to add another field to
the linedata table create a query that copies the tag to it clearing any a b
c d e or f at the end (some times its that bad). but im not 100% sure how to
do this.

 

[Ron2006]

If the tag is a set lenth then

1) for existing information after the field has been added to the
table.
A) write update query that loads that field with
newfieldname =
left(oldfieldnamewithletter,lengthoftagfield)

2) for importing of new information...
A) basically the same thing - load it with the left hand portion
of the information for the length of the tag field.


Ron

 

[Leslie Porter OHV]

i thought of that but it is variing length and sometimes also has letter in
the begining so then it gets hard. i really need to be able to look at the
last character and see if its a number of a letter. if its a letter then
remove it. but i dont want to remove it from the original but copy from the
original field to a new field and check it for the letter in the proccess.

 

[John Spencer]

Two queries:

UPDATE YourTable
SET YourNewField = [YourOldField]

UPDATE YourTable
SET YourNewField = Left([YourOldField],Len(YourOldField)-1)
WHERE YourOldField LIKE "*[A-Z]"

Or one query:

UPDATE YourTable
SET YourNewField = IIF([YourOldField] like "*[A-z]"
,Left([YourOldField],Len(YourOldField)-1),[YourOldField])



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Leslie Porter OHV]

Absolutly Stunning!! Ide almost kiss ya =] except well your a dude =]

Two queries:

UPDATE YourTable
SET YourNewField = [YourOldField]

UPDATE YourTable
SET YourNewField = Left([YourOldField],Len(YourOldField)-1)
WHERE YourOldField LIKE "*[A-Z]"

Or one query:

UPDATE YourTable
SET YourNewField = IIF([YourOldField] like "*[A-z]"
,Left([YourOldField],Len(YourOldField)-1),[YourOldField])



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

<sigh>

ive inherited a database that is a little broken with my new job. =]

it is relational but its not. i could go into depth and detail but wont
unless its nessesary.

i have to tables one linedata the other log data.

log data is where the main information of the logs (tree's) are stored tag
number leng/diam cust and so on.

linedata is where clipping information is stored after the log is all cut
up. this is a hodge podge of machines and importing. whe it gets to the table
the tag field is soposed to be directly linked to the tag field in log data
but because of the customers there is sometimes an a b or c at the end thus
any queries i do dont link/match up. but i cant take the letters off the end
because the customer wants it that way. <ugh>

i guess the best idea that i could come up with is to add another field to
the linedata table create a query that copies the tag to it clearing any a b
c d e or f at the end (some times its that bad). but im not 100% sure how to
do this.