Access 2007: Can Select query successfully, create table gives

[IntelliOfficer]

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

[jaxjagfan]

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.

[IntelliOfficer]

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

[IntelliOfficer]

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.