 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Merge 2 different tables
Hi, How is it possible to merge 2 different tables together. Table1 has AddressID, ApptNum, ApptDate, CompletedDate Table2 has AddressID, NANum, NADate, CompletedDate I want to merge into AddressID, Number, Date, CompletedDate, Description The Desctiption will show Appt for table1 and NA for table2. Thanks, SELECT Table1.AddressID, Table1.ApptNum AS [Number], Table1.ApptDate AS [Date], Table1.CompletedDate, "Appt" AS Description FROM Table1 UNION SELECT Table2.AddressID, Table2.NANum AS [Number], Table2.NADate AS [Date], Table2.CompletedDate, "NA" AS Description FROM Table2; WARNING: Bothe Number and Date are reserved words in Access and could cause problems. Check out the following: http://support.microsoft.com/kb/286335/  SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Hi, > [quoted text clipped - 9 lines] > > Thanks, Thanks, I adapted the Number and Date to aviod any issues with them being reserved words. The query works but when running it the description column is filled with the AddressID and not either "Appt" or "NA". How can I get around this? >SELECT Table1.AddressID, > Table1.ApptNum AS [Number], [quoted text clipped - 19 lines] >> >> Thanks, Forgot to write... If I just go with table1 and use "Appt" as Description it will work but when the Union is created it duplicates the AddressID >Thanks, I adapted the Number and Date to aviod any issues with them being >reserved words. [quoted text clipped - 7 lines] >>> >>> Thanks, I might be missing something here.... Please post your SQL statement and some sample data showing the problem. One thing to try first is to change the UNION to UNION ALL. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Forgot to write... > [quoted text clipped - 12 lines] > >>> > >>> Thanks, The SQL SELECT quniAddressAppts.uprn AS HRN, quniAddressAppts.ApptNum As ApptOrNANum, quniAddressAppts.ApptDate As ApptOrNADate, quniAddressAppts.completion_date AS [Completed?], "Appt" As Description FROM quniAddressAppts UNION SELECT quniAddressNoAccess.uprn AS HRN, quniAddressNoAccess.NA As ApptOrNANum, quniAddressNoAccess.NADate As ApptOrNADate, quniAddressNoAccess. completion_date AS [Completed?], "NA" As Desciption FROM quniAddressNoAccess; >I might be missing something here.... Please post your SQL statement and >some sample data showing the problem. [quoted text clipped - 5 lines] >> >>> >> >>> Thanks, Your SQL statement looks fine. We probably need to see a sample of the results and what it wrong with it. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > The SQL > [quoted text clipped - 17 lines] > >> >>> > >> >>> Thanks, Sample of the data when in Union query HRN ApptOrNANum ApptOrNADate Completed? Description 6543645 1 08-Apr-08 6543645 15352001295 1 12-Apr-08 12-Apr-08 15352001295 10029000261 1 14-Apr-08 15-Apr-08 10029000261 15352000825 1 02-Apr-08 02-Apr-08 15352000825 87555666 1 21-Apr-08 21-Apr-08 87555666 10457000677 1 27-May-08 10457000677 >Your SQL statement looks fine. We probably need to see a sample of the >results and what it wrong with it. [quoted text clipped - 3 lines] >> >> >>> >> >> >>> Thanks, This is when I just use the single query (not Union) 6543645 1 08-Apr-08 Appt 15352001295 1 12-Apr-08 12-Apr-08 Appt 10029000261 1 14-Apr-08 15-Apr-08 Appt 15352000825 1 02-Apr-08 02-Apr-08 Appt 87555666 1 21-Apr-08 21-Apr-08 Appt 10457000677 1 27-May-08 Appt >Sample of the data when in Union query > [quoted text clipped - 11 lines] >>> >> >>> >>> >> >>> Thanks, WOW! That is strange. Everything looks fine with one teenie-weenie thing. The word "Description" is reserved. Try changing it a little to something like "Descriptions". If this fixes it, I really, really want to know! For more on reserved words, check out the following: http://support.microsoft.com/kb/286335/ SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > This is when I just use the single query (not Union) > [quoted text clipped - 20 lines] > >>> >> >>> > >>> >> >>> Thanks, This is so bizarre!!! Tried the changing of Description to Descriptions and still the same happens. I am totally lost with this >WOW! That is strange. Everything looks fine with one teenie-weenie thing. The >word "Description" is reserved. Try changing it a little to something like [quoted text clipped - 8 lines] >> >>> >> >>> >> >>> >> >>> Thanks, I'd like to see this. If the information in the database isn't sensitive, please zip it up and send a copy to me at: oclv5200@hotmail.com SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > This is so bizarre!!! Tried the changing of Description to Descriptions and > still the same happens. [quoted text clipped - 13 lines] > >> >>> >> >>> > >> >>> >> >>> Thanks, Ok, I have come accross the issue thats stopping the uery running properly. I was recreating the DB with the tables in that build up my SQL and transfering the original Union Query into this temp DB and ran them to make sure it worked as should before sending to you. You would not believe it but the SQL been having issues with actually works and puts either "Appt" or "NA" in the Description columns. Unbelievable!!! Now here is my dilema... The Temp DB I was sending you worked on the fact I imported the tables from the Original Database, where as the DB I work from everyday uses the Link Tables option. Could this be affecting the whole thing? I dont understand why it would but thats the difference between it working and not working. If I was to recreate the DB as a stand alone DB would that resolve the issue? My original idea was to ADO link from the SQL Server to my Access DB (not that I know how to do that) but instead I opted for the Link Tables option. >I'd like to see this. If the information in the database isn't sensitive, >please zip it up and send a copy to me at: [quoted text clipped - 5 lines] >> >> >>> >> >>> >> >> >>> >> >>> Thanks, I am still struggling with this, and totally confused with what's happening >Ok, I have come accross the issue thats stopping the uery running properly. > [quoted text clipped - 22 lines] >>> >> >>> >> >>> >>> >> >>> >> >>> Thanks, Hi Jez, I finally got a chance to look at the database you sent. As you mentioned, it works find for me. Some possibilities: 1. You mention that it's linked to SQL Server. There could be some problems there. Instead of linking to the table, you might consider using pass-through queries and let SQL Server do the work. 2. You have a union query built upon other union queries. That introduces many more places for an error to occur. See if you can directly query the tables in one SQL statement. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > I am still struggling with this, and totally confused with what's happening > [quoted text clipped - 24 lines] > >>> >> >>> >> >>> > >>> >> >>> >> >>> Thanks,
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.