Three tables to link

[mohd21uk via AccessMonster.com]

I have three tables in Access where I am trying to link using a common field.
The common field in each table is provided below:

tbl_CN43 WBS Elem
tbl_CN42 Proj Def
tbl_CATS WBS code

The trouble is that tbl_CN43 and tbl_CATS have similar values that is the
values are prefixed with a two digit value, for e.g. GB2-E0005.02
but tbl_CN42 has values without out the prefix for e.g. GB2-E0005. Is there
any way we can link all three tables together.

 

[KARL DEWEY]

There are a couple of ways.
1 - You can build a query with added field to drop the SUFFIX and link the
query instead of the table.
2 - You can edit the join in SQL to join field dropping the SUFFIX. When
you do this it cannot be displayed in design view.

 

[mohd21uk via AccessMonster.com]

How can I remove the suffixes from the WBS codes ?

There are a couple of ways.
1 - You can build a query with added field to drop the SUFFIX and link the
query instead of the table.
2 - You can edit the join in SQL to join field dropping the SUFFIX. When
you do this it cannot be displayed in design view.

I have three tables in Access where I am trying to link using a common field.
The common field in each table is provided below:

[quoted text clipped - 7 lines]

but tbl_CN42 has values without out the prefix for e.g. GB2-E0005. Is there
any way we can link all three tables together.

 

[Klatuu]

You are descibing a suffix, not a prefix.
Carry the suffix in a separate field. If you need to work the two fields
together, create an index that includes both fields.

 

[KARL DEWEY]

You can do like Klatuu says and have the two elements in separate fields.
Create two new field and use an update query. If data is consistent the
Left([YourField],9) and the other Right([YourField],2) for the suffix.

For the query linking you also use the Left([YourField],9) for it.

It is better to normalize your data by doing what Klatuu said.

How can I remove the suffixes from the WBS codes ?

There are a couple of ways.
1 - You can build a query with added field to drop the SUFFIX and link the
query instead of the table.
2 - You can edit the join in SQL to join field dropping the SUFFIX. When
you do this it cannot be displayed in design view.

I have three tables in Access where I am trying to link using a common field.
The common field in each table is provided below:

[quoted text clipped - 7 lines]

but tbl_CN42 has values without out the prefix for e.g. GB2-E0005. Is there
any way we can link all three tables together.