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.
15 2142 NeoPa 32,556
Expert Mod 16PB
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.
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 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?
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
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?
It started off that way and I was getting mismatch data type when running the query so I changed it to text.
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 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 : - 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 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) : - SELECT S1.*,
-
T2.*
-
-
FROM Sheet1 AS S1 INNER JOIN divregdistoff AS T2
-
ON Val(Left(S1.[machine name],5))=T2.office_gl_dep_id
Thanks guys works like a charm.
NeoPa 32,556
Expert Mod 16PB
No worries :)
Thanks for letting us know how you got on.
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 .
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.
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |