ALLOWING DOUBLE ENTRY IN UNION QUERY

A

alish

I unite three tables and am not getting the right result. One of the tables
has double records, which is fine. In the result i want to see all
information from the tree tables, including double records. The union query
is grouping and giving me one record, where there are three of them. Please
help...
 
J

JohnFol

You need to use the ALL clause ie

select * from shippers
union ALL
select * from shippers
 
A

alish

john, i really appreciate your time given to answer my question. I followed
what you have suggested, but it is now giving my 4 instead of just 2. I guess
it doubled it.
 
L

Lynn Trapp

No, it didn't double it. UNION ALL will return all records, including
duplicates, from all tables involved. UNION will remove any duplicates.
 
A

alish

I am just through checking it. You guys are right. It did not double it.
Actually, one of the tables are giving me four records. Let me give you a
little description of my little project here: I am doing an audit involving
two supposedly similar tables. Each table has huge amount of information. It
has peoples worked hours, advances drawn during a certain week. And all info
is located under certain code. So, what I am doing is i am comparing them
together, doing it for each code. Table1 has to be processed and Table2 has
been processed.

I created queries for each code for two tables. I am stuck on deductions.
One employee was deducted twice. The queries are showing correct info: two
deductions for one employee.

I have 3 tables: InCommon (select query of Table1 and Table2) not grouped as
all records must be shown; InTable1_but_NotInTable2 (select query
Table1->Table2) not grouped as all records must be shown;
InTable2_but_NotInTable1 (select query Table1<-Table2) not grouped as all
records must be shown.

Then i unite them, the result shows that the employee has four deduction
records. If you know how to fix it, please help...

Thank you all for your precious time...
 
Top