The data I am using was imported from Excel. The tables were then merged into one large table (3 million + records) and so cannot be re-exported into excel for modification.
I am trying to map the transactional data back to a list of account numbers I have. (By matching the fields branch to Neab (Branch), and customer number (Pwanx) to Customer Number (Nean).
This works fine but as soon as I add the suffix (field Pwasx), I get (Error 3049).
It works with the suffix field when doing a select statement. But when I do a make table, I get the error message.
Also, I tried exporting the results of the Select statement and then re-importing them but I get an error message.
the Suffix field is saved as text as are all other fields with the exception of the Amount fields.
The last (very time consuming) solution I have is to try to import from Excel, recombine, and try again after modifying the suffix field (I am not sure this will resolve it though and am hoping there is a more direct solution).
Here's the SQL: - SELECT [All Transactions].[Customer Name], [All Transactions].Branch, [Branch and nean combos].[acca good], [All Transactions].Currency, [All Transactions].Amount, [All Transactions].[Amount USD], [All Transactions].[Depositor Name], [All Transactions].[D TYPE], [All Transactions].[W TYPE], [All Transactions].RATE, [All Transactions].DATE, [All Transactions].SEGMENT, [All Transactions].[TRANS CONT], [All Transactions].Reference, [All Transactions].[BENEFICIARY/ORIGINATOR], [All Transactions].[TYPEOFTRANSACTION(REPORT NAME)], [All Transactions].Purpose, [All Transactions].BBI
-
FROM [Branch and nean combos] INNER JOIN [All Transactions] ON ([Branch and nean combos].Neab = [All Transactions].Branch) AND ([Branch and nean combos].Nean = [All Transactions].Pwanx);
-
I could also try to write some VB script to copy the records one by one from the Select query results into a new table but I'm not entirely sure how to do that.
Thanks
3 3328
The data I am using was imported from Excel. The tables were then merged into one large table (3 million + records) and so cannot be re-exported into excel for modification.
I am trying to map the transactional data back to a list of account numbers I have. (By matching the fields branch to Neab (Branch), and customer number (Pwanx) to Customer Number (Nean).
This works fine but as soon as I add the suffix (field Pwasx), I get (Error 3049).
It works with the suffix field when doing a select statement. But when I do a make table, I get the error message.
Also, I tried exporting the results of the Select statement and then re-importing them but I get an error message.
the Suffix field is saved as text as are all other fields with the exception of the Amount fields.
The last (very time consuming) solution I have is to try to import from Excel, recombine, and try again after modifying the suffix field (I am not sure this will resolve it though and am hoping there is a more direct solution).
Here's the SQL: - SELECT [All Transactions].[Customer Name], [All Transactions].Branch, [Branch and nean combos].[acca good], [All Transactions].Currency, [All Transactions].Amount, [All Transactions].[Amount USD], [All Transactions].[Depositor Name], [All Transactions].[D TYPE], [All Transactions].[W TYPE], [All Transactions].RATE, [All Transactions].DATE, [All Transactions].SEGMENT, [All Transactions].[TRANS CONT], [All Transactions].Reference, [All Transactions].[BENEFICIARY/ORIGINATOR], [All Transactions].[TYPEOFTRANSACTION(REPORT NAME)], [All Transactions].Purpose, [All Transactions].BBI
-
FROM [Branch and nean combos] INNER JOIN [All Transactions] ON ([Branch and nean combos].Neab = [All Transactions].Branch) AND ([Branch and nean combos].Nean = [All Transactions].Pwanx);
-
I could also try to write some VB script to copy the records one by one from the Select query results into a new table but I'm not entirely sure how to do that.
Thanks
Are [Branch and nean combos].Nean and [All Transactions].Pwanx both of the same data type?
When working with such large data sets as you are importing try a different approach of appending to a table instead of MakeTable (you will probably find this to be faster). Take a small sample of the import data and use it initially to make a table. Once table is created open it up in design view to verify data types and sizes. You would then create a query or SQL to delete all data from this table and then append your import.
Excel is notorius for changing your data types - it looks at the first row and assumes the rest of the column is of the same data type. It may designate a column as text this time and numeric the next.
Some use Excel to mine data from SAP, JDEdwards, etc. Whether or not this is the case, I would save the speadsheet as a delimited text file, create an import specification, and import the text file. The import specification will give you more control over field names and data types.
Jax,
thanks for the quick response.
All of the fields in all tables are of the same data type (text) with the exception of (Amount) and (Amount USD). All the data was set properly in excel (I emphasize.. IN excel, not By excel).
As for the speed, work was nice enough to provide me with a super fast PC (with 4GB RAM) for just such tasks.
All showing off aside though, I tried the approach you suggested. The append doesn't work.
I also tried exporting the Select results as Tab delimited and re-importing them but to no avail.
Not quite sure what's up with it but I really have tried almost everything i can think of.
The only thing left is to try and re-import (after triple checking the data in Excel) and then try it again.
What really bugs me is that Select works, but not make table.
Very very strange.
Hope this information helps you help me.
Thanks again
Jax,
FYI - I went ahead and re-imported the data from Excel. It seems that there was one table (out of three) where the suffix was not properly formatted.
It works fine now but I still need to occasionally shut Access down and start it up again for queries to work. Not sure why that is.
Thanks very much for your help and all your advice.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: ImraneA |
last post by:
Hi there
I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K.
Wish to provide some knowledge gained back to community - hopefully
help others.
1.Question how do you test stored procedure from SQL Server vs MS
Access point of view ?
|
by: Bob |
last post by:
Currently I am using this statement to translate 3 fields in my db
thru Visual Basic. I import the data from one table to another then
call the IFF statements and the NewDate to translate the fields. Can
this be done in an Access db? I am trying to learn how to do these
things in Access vs Visual
Dim strIIF As String
Dim sstrIIF As String...
|
by: abefuzzleduser2 |
last post by:
I am using sql 2000 (mdb) with linked tables on SQL 2000. I need to
use views to limit users records, instead of direct table access. I
setup a single talbe view in sql and tested update and inserting the
view using query analyzer. But when I add that view as a "linked"
table in access (windows db security dsn-less). Access does not allow...
|
by: royaltiger |
last post by:
I am trying to copy the inventory database in Building Access Applications
by John L Viescas but when i try to run the database i get an error in the
orders form when i click on the allocate button "Unexpected Error":3251
operation is not supported for this type of object.The demo cd has two
databases, one is called inventory and the other...
|
by: alexandre.brisebois |
last post by:
Hi, I am using access 2003, I would like to know if there is an option
to reorganize the tables in a maner that is readable, as we can do in
sql sever 2000 or 2005.
I have been given a database to look a and I am loosing tremendious
amounts of time trying to organize it so that I could view it.
Regards,
Alexandre Brisebois
| |
by: Kc-Mass |
last post by:
In a standard Ascii table a dash or hyphen is decimal 45. A period or dot is
decimal 46.
If I sort a table or recordset of mixed character string ascending in
Access, those strings beginning with a dot or period are first; those
beginning with a dash or hyphen are sorted as though the hyphen is not there
(that is the next non-hyphen...
|
by: jafastinger |
last post by:
I have a large union.
If I break it into its individual parts they all run quick. The
longest is the last select it takes 2 minutes to fetch all rows. When
I run the query below it does not come back for quite some time. 20
minutes. There are very few duplicates. When I run the sql's
individually the first two give no warning but the...
|
by: fiff |
last post by:
Hi,
I need help with query. I have access log table something like this:
session_id (int) | user (varchar) | login_time (datetime) | logout_time (datetime)
1 | JOHN | 2007-03-02 15:32:01 | 2007-03-02 18:01:55
2 | JOHN | 2007-03-09 11:43:33 | 2007-03-09 21:02:25
3 | JOHN | 2007-03-15 22:00:05 | 2007-03-16 06:37:21
4 | MIKE | 2007-04-02...
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge.
I would be using layman's...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| | |