Next Date Query

[mystifier]

Can anyone help with the following query to display the next appointment date:

DATA
Company, Frequency, Appointment
---------------------------------------
CompanyA, 7, 1-Feb-2012
CompanyA, 7, 8-Feb-2012
CompanyA, 7, 15-Feb-2012
CompanyB, 10, 1-Feb-2012


CompanyB, 10, 11-Feb-2012
CompanyB, 10, 21-Feb-2012
CompanyC, 14, 1-Feb-2012
CompanyC, 14, 15-Feb-2012

RESULT
CompanyA, 22-Feb-2012
CompanyB, 02-Mar-2012
CompanyC, 29-Feb-2012

(i.e. Most recent Appointment + Frequency)

Thanks.

[pbaldy]

You should be able to use this to get the last record and the frequency:

http://www.baldyweb.com/LastValue.htm

In the second query, you can create a column with a DateAdd() using those two values.

[mystifier]

Thanks Paul.

If I had:
SELECT MAX(Appointment) As LastAppointment FROM Table GROUP BY Company

It worked fine but as soon as I added another field in broke. I would like to see how to handle it in one query but for now I can use two seperate queries as you suggest.

Thanks for the response.

[pbaldy]

The first can be a subquery in the second, but I feel it's harder to work with that way.