Emp no stored as text...need it to be numeric

[sjdk]

Good morning, and thanks in advance for any assistance...

I have a linked table in SQL (tblSupervisor) that contains an employee number and a supervisor name. I want to join that table with an access table (tblEmpview) that contains employee number and name. Simple...right? Here's the problem...in my SQL table (tblSupervisor), the employee number is stored as TEXT and in the access table (tblEmpview) the employee number is stored as a number. (I didn't create the SQL table...it comes from a vendor...why would anyone store a number as text?!?) So the problem is...I can't join the 2 tables and come up with an Employee name and a supervisor name. Any suggestions??

Thanks!!

 

[SkipVought]

Hi,

...why would anyone store a number as text?!?)

WHAT?!

Are you going to ADD a numeric value to an Employee ID???

Are you going to MULTIPLY a numeric value with an Employee ID?

Are you going to SUBTRACT a numeric value from an Employee ID??

Are you going to DIVIDE a numeric value into an Employee ID???

Are you going to raise your Employee ID to a POWER?

Are you going to apply DIFFERNTIALS to your Employee ID???


My question is, "Why would any thinking person, store a numeric IDENTIFIER as a NUMBER, when it clearly has absolutely no numeric significance?"

Change your MS Access DB to meet reality.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

faq68-6659

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sjdk]

Hi Skip...
I get what you are saying (LOUD and CLEAR!)....however....I didn't design or create the database...I inherited it. Right now changing the database isn't an option. I guess I am just looking for a work-around until making a change is an option.

Thanks!

 

[SkipVought]

Your choice is to change the vendor's data structure or change your data structure.

What's the better chance?

But if you need to crutch this, blechlk, convert the column in a query and then join.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Or...

From SqlSvr, MsAcc
Where SqlSvr.EmpID = Format(MsAcc.EmpID, "00000000")

assuming that the EmpId in the SQL table is EIGHT characters in length.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sjdk]

Ok...got a temporary solution...I converted it in a query and then joined. It 'aint pretty, and it 'aint right...but it worked until changes can be made. Thank you for your time and wisdom!

 

[JoeAtWork]

Actually Skip, I can't agree with your assertion that identifiers should always be text. The purpose of an identifier is to provide a unique value, and a number can do this quite as well as a text value, and almost certainly more efficiently as well. There's a reason why SQL Server uses an Int for Identity columns. An index on a numeric column will be more efficient than a text column.

A number field is not necessarily something you want to do arithmetic operations on. For example, if I have a field that is for designating a sort order in a list, that's not something I would ever concieve of adding, dividing, multiplying, etc., yet it is still a numeric field (otherwise if I made it text it would sort so that 11 comes before 2, which is not what I want).

As for the OP's original question, I would simply have done what he eventually came up with, which is to use a query to convert the field. This is a common solution for when you need to combine data from diverse sources.

 

[alvechurchdata]

I find myself agreeing with both Joe and Skip. An integer makes a very efficient unique identifier and that's what I'd use given a free hand. The problem is that the original system was storing employee numbers. I'd always choose an integer as a primary key if there was no real-world meaning to it but I'd use a string for anything like employee "numbers", serial "numbers" or order "numbers". These may well be numeric at the time of asking but it's not unusual for requirements to change and for someone to want to add an "A" or a "/1" as a suffix.


Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lespaul]

additionally when you are storing something like a Social Security number that has leading zeros you lose those if SSN is a number instead of text.

Leslie

 

[SkipVought]

Joe,

Not referring to INTERNAL keys.

Referring strictly to USER GENERATED VALUES. And I'll go to the wall on this one.

OFTEN, in more than one case, I have seen 1970's flavor db's having to be converted, cuz NO ONE at the time ever thot that the 6 to 8 digit NUMBER would ever be exceeded.

So now serial "numbers" that were indeed all numeric BUT had character lenght significance, NOW have ALPHA characters to expand the sequencing capacity. So NOW, at last, we ave been FORCED into a string data type.

Shuda been there all along, IMHO

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alvechurchdata]

OFTEN, in more than one case, I have seen 1970's flavor db's having to be converted, cuz NO ONE at the time ever thot that the 6 to 8 digit NUMBER would ever be exceeded.

We can't really blame that one on numbers though. I've just finished upgrading a 6-character serial string to 10 characters because twenty years ago nobody dreamt they'd ever sell a million items.

And perhaps in another twenty years someone will be saying much the same about me "Why did they pick ten characters?"

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Golom]

Actually you can join them ... just do a conversion

Select ...

From tblSupervisor As S INNER JOIN tblEmpview As E
     ON [red]Val(S.EmpID) = E.EmpID[/red]

The comments that others have made are on the mark.

If you ain't doin' math then it ain't a number.