subquery select

[Ciprian]

Hi,

this select query only works half right.

SELECT Table1.ContractID, Table1.ContractFinishDate, Table2.AddendumID, Table2.AddendumFinishDate
FROM Table1 LEFT JOIN Table2 ON Table1.ContractID = Table2.ContractID
WHERE (((Table1.ContractFinishDate) Like "*" & [Year] & "*"));

it selects the contracts that have a finish date in the given year. the problem is that it shows all of the addendum connected to each contract.

how do i modify this to only select the contracts and the addendum that has a finishdate in the year provided

edit:
so i think that i need to do an INTERSECT between the code already posted and this one, which gives me the correct addendum results

SELECT Table1.ContractID, Table1.ContractFinishDate, Table2.AddendumID, Table2.AddendumFinishDate
FROM Table1 INNER JOIN Table2 ON Table1.ContractID = Table2.ContractID
WHERE (((Table2.AddendumFinishDate) Like "*" & [Year] & "*"));

but access doesn't support INTERSECT as far as I know

 

[Galaxiom]

WHERE (((Table1.ContractFinishDate) Like "*" & [Year] & "*"))

This is not a nice peice of code. You have the date as a string which is a bad start and using the Like operator is very inefficient. You should be storing the date as a Date datatype and using a date range as the Where criteria.

In answer to your original question, you need to apply a condition to both date fields in the Where clause.

 

[Ciprian]

the dates are stored as Date type, i used the like operator since for this query i want all the records for a certain year

In answer to your original question, you need to apply a condition to both date fields in the Where clause.

if i use AND in the where clause, i lose the contracts that don't have addendum from the results

 

[Galaxiom]

the dates are stored as Date type, i used the like operator since for this query i want all the records for a certain year

It is still poor coding. It relies on an intrinsic conversion of the record to a string rather than being applied to the actual date. It don't think it would be able to use any index on the field either so will be relatively slow.

if i use AND in the where clause, i lose the contracts that don't have addendum from the results

Use a LEFT OUTER JOIN and the condition
WHERE (blah blah OR rightfieldname Is Null)

 

[Ciprian]

managed to do it

thanks GalaxiomAtHome for the NULL idea