Michael Franz
asked on
MS Access and SQL Joining / Union
Trying to take data from a SQL table and Access table and inter-lay them. We have 2 processes that causes 1 department to use a program built in SQL and another process where Access was built to do the job. Each data source has multiple tables and multiple like and unlike field names. BUT! there are 2 tables that I am interested in. In SQL it is New Business Header. In the Access database it is Submission. Both "table" have 5 field names (and in meaning) that I am trying to report on. I ONLY want 1 report for those 5 fields. See the attached excel file for a picture. Nothing is technically in common between the 2. I just want to "stack" the data on top of each other.
I will need to create a group or sort later.
I will need to create a group or sort later.
Jim Horn
Attachy no worky
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Here is the attachment
Informer-Reporting.xlsx
Informer-Reporting.xlsx
ASKER
I have a program called Informer that is not linked to the individual datasource, but can see both datasources. Can we try something simple to see if we can get 1 field from each source.
Looking at the attachment, should be pretty straightforward...
SELECT [Cust#], Name, Lob, Carrier, Premium
FROM NewBusinessHeader
UNION ALL
SELECT [Cust#], Name, Lob, Carrier, Premium
FROM Submissions
Couple of things though..
(1) If any of these columns do not have a matching data type, such as Access text to SQL numeric, then you'll have to format one to match the other.
(2) I'd lose the # in the column name, which forces you to use square brackets [ ].
(3) The Final results have a Mike, and there's no Mike in either table. Typo?
SELECT [Cust#], Name, Lob, Carrier, Premium
FROM NewBusinessHeader
UNION ALL
SELECT [Cust#], Name, Lob, Carrier, Premium
FROM Submissions
Couple of things though..
(1) If any of these columns do not have a matching data type, such as Access text to SQL numeric, then you'll have to format one to match the other.
(2) I'd lose the # in the column name, which forces you to use square brackets [ ].
(3) The Final results have a Mike, and there's no Mike in either table. Typo?
ASKER
Couple of things though.
(1) If any of these columns do not have a matching data type, such as Access text to SQL numeric, then you'll have to format one to match the other. All the same
(2) I'd lose the # in the column name, which forces you to use square brackets [ ]. Just used because in the excel file. easier to type
(3) The Final results have a Mike, and there's no Mike in either table. Typo? Yes typo
(1) If any of these columns do not have a matching data type, such as Access text to SQL numeric, then you'll have to format one to match the other. All the same
(2) I'd lose the # in the column name, which forces you to use square brackets [ ]. Just used because in the excel file. easier to type
(3) The Final results have a Mike, and there's no Mike in either table. Typo? Yes typo
>I have a program called Informer that is not linked to the individual datasource, but can see both datasources
huh?
huh?
ASKER
I am just saying mentioning it because I have no link between the 2 datasources what so ever. So I felt it was relavent because somewhere SQL and Access needs to see the other datasource and table
>I am just saying mentioning it because I have no link between the 2 datasources what so ever.
As long as you can link the two tables, from the two separate data sources, into the same Access database, then this is not relevant. Access will treat (big sweeping generalization, true in this case) all tables the same regardless of if they are linked or local, for query manipulation.
As long as you can link the two tables, from the two separate data sources, into the same Access database, then this is not relevant. Access will treat (big sweeping generalization, true in this case) all tables the same regardless of if they are linked or local, for query manipulation.
ASKER
thanks for the help. GOt the UNION ALL to work.
Thanks for the grade. Good luck with your project. -Jim