So, I'm pretty much in a nightmare, that makes no sense to me why it is a nightmare.
I want to do one simple thing: Join the entire information of two tables based on a field of like values. I have 41 sets of two tables to do this too. With some of these tables I am having the same problems in importing.
I should add, all the sets of tables have gone through considerable formatting and data cleaning in excel 2007 to make the values alike. Originally, I was using the "VLOOKUP" function in excel to join these tables...however, the huge size of these tables (some have over a million rows) means it needs to be done in a much more large-database friendly access format.
Behind the cut, I detail the problems I'm having from importing the tables, and with joining them the way I want (which is basically what I would accomplish using "VLOOKUP": a join of all the like values AND the inclusion of the data that did not have like values from the two sheets).
1) So, in Access 2007, I start by importing the two tables. I use External Data>Excel>Import, and select to create a table from this.
2) Enter Problem Uno One of my imported excel tables (notably the one with the most fields), has constant import errors. After Importing (and yes, I check that every field has the proper properties), I ALWAYS create a error table full of THOUSANDS of Type Conversion Errors. I read up carefully on them, but nothing I can find on the internet and in my texts answers my two critical questions about this...which are:
-DOES THIS CHANGE MY DATA IN ANYWAY (meaning the imported Access table is in any way different then my original Excel table)...and if not...
-WILL THIS AFFECT my join operation (notably the data types that are having Type Conversion Errors are not in the Field that I am basing my join on.
I should add that I've tried converting the excel (.xsls) to different file formasts (like a .csv and .dbf) with the same Type Conversion Errors occuring.
3) So, skipping over the above concerns (which notably, mean I can't Join if I can't figure them out)....and moving onto Joining.
The closest operation I can find to get the results I want (and please, feel free to tell me if this isn't my best option) is creating a 1-to-1 relationship (i.e. Database Tools>Relationships) between the two like fields in the different tables, and then running a query (Create>Query Design).
Notably once I hit "Run" I get this "Join Properties" message:
AS I understand it: None of these options (1,2, or 3) get me what I want: a table showing the joined values, and the un-joined values of both tables. For the time being I've been selecting option 1 (i.e. "Only include rows where the joined fields from both tables are equal")....but it's not really what I want. I know there is data in both tables that will not have corresponding data in the other table...and I'd still like to preserve that information if possible.
4) After running the Join (using Option 1...which isn't really what I want). I have a Query table. Fantastic (sort-of), but I want to make this a real table....so that I can add it into a master spreadsheet with it's 41 counterparts. After fishing around for awhile for a method to do this (apparently you cannot save a query as a table???) I chose to export this to an excel spreadsheet so that (hopefully) I could add it back into access again.
This was wildly to round-about to be logical...there has to be a better way (but I can't seem to find it).
Final Note: I'd say I'm like 20% familiar with Access, and 100% familiar with Excel. I've tried very hard to solve these dilemma's on my own...but it's getting me nowhere. Any help would be wildly appreciated!!