Union query to show records after certain date (1 Viewer)

[shenty]

Hi all

I am hoping someone can help with the attached sample database.

I have created a query (qryHistoryPD) which pulls information from several other queries, some of which are union queries. In there it shows a date for "Last Calved" which is a date created from another query.

I only want this query (qryHistoryPD) to show information if the Date is AFTER or ON the MaxOfDate !.

The report lists some history of some animals, including AI details, Bulling details, medicine details and calving details. But i want the report to only show these details AFTER the last calving date ! (Or ALL the details if that particular animal has not calved before).

Any help would be great as i feel like i'm getting my knickers in a twist over something relatively simple !!!

Thanks
John

 

[Guus2005]

SELECT [History-PD].ID, [History-PD].DATE, [History-PD].DETAIL, [History-PD].Expr1003, AnimalRegister.Brand, AnimalRegister.Sex, AnimalRegister.Breed, AnimalRegister.DOB, AnimalRegister.InCalf, qryLastCalvedforPD.MaxOfDATE
FROM (AnimalRegister INNER JOIN [History-PD] ON AnimalRegister.TAG = [History-PD].ID) LEFT JOIN qryLastCalvedforPD ON [COLOR="red"]([History-PD].DATE >= qryLastCalvedforPD.MaxOfDATE)[/COLOR] AND ([History-PD].ID = qryLastCalvedforPD.ID)
WHERE (((AnimalRegister.Sex)="F") AND [COLOR="Red"]((qryLastCalvedforPD.MaxOfDATE) Is Not Null)[/COLOR] AND ((AnimalRegister.[On Farm])=Yes) AND ((AnimalRegister.KeptFor)="1"))
ORDER BY [History-PD].ID, [History-PD].DATE;

This is your query with two changes (colored red).
because you are using an outer join there are some MAXDATE fields null. They are filtered out (((qryLastCalvedforPD.MaxOfDATE) Is Not Null))

Knowing it is better to use a regular join to filter these out but i didn't wanted to change your query much.
Since the QBE can't represent the >= in a join, you have to do it in the SQL window.

HTH

 

[shenty]

aaaaaahhhhhhhhhh - spotted it - nice 1 - thank you very much

perfect

thanks