Linking of Text Field with Numeric Field

[CarrieR]

Hello-

I have an access table of shipments. I am trying to link the serial # (numeric field) of shipments from that table with an imported table from our legacy system. The problem is the serial # in the legacy table is a text field with not only the serial # but notes, etc. How could I link these 2 tables together? I need to show the ship date of a product by serial # if the serial number exists in the legacy table field. I am not familiar with writing SQL commands nor visual basic.

Your help is appreciated.

 

[mdthornton]

Is the text version of the serial number delimited in any way? Eg, by a space, "12345 some notes". Depending on the contents of the field you should be able to strip out the serial number and do a join. Give us some examples... Best Regards,
Mike

 

[CarrieR]

Sometimes the techs who enter the data use "S/N 012302934, otherwise, they are inconsistent, such as sn 1234.

 

[mdthornton]

Can you change the design of the legacy table? If you could add a new column for serial number you could run a few updates which would set the serial number for the common cases then you could manually set the remaining (more) unusual cases. Best Regards,
Mike

 

[CarrieR]

No, I cannot. This is a Unidata system, I can't even use an ODBC link at this point. My first thought also was let's create a new field to use for this purpose. Is there any way to strip out text to only link with numeric data in this field?

 

[starman0724]

I was reading, hoping to learn something myself. I have an Access database which imported ODBC data into 2 tables. One table is "Area" with and AreaID (2 digits) and the other table is "Equipment" with EquipmentID (10 digits). I need to "merge"? the data into one FIELD, such that the areaID digits preceed the equipmentID digits for a total of 12 characters.

Every time I try the join commands in SQL for a query...well, I've read the help and I get nowhere. Does it have to be so difficult? Or am I just missing something basic?

(later, I need to format the 12 numbers into 3 groups of 4 separated by hyphens)

Ken

 

[mdthornton]

CarrieR

Can you just create a new table alongside the legacy one. Is the data changing much/at all?

Without a pattern in the data it will be very hard to join correctly on this column. There are things you could do but you would not be able to be sure of a complete join. A lot depends on your data and how complete things have to be....

Starman - you're easy... assuming you have a way to join (if necessary) between Area & Equipment, qMerge is:

select Cstr(AreaID)+Cstr(EquipID) as MergeID, etc
from Area, Equipment
etc

then for split,

select Mid(MergeId, 1, 4)+"-"+Mid(mergeID,5,4)+"-"+Mid(MergeId,9,4) as SplitMergeID, etc
from qMerge



Best Regards,
Mike

 

[starman0724]

Mike, how do you read this language in your brain. After several minutes, this is what I came up with on a new SQL query:

SELECT Cstr(AreaID)+Cstr(EquipmentID) AS MP2
FROM Area, Equipment

SELECT Mid(MP2, 1, 4)+"-"+Mid(MP2, 5, 4)+"-"+Mid(MP2, 9, 4) as MP2n
FROM qMerge;

I ran the first half...it works (with one problem, evidently the source external DB is set up for 4 characters, so even though I only use 2, I get 2 spaces stuck between my 2nd and 3rd characters!!!!).
I get an error on the "FROM" statement....I must've missed an important piece of information, yes?

Ken

 

[mdthornton]

The trim functions below should tidy up the area id:

SELECT Rtrim(Ltrim(Cstr(AreaID)))+Cstr(EquipmentID) AS MP2
FROM Area, Equipment

If you save this query as qMerge then to split you would do:

SELECT Mid(MP2, 1, 4)+"-"+Mid(MP2, 5, 4)+"-"+Mid(MP2, 9, 4) as MP2
FROM qMerge;

If you want to, you could do this in one go but it gets a bit messy:

SELECT Mid(Rtrim(Ltrim(Cstr(AreaID)))+Cstr(EquipmentID), 1, 4)+"-"+Mid(Rtrim(Ltrim(Cstr(AreaID)))+Cstr(EquipmentID), 1, 4)+"-"+Mid(Rtrim(Ltrim(Cstr(AreaID)))+Cstr(EquipmentID), 1, 4) AS SplitID
FROM Area, Equipment

ps sorry CarrieR - your thread has been hijacked... Best Regards,
Mike

 

[starman0724]

I thought the problem was licked. I've got two tables from an external source via ODBC drivers, right?
Table 1: Area has "AkeaKey" and "AreaID" -- AreaKey is a Primary Key
Table 2: Equipment has "AreaKey" and "EquipmentID" -- AreaKey is a Primary Key

AreaID is a text field of 4 characters...of which I need only those records with numeric values, and I need only the first 2 characters (no spaces afterwards).
EquipmentID is a text field of 10 characters...I will use all 10 characters (numbers).
I need to "merge"? the data into one FIELD, such that the areaID digits preceed the equipmentID digits for a total of 12 characters, with an output format of 1234-1234-1234.

Examples from the two tables are below:
AreaKey AreaID AreaKey EquiptKey EquipmentID

-2832 SAWS -2832 -2833 1800BD2RPM
-2832 SAWS -2832 -2863 1200BD2RPM
-31919 30 -31919 -32100 7501130700
-31919 30 -31919 -32160 7501171000

I'd like to run this query / queries such that those records with alpha characters in the AreaID are not used in the final output (they are information provided by the software vendor for troubleshooting). So far I'm using 2 make table queries, so that the final product (table 4)can be easily acessed via excel.

The problem I have is this:
one - when I run the first solution provided as a solo query (merging the two fields, complete with spaces), my records jump from 414 records to over 10,000 records!!!
two - when I run the final solution provided as a solo query (merging the two fields, with the last two characters trimmed off), causes my new 12 character equipment ID (with hyphens) to be changed...here is a sample:

3075-3075-3075
3075-3075-3075
3165-3165-3165
3165-3165-3165

You can see that the numbers are identical in groups? why does it do this?
Here's the query as I'm using it:
SELECT Mid(RTrim(LTrim(CStr(AreaID)))+CStr(EquipmentID),1,4)+"-"+Mid(RTrim(LTrim(CStr(AreaID)))+CStr(EquipmentID),1,4)+"-"+Mid(RTrim(LTrim(CStr(AreaID)))+CStr(EquipmentID),1,4) AS SplitID, RBM_Eqpt.Description, RBM_Eqpt.LastTrend, RBM_Eqpt.EquipAlarmStatusCode INTO MP2_Vib
FROM RBM_Eqpt;

Any help you can give me is greatly appreciated...otherwise, I'm gonna have to read this "Complete Reference SQL" book on my desk.

Ken

 

[mdthornton]

Without seeing the query I would say problem 1 is caused by a missing join. Are you joining on the AreaKey field between the two tables? If so how?

The second problem is because you have mistranscribed my code. You need to set the start & length parameters in the mid function for the second and third sections to 5,4 & 9,4 respectively. Best Regards,
Mike

 

[starman0724]

Mike...I don't know about the issue with the missing join....those seem to be beyond me as well. However, the first Make table query I run gives me the correct number of records, and then I run the corrected mid function on that table...it works good that way.

Thanks!

Ken

PS - I've still got work to do, but this is one major step closer to acomplishment.