Query problem !

[shenty]

I have a query that lists AI dates for animals. The same animal is often listed twice with different AI dates.

I want to add a column to the query, where, for the most recent AI date for any duplicate animals it puts a date 280 days from that AID date

ie

My query returns:-

DATE ID
11/09/2005 UK162574100222
28/02/2008 UK162574100222
28/04/2009 UK162574100222
28/12/2006 UK162574100222
27/02/2009 UK162574100222
16/05/2009 UK162574100222
19/08/2006 UK162574100222

For the most recent entry i want to add a 3rd column with a date 280 after the AI date.

Can anyone help on that ?

 

[jzwp22]

What do you want to show in the third column if the date is not the most recent for a particular animal? If you are only concerned with only the most recent AI date, do you even need to display the older dates at all?

In general, the dateadd() function can be used to add a particular number of days to a given date. This assumes that the particular date is a date/time datatype.

dateadd("d", 280, AIDate)

 

[shenty]

Yes i need to display ALL AIDates but only have a calculated date for the most recent for any given animal !

Does that make sense ?

 

[jzwp22]

You will need to know which date is the most recent for the animal. Therefore, you will need to create a totals query. Let's call this query: qryGetMostRecent

SELECT ID, Max(AIDate) as MaxofAIDate
FROM yourtablename
GROUP BY ID


Now in your original query you will have to use the IIF(), DCount() and dateadd() functions in conjunction with the query you just created to see if the date is the most current date and if so, add 280. That query will look something like this. I assumed that your ID field is text and the AIDate is a date/time datatype.

SELECT Table1.ID, Table1.AIDate, IIF(DCount("MaxOfAIDate","qryGetMostRecent","MaxofAIDate=#" & table1.AiDate & "# and ID='" & table1.ID & "'")>0, dateadd("d",280,AIDate),Null) AS NestAIDate
FROM Table1
Order by ID, AIDate desc

I have had some issue when working with dates in the dd/mm/yyyy format. Access always uses mm/dd/yyyy format in SQL and VBA code. So if things do not look correct you may want to check out this link

 

[Brianwarnock]

You don't need the complexity of a DCOUNT just join the table and the query like so

SELECT Table1.refid, Table1.aiddate, IIf([maxofaiddate] Is Null,Null,DateAdd("d",280,[maxofaiddate])) AS newdate
FROM Table1 LEFT JOIN Query1 ON (Table1.aiddate = Query1.MaxOfaiddate) AND (Table1.refid = Query1.refid)
ORDER BY Table1.refid, Table1.aiddate;

Brian

 

[shenty]

Thank you both for looking at this - i will go and look again after my tea !

I did get so far before i left the office and was getting somewhere near.....

I have created the totals query OK and altered the original query to add the dcount function. However it was only showing the calculated date for some of the records !! What is was showing was correct but there were a number of records that it wasn't showing a MaxOfAIDate for !

I will try Brians suggestion too shortly.

Thanks again both of you for your input.

 

[shenty]

Guys between you you seem to have answered my question.

With the DCount it was kind of working but for some strange reason missing dates on 'some' records, with no obvious pattern as to why !!! Much head scratching later & brian you saved my fingers from many splinters

That is a very neat lesson to learn - thank you.