By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

Trouble splitting data in text field

P: 15
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.
Apr 24 '09 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,709
You would probably want to use a subquery (See Subqueries in SQL).
Something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT S1.*,
  2.        T2.*
  3.  
  4. FROM   (SELECT Left([Field2],5) AS Stub,
  5.                *
  6.         FROM   Table1) AS S1
  7.        INNER JOIN Table2 AS T2
  8.   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.
Apr 25 '09 #2

P: 15
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT S1.*, 
  2.        T2.* 
  3.  
  4. FROM   (SELECT Left([machine name],5) AS Stub, 
  5.                * 
  6.         FROM   sheet1) AS S1 
  7.        INNER JOIN divregdistoff AS T2 
  8.   ON   S1.Stub=T2.office_gl_dep_id 
Apr 25 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
Do you have any example data that might help me get a better idea of some of the details that might need special consideration?
Apr 25 '09 #4

P: 15
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_id] 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_id] 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_id
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
Apr 25 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
@Kasghost
Yes there would, but I suspect a separate problem here.

Is [divregdistoff].[office_gl_dep_id] actually a numeric field?
Apr 25 '09 #6

P: 15
It started off that way and I was getting mismatch data type when running the query so I changed it to text.
Apr 25 '09 #7

FishVal
Expert 2.5K+
P: 2,653
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON Val(t1.[FieldName])=Val(t2.[FieldName]);
Apr 25 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT S1.*, 
  2.        T2.* 
  3.  
  4. FROM   (SELECT Val(Left([machine name],5)) AS Stub, 
  5.                * 
  6.         FROM   sheet1) AS S1 
  7.        INNER JOIN divregdistoff AS T2 
  8.   ON   S1.Stub=T2.office_gl_dep_id 
NB. The change is to line #4.
Apr 25 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
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_id] is converted back to numeric) :
Expand|Select|Wrap|Line Numbers
  1. SELECT S1.*, 
  2.        T2.* 
  3.  
  4. FROM   Sheet1 AS S1 INNER JOIN divregdistoff AS T2
  5.   ON   Val(Left(S1.[machine name],5))=T2.office_gl_dep_id
Apr 25 '09 #10

P: 15
Thanks guys works like a charm.
Apr 25 '09 #11

NeoPa
Expert Mod 15k+
P: 31,709
No worries :)

Thanks for letting us know how you got on.
Apr 25 '09 #12

P: 15
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
Expand|Select|Wrap|Line Numbers
  1. SELECT S1.*,  
  2.        T2.*  
  3.        T3.*
  4.  
  5. FROM fieldappstomachine AS S1 INNER JOIN seasonaldatelist AS T2 ON Val(Left(S1.[machine name],5))=T2.office_gl_dep_id
  6. FROM fieldappstomachine AS S1 INNER JOIN 3rdpartyapps AS T3 ON S1.application = T3.applicationName;
  7.  
  8.  
I just need this to run once so I would prefer not to have to migrate it to another program like MySQL .
Apr 29 '09 #13

DonRayner
Expert 100+
P: 489
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.
Apr 29 '09 #14

P: 15
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.
Apr 29 '09 #15

NeoPa
Expert Mod 15k+
P: 31,709
If you're still struggling after that, you can break up your database into multiple databases with a Front-End / Back-End (FE/BE) setup. The FE can have more than one BE if necessary. That way, your limit is only how much an individual table takes up.
Apr 30 '09 #16

Post your reply

Sign in to post your reply or Sign up for a free account.