Trouble splitting data in text field

[Kasghost]

Rarely do I play with access so please keep it simple if possible.

What I have are 2 tables. In table1 I have two fields, both are text. Field1 lists application names (Access, Excel, IE 7, ect). Field2 lists computer names (12345-ws-00). What I need is to either return just the first 5 digits in another field or in another table. Table 2 has 6 fields but uses the 5 digits I need from Table1 as the primary key and I need to end up with a 5 field table with most of table 2 and Field2 from Table1.

Normally I would just do all this in Excel but with over 16 million records Excel just does not want to open.

Any help would be greatly appreciated.

[NeoPa]

You would probably want to use a subquery (See Subqueries in SQL).
Something like :

SELECT S1.*,
       T2.*

FROM   (SELECT Left([Field2],5) AS Stub,
               *
        FROM   Table1) AS S1
       INNER JOIN Table2 AS T2
  ON   S1.Stub=T2.FieldN

PS. I'm really not sure exactly what your field and table names are so you may need to make some changes, but the concept should work.

[Kasghost]

Thanks for the help. When I run the query I just get a blank sheet, though I do have all the fields that I was hoping for.

Here is the code from the SQL statement that you suggested with the names of the actual sheets and fields that I ran it on.

SELECT S1.*, 
       T2.* 
  
FROM   (SELECT Left([machine name],5) AS Stub, 
               * 
        FROM   sheet1) AS S1 
       INNER JOIN divregdistoff AS T2 
  ON   S1.Stub=T2.office_gl_dep_id

[NeoPa]

Do you have any example data that might help me get a better idea of some of the details that might need special consideration?

[Kasghost]

Just figured out what the problem is. Kind of. The field [machine name] has 5 characters that I am taking out. The problem is in the field [office_gl_dep_i d] I have some records with 4 characters and some with 5. It looks like all of the id's with 4 characters in [office_gl_dep_i d] start with a 0 when coming from [machine name]. Would there be some way to drop the 0 from these?

Here are a few examples of the records.

table: divregdistoff
office_gl_dep_i d
1000
1008
11121
11122
11123
11124
11125




table: sheet1
application machine name
Acroaum________ _____________ 01010-WS-01
Adobe Acrobat________ _________01010-WS-01
Adobe Acrobat________ _________01010-WS-02
Adobe Reader_________ ________01010-WS-01
Adobe Reader_________ ________ 01010-WS-02
Adobe Update Manager________ __01010-WS-01

[NeoPa]

Would there be some way to drop the 0 from these?

Yes there would, but I suspect a separate problem here.

Is [divregdistoff].[office_gl_dep_i d] actually a numeric field?

[Kasghost]

It started off that way and I was getting mismatch data type when running the query so I changed it to text.

[FishVal]

You may use feature of Val() function to convert leading numeric portion of string.
e.g.
Val("01010-WS-01") gives 1010

so, the query could be like the following:

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON Val(t1.[FieldName])=Val(t2.[FieldName]);

[NeoPa]

It needn't be a text field, but if it is, it would make better sense to format the data as a 5-digit string anyway (rather than simply the standard display of however many digits are there). That way the original SQL would work fine for you.

As it's essentially a numeric value though, you can change the field back to numeric and use this slightly changed SQL :

SELECT S1.*, 
       T2.* 
  
FROM   (SELECT Val(Left([machine name],5)) AS Stub, 
               * 
        FROM   sheet1) AS S1 
       INNER JOIN divregdistoff AS T2 
  ON   S1.Stub=T2.office_gl_dep_id

NB. The change is to line #4.

[NeoPa]

Fish makes a good point. The subquery is not required.

Also, Val() can be used that way (It will essentially stop processing the string once it finds a character that isn't convertible). I would tend to avoid that myself, as it can be quite confusing to understand when amending code later, but even then, the use of Val(Left("...", 5)) can still be used, and the SQL is pretty well as simple as Fish's version.

The SQL would then be (if the [office_gl_dep_i d] is converted back to numeric) :

SELECT S1.*, 
       T2.* 

FROM   Sheet1 AS S1 INNER JOIN divregdistoff AS T2
  ON   Val(Left(S1.[machine name],5))=T2.office_gl_dep_id

[Kasghost]

Thanks guys works like a charm.

[NeoPa]

No worries :)

Thanks for letting us know how you got on.

[Kasghost]

Ok i've run into another snag with this. I was given another table and need to run a query against the table and the last query. The issue I have is that with just the 3 tables the DB is about 1.30GB. I have tried using the queries to create tables in both this DB and another but in both instances I hit the 2GB limit before the tables are created. I have also tried modifying the SQL to try and account for the third table but keep getting syntax errors (I know nothing about SQL commands so I just copied the code and tried to modify it to what I thought might work).

Here is what I tried

SELECT S1.*,  
       T2.*  
       T3.*

FROM fieldappstomachine AS S1 INNER JOIN seasonaldatelist AS T2 ON Val(Left(S1.[machine name],5))=T2.office_gl_dep_id
FROM fieldappstomachine AS S1 INNER JOIN 3rdpartyapps AS T3 ON S1.application = T3.applicationName;

I just need this to run once so I would prefer not to have to migrate it to another program like MySQL .

[DonRayner]

Try performing a compact and repair on the database. This should reduce it's size somewhat. How much I can't say as that will depend on how many records have been deleted out of the database over time.

[Kasghost]

Did this when I converted from 2000 to 2007 format and was able to get an update query to run so that i could weed out some data that I don't need. I'm guessing that I will need to run compact after the delete finishes. Hopefully this will bring it down to a managable size.