Thread: Query Join / Union

Hi

The easiest thing would be to have 2 queries.

Since you need to know which one to call, usually you'd use a stored procedure; however Access doesn't directly support these in the UI. They can be used within the VBA environment however, but it depends how comfortable you are in using code.

i thought that would be the case, i have some exp with vba in excel but in access none unfortunately.

but wouldn't a FULL JOIN help me in this case ? even though access doesn't support it, it would help to know

Wouldn't you need some way of telling Access whether to look at the Table2.FinishDate or Table1.FinishDate? Unless you wanted the same end date in both in which case you could use:

Select Table1.ContractID, Table1.Value, Table1.Finishdate, 
Table2.AditionalID, Table2.Value, Table2.FinishDate 
FROM Table1 LEFT JOIN Table2 ON Table1.ContractID=Table2.ContractID 
Where ((Table1.FinishDate) Between [Date1] AND [Date2])
AND ((Table2.FinishDate) Between [Date1] AND [Date2])

the searched FinishDate is the same for both Tables but i have cases when data in Table1 is not valid but the linked data in Table2 is valid.

If i use AND i won't get any results since the data in Table2 will never be the same FininshDate as in Table1.

If i use OR all the data in Table2 is showed in the results of the query

In that case you'd need some way of telling Access which Query to run - Stored Procedures

So you'd need to say run:

Select Table1.ContractID, Table1.Value, Table1.Finishdate, 
Table2.AditionalID, Table2.Value, Table2.FinishDate 
FROM Table1 LEFT JOIN Table2 ON Table1.ContractID=Table2.ContractID 
Where ((Table1.FinishDate) Between [Date1] AND [Date2])

or:

Select Table1.ContractID, Table1.Value, Table1.Finishdate, 
Table2.AditionalID, Table2.Value, Table2.FinishDate 
FROM Table1 LEFT JOIN Table2 ON Table1.ContractID=Table2.ContractID 
Where ((Table2.FinishDate) Between [Date1] AND [Date2])

Unless I've misunderstood?

Access can't do this natively so you'd need either 2 queries or use VBA to select which one to run

Actually, try the following, this might work depending on how you want the data displaying:

Select Table1.ContractID, Table1.Value, Table1.Finishdate, 
Table2.AditionalID, Table2.Value, Table2.FinishDate 
FROM Table1 LEFT JOIN Table2 ON Table1.ContractID=Table2.ContractID 
Where ((Table1.FinishDate) Between [Date1] AND [Date2])
UNION
Select Table1.ContractID, Table1.Value, Table1.Finishdate, 
Table2.AditionalID, Table2.Value, Table2.FinishDate 
FROM Table1 LEFT JOIN Table2 ON Table1.ContractID=Table2.ContractID 
Where ((Table2.FinishDate) Between [Date3] AND [Date4])

Try:

Select Table1.ContractID, Table1.Value, Table1.Finishdate, 
FROM Table1 
Where ((Table1.FinishDate) Between [Date1] AND [Date2])
UNION
Select Table2.ContractID, Table2.Value, Table2.Finishdate, 
FROM Table2 
Where ((Table2.FinishDate) Between [Date3] AND [Date4]);

Think I may have misunderstood, see my undeleted post, think this should work?

nope, i changed the jpg to show the query from where i get the results

the union thing does not work for me in this case