473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Trouble splitting data in text field

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
15 2142
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
@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
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
2,653 Expert 2GB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
Thanks guys works like a charm.
Apr 25 '09 #11
NeoPa
32,556 Expert Mod 16PB
No worries :)

Thanks for letting us know how you got on.
Apr 25 '09 #12
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
489 Expert 256MB
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

9
by: Penn Markham | last post by:
Hello all, I am writing a script where I need to use the system() function to call htpasswd. I can do this just fine on the command line...works great (see attached file, test.php). When my...
2
by: Gary Lynch | last post by:
I am looking for a simple solution to a recurrent problem with imported data in Access 97. The example below is a simplification of a problem with a much larger database. Let's say I start out...
20
by: Opettaja | last post by:
I am new to c# and I am currently trying to make a program to retrieve Battlefield 2 game stats from the gamespy servers. I have got it so I can retrieve the data but I do not know how to cut up...
1
by: Montana_Trader | last post by:
I have a product database that includes a memo field for product descriptions. That database must be imported into a legacy system that has four text fields for product descriptions, each with a...
1
by: Jim | last post by:
I have a new database in which I have a form where in one field I type a letter A, B, C or D and the field next to it autofills (auto lookups) with a description associated with the specific...
3
by: aaronvb | last post by:
Hi there, I'm currently trying to fix up a database that has had many different people work on it and therefore is confusing me no end. Currently i am trying to update a field, in the table ...
9
by: devranger | last post by:
I am using the below CURL Function and can not figure out why it is not retruning the results from the post. Can anyone take a look and tell me what I may be doing wrong? I am just not seeing...
7
by: Matik | last post by:
Hi to everyone, My problem is, that I'm not so quite sure, which way should I go. The user is inputing by second part application a long string (let's say 128 characters), which are separated...
3
by: mindlike | last post by:
I have a form "DataEntry" with various fields "Ticker", "TickerName", etc. I want to autofill the "TickerName" field when I insert the Ticker in the "Ticker" field. So when I put IBM into the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.