Query Union tables

[Ciprian]

Morning to all!

So I have these 2 tables:

Table1
ID
ContractID (Primary)
Value
FinishDate

Table2
ID(Primary)
ContractID (allow duplicate)
AditionalID
Value
FinishDate

The Query should only show the data form the 2 tables if it's in the date range searched.

In the attachment i have a test database and a jpg showing what i what to achieve more clearly. Thanks for the help.

 

[Guus2005]

Your table design is questionable. It is weird to use a union query on two tables which seem mostly to be the same. Apparently there is a 1-N relationship but why is there a Finishdate in both tables??

The date restriction in the first part of the union query is set on the first table.
In the select statement of the first query Table2.Finishdate is also selected. As a result the date you didn't want to see is selected.

SELECT Table1.ContractID, Table1.Beneficiar, Table1.ValueContract, Table1.FinishDate, Table2.AditionalID, Table2.FazeNumber, Table2.ValueAditional, Table2.FinishDate
FROM Table1 INNER JOIN Table2 ON Table1.ContractID = Table2.ContractID
WHERE [COLOR="Red"](((Table1.FinishDate) Between Now()-5 And Now()))[/COLOR]
UNION SELECT Table1.ContractID, Table1.Beneficiar, Table1.ValueContract, Table1.FinishDate, Table2.AditionalID, Table2.FazeNumber, Table2.ValueAditional, Table2.FinishDate
FROM Table1 INNER JOIN Table2 ON Table1.ContractID = Table2.ContractID
WHERE (((Table2.FinishDate) Between Now()-5 And Now()));

To make it easier to understand create a union query which shows the data from both tables without restrictions. You can add them later.
Save the union query (Query1)

Use the query (Query1) to add some restrictions

SELECT Query1.ContractID, Query1.Beneficiar, Query1.ValueContract, Query1.FinishDate1, Query1.AditionalID, Query1.FazeNumber, Query1.ValueAditional, Query1.FinishDate2
FROM Query1 
WHERE (((Query1.FinishDate1) Between Now()-5 And Now()) AND ((Query1.FinishDate2) Between Now()-5 And Now()))

Later on you can put the restriction on both select statements in the union query to speed things up when using large amounts of data.

HTH