Prev: Converting Access 2000 database to Access 2007
Next: je stinkt
From: Jon on 1 Apr 2010 09:22
Hi,
I have two tables as follows:
Table1
A--------------B
34145 D173abcd
333215 TIE242EFG
344233 EA0124ABCD

table2
A-----------B------------C
D-173 abcd ?
B-241 efgh ?
TI-242 XYZ ?
E-0124 STQ ?


What I want to do is taking the value from field A in table1 and put it in
field C in table 2 using table1 field B as an indicator.

Please note that the field A in table2 has the same data of field B in
table1 but there are extra letters and characters.


From: Al Campagna on 1 Apr 2010 09:54
Jon,
How can FieldB in table 2 be used as "an indicator?" There's not a
single
match between that and the B values in table1.
From what I can see from your examples, there is no logical association
that can be established between those two fields.

The only one that even comes close is the "D173abcd" vs. the "abcd"
abcd = Right(FieldB,4)
but all others Table2 B's would fail that association.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"Jon" <Jon(a)discussions.microsoft.com> wrote in message
news:4A36B668-A75B-4C9F-9FC8-27B215909A19(a)microsoft.com...
> Hi,
> I have two tables as follows:
> Table1
> A--------------B
> 34145 D173abcd
> 333215 TIE242EFG
> 344233 EA0124ABCD
>
> table2
> A-----------B------------C
> D-173 abcd ?
> B-241 efgh ?
> TI-242 XYZ ?
> E-0124 STQ ?
>
>
> What I want to do is taking the value from field A in table1 and put it in
> field C in table 2 using table1 field B as an indicator.
>
> Please note that the field A in table2 has the same data of field B in
> table1 but there are extra letters and characters.
>
>


From: Jerry Whittle on 1 Apr 2010 10:21
SELECT Table2.A, Table2.B, Table1.A AS C
FROM Table1, Table2
WHERE Table1.B Like "*" & [table2].[B] & "*";

Put in the proper table and field names.

BTW: You really shouldn't put the data into field C. Instead run a query
like this when you need to see this. An exception would be if this is a
one-time deal to populate the table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jon" wrote:

> Hi,
> I have two tables as follows:
> Table1
> A--------------B
> 34145 D173abcd
> 333215 TIE242EFG
> 344233 EA0124ABCD
>
> table2
> A-----------B------------C
> D-173 abcd ?
> B-241 efgh ?
> TI-242 XYZ ?
> E-0124 STQ ?
>
>
> What I want to do is taking the value from field A in table1 and put it in
> field C in table 2 using table1 field B as an indicator.
>
> Please note that the field A in table2 has the same data of field B in
> table1 but there are extra letters and characters.
>
>
 | 
Pages: 1
Prev: Converting Access 2000 database to Access 2007
Next: je stinkt