473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

complex union query

Hi every body

I've a little problem with my query, I can't figure it out. I've three
tables:
Table A (EmployeNr, Date, Code)
Table B (EmployeNr, Date, Code) Which is an historic of Table A
Table C (BegD, EndD)

I can have all the records in table A that doesn't match in table B
but not from the other side.

Table A Table B Table C
0001 0001 20031001
20031002 20030930 20031031
PR1 PR1

0001 0001
20031003 20031001
PR1 Mal

0001
20031003
PR1
The result expected is:

0001
20031001
PR1

0001
20031002
PR1

I need to get all the records In table A which aren't in Table B
but also all the records in table B which aren't in table A but
matching with the period in table C.

Any suggestion will be greatly appreciated.
Nov 12 '05 #1
4 7193
Akinia wrote:
Hi every body

I've a little problem with my query, I can't figure it out. I've three
tables:
Table A (EmployeNr, Date, Code)
Table B (EmployeNr, Date, Code) Which is an historic of Table A
Table C (BegD, EndD)

I can have all the records in table A that doesn't match in table B
but not from the other side.

Table A Table B Table C
0001 0001 20031001
20031002 20030930 20031031
PR1 PR1

0001 0001
20031003 20031001
PR1 Mal

0001
20031003
PR1
The result expected is:

0001
20031001
PR1
Due to the difficulty of determining your example I wonder if PRI should
be Mal or PRI.


0001
20031002
PR1

I need to get all the records In table A which aren't in Table B
but also all the records in table B which aren't in table A but
matching with the period in table C.

Any suggestion will be greatly appreciated.


Create an unmatched query (Query/New/FindUnmatched) for TableA recs not in
TableB. Note your field names and their positions when you save the
query.

Now create an unmatched query (Query/New/FindUnmatched) for TableB recs
not in TableA. The field names, position and count should match those in
the first query. If this query does not contain a field contained in
Query1, you can always enter something like Expr1 : ""

Now modify this second query. Add TableC to it. Create a relationship
line between the date range fields of Table2 and Table3 if it does not
exist. While in desing mode of the second query, from the menu select
View/SQL. Highlight and copy the SQL to the clipboard.

Now open Query1 in design mode, select ViewSQL, remove the semi-colon at
the end, and enter
UNION ALL
and then paste the SQL statement from Query2.

Save and run.


Nov 12 '05 #2
Thanks for your answer U N ME
I have got the answer. see below:

SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Where B.EmployeeNr IS NULL

UNION ALL

Select B.*
FROM B INNER JOIN C
ON B.Period = C.Period
WHERE NOT EXITS
SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code

That's all.
Nov 12 '05 #3
Just a little mistake. here is the right SQL:

SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Where B.EmployeeNr IS NULL

UNION ALL

Select B.*
FROM B INNER JOIN C
ON B.Period = C.Period
WHERE NOT EXISTS
SELECT A.*
FROM A
WHERE A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Nov 12 '05 #4
Akinia wrote:
Just a little mistake. here is the right SQL:

SELECT A.*
FROM A LEFT JOIN B
ON A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code
Where B.EmployeeNr IS NULL

UNION ALL

Select B.*
FROM B INNER JOIN C
ON B.Period = C.Period
WHERE NOT EXISTS
SELECT A.*
FROM A
WHERE A.EmployeNr=B.EmployeNr
AND A.Date = B.Date
AND A.Code = B.Code


I see what you are doing now. I'm wondering if the Not Exists add some
time to the query. If it is not near instantaneous I might make the
first part Query1. Then in the second part make that Query2. I would
maybe make it

Select B.*
FROM B Left Join A On B.EmployeNR = A.EmployeeNR
B INNER JOIN C ON B.Period = C.Period
Where B.EmployeeNr IS NULL

Then create a third query where its
Select * from Query1 UNION ALL Select * from Query2

I think this would be much faster. Maybe not.

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Subodh | last post by:
I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and another column as Virtual_Pack. I've to write a query to update Virtual_Pack column. The logic is as follows 1 virtual pack...
1
by: Beachvolleyballer | last post by:
hi there anyone had an idea to join following 2 queries to 1???? ----- QUERY 1 --------------------------------------------- SELECT TMS_CaseF_2.Name AS TCDomain_0, TMS_CaseF_3.Name AS...
3
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT as ID from faxdata UNION SELECT as ID ...
2
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code ...
2
by: marco | last post by:
Dear List, as it seems, MS SQL as used in Access does not allow a select INTO within a UNION query. Also, it seems that a UNION query can not be used as a subquery. Maybe my (simplified)...
3
by: mikes | last post by:
I have 2 separate queries, which effectively are the same except they draw data from separate tables. Both tables are (design-wise) identical, only the data is different. for each query, there are...
1
by: phlype.johnson | last post by:
Suppose we have to design a database for a recruitment agency. There will be a table "candidates" with fields "candidateid","last name","first name" ; the languages mastered by a candidate as well...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
1
by: Randy Volkart | last post by:
I'm trying to fix a glitch in a complex access database, and have a fairly complex problem... unless there's some obscure easy fix I don't know being fairly new with Access. Basically, the area...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.