problem

[Thedon123]

i have to sets of table with primary kaeys as follows
45366rf2 for table 1 and 45366rf2e for table 2.

They are both linked by these keys.

Problem.

when i create a query to search for something in table 1 using the primary key of table 2 it does not work because of the extra letter at the end.

Question

How would get rid of the extra letter or go around it.

 

[The_Doc_Man]

You appear to have a situation in which your primary keys are not "atomic" in nature. That is, a part of one key corresponds to the entirety of another key. If the longer key field isn't elementary, that would lead to the problem you describe. If the extra letter in the longer key is functional (i.e. last letter of 'e' means one thing and 'f' would mean something else) then you have to split that key into two fields. Put the last letter in a separate field by itself.

Access works best only when all fields are "atomic" in meaning. (In this case, the old Greek meaning of "atomic" = indivisible.) You can still, if you wish, make the COMBINATION of two fields as the table's primary key. Multi-field keys (up to 10 fields in any one index, totalling no more than 255 bytes per combined key, if I recall correctly) are permitted.

In that case, you would then be able to link directly between the two entries as you wished. But let me also ask this: If you saw two entries in table 2 and their (compound) keys were 45366rf2e and 45366rf2f, would they BOTH map to table 1's entry with key 45366rf2? In which case, you would have a one-to-many relationships between the tables with your Table2 on the 'many' side of the picture.

If the extra letter ISN'T functional and the field really IS atomic, then my first question is, how do you know that the two records are related? My second question is still, do you really need the extra letter?

You will probably have to drive this with a query based on a "like" operator using the shorter key as the driving force. In the WHERE clause, you are looking at

... WHERE [Table2].[PrimeKey] LIKE "*" & [Table1].[PrimeKey] & "*" ...

Don't forget that if you have to build a string like this because you are generating SQL programatically, your quotes have to be doubled inside the string.

... "WHERE [Table2].[PrimeKey] LIKE ""*" & [Table1].[PrimeKey] & "*"" ...

 

[Thedon123]

To your question do the end letters mean anything. Yes they do they are multiple instances of the value.

The problem is i have a set of values atleast 2000 with these instances and the only thing the links trhge two tables is the primary key values.

All i want to do is to get rid of the klast letter.

cheers

 

[The_Doc_Man]

I'll answer you twice. Once as a band-aid, once as a correct method. The situation you have is

Table 1 has
-- PK1
-- other data

Table 2 has
-- PK2
-- other data

AND PK1 is a formal subset string of PK2. Further, PK2 is created by concatenation of a letter to a key from PK1.

EITHER SOLUTION presumes that you NEVER, EVER wrote PK2 with a "blank" for the first case where PK2 was derived from PK1.

OK, first way:

You need a query like this...

SELECT ..... FROM Table1, Table2 WHERE
[Table1].[PK1] = Left$( [Table2].[PK1], Len( [Table2].[PK2] - 1 ) AND .... (any other criteria as appropriate)

Problems: (1) You had better not forget to concatenate the letter to PK2 even if there is only one entry. (2) You had better not get too many variants with the same subset key in table2.

Now, the better way. Better, but not necessarily prettier to retrofit. But FAR better down the road.
Make a backup of your database just in case this hoses up.

In table2, go into design view.
Take the "Primary Key" designation off of PK2. Set the field to be No Index.
Add a text field next to PK2. Could be a single character. Set the field to be No Index.
Now write an update query to deposit Right$( PK2, 1 ) into the new field. Execute the update. Don't take the next step until this step runs to a successful completion.
Now write an update query to change PK2 to Left$( PK2, Len(PK2) - 1 ). Execute it.

Click on both the PK2 field and the new field (which should, for convenience sake, be adjacent). Now re-establish the pair of fields as a compound primary key. You should also be able to establish a separate non-prime index on the original PK2 field, one that allows duplicates.

From that point forward, instead of appending a letter to PK1 to form a new PK2, just copy PK1 into PK2 and store that discriminator letter in the new field.

OK, now you can DIRECTLY relate Table1 to Table2. You can do a formal relationship between the two tables. Which means that JOIN operations will work automatically.

The catch is, if anything depended on the original format of PK2, it now must depend on the concatenation of the NEW PK2 & {the new discriminator field}

If the assumption stated before these solutions is NOT met (i.e. sometimes PK2 uses a blank as a discriminator), you have already screwed yourself royally. I don't know of any way to recover from that situation.

 

[Thedon123]

Cheers mate problem solved