Mar 25 2004, 07:46 PM
well - its been a while since i was last on here... todays's question: MERGINg TABLES
I have data sumitted to me in Excel format, which I then extract using Access' "Get External Data" command. Now, there's an option in the import wizard that should
let me import the data into an existing Access table w/in my db - but that option i ALWAY grayed out. I'm forced to import into a new table, even though the data, and format are identical.
That being the case - is there anyway for me to merge these two tables together?
Mar 25 2004, 10:49 PM
Try an Append query.
Mar 25 2004, 10:52 PM
i tried that - but it asks me for a "destination field"?
Mar 25 2004, 11:06 PM
when you change the select query to an append query, it asks for the table you want the records added to. Then, if the source table doesn't find an exact field name match in the receiving table you need to go to that row of your query grid and pick the "destination" fields that your source fields will go into. That row of your query will be unique to an Append query, is under the sort row, has the heading Append To and each column will offer the field list of the receiving table, the one you chose in dialog box when you switched from Select to Append query.
You said originally that your two tables from Excel are identical, do they have different headings, therefore different field names assigned when they become tables from your Excel imports? Or are they assigned Field 1, 2, etc?
Your request is fairly common, it is done often, you import both tables from Excel separately, then append one into the other one.
Mar 25 2004, 11:15 PM
sorry - there seems to be a new problem.
i've got quarterly data that for some reaon got split into jan in one sheet and feb and mar together on the second sheet. when i try the append (after establishing a dump table) the query tells me that the field names duplicate.
well, they will, as each worksheet has the SAME CLUMN HEADINGS defining the SAME DATA. it says i need to use an alias clause in the SQL language of the query so i can dump all the data (roughly 1500 records and growing) into the same appropriate fields.
thanks for the input so far - any other suggestions?
Mar 26 2004, 03:34 PM
Doesn't sound logical, a proper append query, from my experience, welcomes the same field names.
Perhaps you could attach the couple of tables in question. Otherwise, I am stumped.
Apr 22 2004, 07:35 PM
sorry for the delay in this -
its difficult for me to attach a couple of tables to this thread because the tables include customer bank account numbers and i would be in breach of bank privacy policies...
but the fields are (in order):
txtBranch/CC, txtBranchSalesID, txtProductID, curNewMoney, lngMonth/Yr, lngBookDate, txtClientName, curBookedAmount, txtAccountNumber, txtProduct, lngSalesID, txtSIPName
i can attach a zipped doc that contains a screen print of the query design...
any help is appreciated...
Apr 22 2004, 07:46 PM
this project is really beginning to blow chunks!!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here