Thread: Query Join / Union

Any chance you could upload a sample db?

Glad it's working, you can zip and upload

Any chance you could convert this to be compatible with 2003?

Sorry, I can't come up with a simple way of doing this without 2 queries.

I'm not even sure that it's possible with standard Access SQL (happy to be proved wrong however!!)

I think you should look at 2 queries then looking at how to choose between them

How's this:

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() And Now()-5))  AND EXISTS (SELECT  FinishDate FROM Table2 WHERE Table2.FinishDate Between Now() And Now()-5);

It seemed to work on your sample database.

I thought you just have to uncheck the "SHOW" boxes for all of table 1 and this would solve your problem?

Also in your Between( ) - I thought the smaller number needed to come first but what do I know?

Thanks dave, came so close to that it's untrue lol, couldnt quite grt my head round it!

Marvin: All the documentation I can find agrees that the first 'argument' in the between predicate must be greater than the last. Not sure why it works in the example database the opposite way, but it does.

As far as showing the results from Table 1, the original post wanted most fields from both tables; I don't see how unchecking those would give the requested results.