Full outer join and Union again !!

[Guest]

Hell
I'm trying something here whotch might be simple for you but it just didn't work for me and I need it to be done badll
I have 2 tables that are Transaction table which has about 40 col and about 70000 rows this table has many primary keys but what I care about here are project_id and transaction_id and I have anoher table called budget witch has anther 40 col and about 3000 rows but there is primary key created for this table it's an excel sheet and I export it to a database table using SQL serve
so what I'm trying to do here is to select all the rows from both table to create a crystal repor
I have tried the left outer join assuming that my transaction table is the left one so that displays all the records from the tranaction table and only the matched records from the budget table where the project_id and the transaction_id are matches on both table but it ignores the records not matching in the budget table
When I tried the right outer join you know it works vice versa so I still can't get all the records from both tabl
So here I tried to use the Full outer join but unfortunatly it's not giving me the right result and it still ignores some rows from both tables, as I'm not so experinced guy, I tried to understand some of your threds taking about duplicating some row and yes I remember when I was trying to use the union all I got some dupliactes , but I still can't get the right combination of using the full outer join with the union in my select statmen
It would be very appreciated if you can help me, since the result I'm expecting it supose to be the total records of both tables (about 73000) but what I'm getting now is about 71000 records and I just can't know which records were dropped and from witch table and what is the reason !
Thanks in advance

 

[Guest]

I am from the Access support department at http://www.enterpriseprojectconsulting.com and we would be happy to assist you in resolving your issue

Thank you

Enterprise Project Consulting

 

[david epsom dot com dot au]

You want all the rows from both tables? Matched where possible?

Create two queries: A left join B, B left join A
Choose the columns in the two queries so that the column order is the same.

Union the two queries:

select * from c
union
select * from d

'Union' rejects all duplicate records, so your result will be
A without B
B without A
A with B

Is this what you want?


(david)

Hello
I'm trying something here whotch might be simple for you but it just

didn't work for me and I need it to be done badlly

I have 2 tables that are Transaction table which has about 40 col and

about 70000 rows this table has many primary keys but what I care about here
are project_id and transaction_id and I have anoher table called budget
witch has anther 40 col and about 3000 rows but there is primary key created
for this table it's an excel sheet and I export it to a database table using
SQL server

so what I'm trying to do here is to select all the rows from both table to create a crystal report
I have tried the left outer join assuming that my transaction table is the

left one so that displays all the records from the tranaction table and only
the matched records from the budget table where the project_id and the
transaction_id are matches on both table but it ignores the records not
matching in the budget table.

When I tried the right outer join you know it works vice versa so I still

can't get all the records from both table

So here I tried to use the Full outer join but unfortunatly it's not

giving me the right result and it still ignores some rows from both tables,
as I'm not so experinced guy, I tried to understand some of your threds
taking about duplicating some row and yes I remember when I was trying to
use the union all I got some dupliactes , but I still can't get the right
combination of using the full outer join with the union in my select
statment

It would be very appreciated if you can help me, since the result I'm

expecting it supose to be the total records of both tables (about 73000) but
what I'm getting now is about 71000 records and I just can't know which
records were dropped and from witch table and what is the reason !!

 

[Michel Walsh]

Hi,




Your post is not appropriate in this newsgoup.


Vanderghast, Access MVP

I am from the Access support department at

http://www.enterpriseprojectconsulting.com and we would be happy to assist
you in resolving your issue.

 

[Michel Walsh]

Hi,



You use MS SQL Server as database? you probably use a WHERE clause too (or
some criteria). You should care for NULLs, in both tables (since both
tables, in a full outer join can be re-injected with NULL ).

Supplying the actual SQL statement may help us to be more precise.


Hoping it may help,
Vanderghast, Access MVP

Hello
I'm trying something here whotch might be simple for you but it just

didn't work for me and I need it to be done badlly

I have 2 tables that are Transaction table which has about 40 col and

about 70000 rows this table has many primary keys but what I care about here
are project_id and transaction_id and I have anoher table called budget
witch has anther 40 col and about 3000 rows but there is primary key created
for this table it's an excel sheet and I export it to a database table using
SQL server

so what I'm trying to do here is to select all the rows from both table to create a crystal report
I have tried the left outer join assuming that my transaction table is the

left one so that displays all the records from the tranaction table and only
the matched records from the budget table where the project_id and the
transaction_id are matches on both table but it ignores the records not
matching in the budget table.

When I tried the right outer join you know it works vice versa so I still

can't get all the records from both table

So here I tried to use the Full outer join but unfortunatly it's not

giving me the right result and it still ignores some rows from both tables,
as I'm not so experinced guy, I tried to understand some of your threds
taking about duplicating some row and yes I remember when I was trying to
use the union all I got some dupliactes , but I still can't get the right
combination of using the full outer join with the union in my select
statment

It would be very appreciated if you can help me, since the result I'm

expecting it supose to be the total records of both tables (about 73000) but
what I'm getting now is about 71000 records and I just can't know which
records were dropped and from witch table and what is the reason !!