Setting up relationships

[storm warden]

I am experiencing problems linking twow tables togeathar. One one table i
have IMIE which is a 15 digit number, on the other is IMIEprefiex which is
the 1st 6 digits of an IMEI.

 

[Tim Ferguson]

I am experiencing problems linking twow tables togeathar. One one
table i have IMIE which is a 15 digit number, on the other is
IMIEprefiex which is the 1st 6 digits of an IMEI.

Not quite sure what you want.

You cannot enforce integrity between these two tables: the best way is to
split the IMIE into Prefix and Suffix columns.

You can probably create a join between them like this:

SELECT ....
FROM Table1 SOME JOIN Table2
ON LEFT(Table1.IMIE,6) = Table2.IMIEPrefix
WHERE ....


but I have not actually tried this!

The purist's answer is that if IMIEPrefix is an atomic datum, then IMIE
is (at least) two atoms, and should be stored in two columns anyway. I'd
go for splitting it anyway, because it makes everything else so much
easier.

Hope that helps


Tim F

 

[John Vinson]

On Fri, 4 Feb 2005 06:15:03 -0800, storm warden <storm

I am experiencing problems linking twow tables togeathar. One one table i
have IMIE which is a 15 digit number, on the other is IMIEprefiex which is
the 1st 6 digits of an IMEI.

I hope IMIE is a Text field type, not a Number! None of the standard
numeric field types will reliably store 15 digits; and if you're not
doing math with the field, a Text datatype is preferable.

And if the IMIE field contains two distinct values with separate uses,
as appears to be the case, you should certainly consider storing the
two portions in two different fields. This will let you link on the
Prefix field; you can combine the IMIEPrefix and IMIESuffix fields for
display purposes much more easily than you can link the tables.

You can create a *query* joining IMIEPrefix to Left([IMIE], 6) but
since this portion of the field cannot be indexed, you will not be
able to enforce referential integrity or update the query.

John W. Vinson[MVP]