Calucation of Months between 2 records...

[tanyalee123]

Happy Friday!! I hope someone can help :-)



I have a field DATEOFSERVICE and I want to create a new field called NoOfMonths. This field should show how many months between the current DateOfService and the previous DateOfService. I don't know how to do this with the same field on a previous record/same table being used in the calculation.

Anyone? Anyone? Thank you!

Tanya

[ItsMe]

Maybe an alias in your query with DateDiff() function and count Months "M" ??? Sounds like Current Date is in a different record/row than previous date so maybe a CrossTab???

I handle these things at the form level or report level using VBA.

[orange]

I don't know that I follow the question. You don't store calculations in tables - you do calculations in queries when needed.
Perhaps it is your table structure that is confusing.

In plain English - let's consider a Customer -- CustomerX
CustomerX gets "serviced" on Date1. Other Customers receive various "services" on various Dates.
CustomerX returns for more service and receives service on DateA.

To get the latest serviceDate for CustomerX (approach untested)

Select CustomerID, serviceDate from table where
Customer = "CustomerX" and
serviceDate = (Select Max(serviceDate) from table as A
                       where customerId = "CustomerX")

To get the second last serviceDate for CustomerX (untested) You want to find the second latest serviceDate for this Customer.
So -approach is to get the latest date, and then get the latest serviceDate for this Customer that is not the latest serviceDate.

Select CustomerID, serviceDate from table where
Customer = "CustomerX" and
serviceDate = (Select Max(serviceDate) from table as A
                       where customerId = "CustomerX"  AND
                        serviceDate Not IN 
                          (SelectMax(serviceDate) from table as B
                           where customerId = "CustomerX")
                      );

Note: This is for the approach -- the code has not been tested.

The next real question is how do you define Months --- using Datediff you could get Days or Months-- but how many days in a Month???

[burrina]

You will need a second date to do this.

Datediff("m",FirstDate,SecondDate)

HTH

[tanyalee123]

Thanks everyone...I will see if I can try to get it working today.
Orange, yes I think what you're suggesting makes sense, I am going to try it out. No, I'm not trying to put the calculation in a table, sorry miscommunication. It will likely be on a form. The user just needs to see the result while she is in the person's record.

I think your approach will work......to clarify, this is the situation:
There are patients who go get a mammogram (DateofService), then say in 15 months, they go get another mammogram (DateofService). Possibly, in 18 months, they go get another mammogram. The user wants to know how many months between mammograms. So when she opens any record (visit) for the patient, she wants to know how many months between the mammogram/visit she's looking at and the previous mammogram/visit.

Each visit is a separate record, of course. The way the database is set up (I didn't design it), the user can only see one visit (one record) at a time, so if the user wants to calculate the months herself, she has to switch between records.

[orange]

The approach I suggested should work --specifically for latest serviceDate and the serviceDate prior to that (2nd latest).
If you need to know the difference between any two consecutive serviceDates, you'll need some logic to determine which are the relevant serviceDates.
Good luck with your project. (The design seems correct---thanks for the added info).