Calculate 6 months out

[René]

I am required to do in home visits to my clients at least once every six months. I currently have seven fields on my main client form. The first field is always set to be six months greater than the highest field in the other six fields. We are currently doing this manually

How do I set up a query so that the "Review" date will be calculated on a value of 6 months from which ever is greater from "Reveiw1", "Review2" etc.

 

[Randal]

I think that this will do what you are asking...

First Create this Module

Public Function Most(ParamArray var() As Variant) As Variant

Dim max As Variant
Dim i As Integer

' Initialize
min = var(LBound(var))
For i = LBound(var) To UBound(var)
If var(i) > max Then max = var(i)
Next i

Most = max

End Function


Then this query

SELECT
Most([Table1]![Review2],[Table1]![Review3],[Table1]![Review4],[Table1]![Revi
ew5],[Table1]![Review6],[Table1]![Review7]) AS LatestDate,
DateAdd("m",6,[LatestDate]) AS Review1
FROM Table1;

I am required to do in home visits to my clients at least once every six

months. I currently have seven fields on my main client form. The first
field is always set to be six months greater than the highest field in the
other six fields. We are currently doing this manually.

How do I set up a query so that the "Review" date will be calculated on a

value of 6 months from which ever is greater from "Reveiw1", "Review2" etc.

 

[John Spencer (MVP)]

If you had a separate table to hold the Reviews, this would be simple. Can you
redesign your table structure? If not, you will need a user-defined function to
get the max date or you will need to use a complex iif statement to get the
maximum or you could use a union query to normalize the data.

The UNTESTED query would normalize the data. You could then use a DMax against
it to get the latest date and then add 6 months to that.

SELECT ClientID, Review1
FROM theTable
UNION
SELECT ClientID, Review2
FROM theTable
UNION
SELECT ClientID, Review3
FROM theTable
UNION
SELECT ClientID, Review4
FROM theTable
UNION
SELECT ClientID, Review5
FROM theTable
UNION
SELECT ClientID, Review6
FROM theTable

Then using a DMax function against the saved union query.
DMax("Review1","TheSavedUnionQuery","ClientID = " & Chr(34) &
SomeSpecificClientID & Chr(34))

Of course, this won't work if you haven't saved the records.

 

[René]

I'm a real newbie, so I am reading both of your suggestions. Thanks so much to both of you

The Client info is in my Customer tabl

The Review info in my Review Schedule tabl

They are connected at the moment as a subform [Review Schedule] on my [Client Info] form. In re-reading my post I didn't make that clear

Would that pertinent piece of information clarify, make easier or change either of the suggestions

PS - My previous dbase had only two tables, I am completely blown away but what I've been able to do by breaking out the unique data into separate tables

 

[John Vinson]

PS - My previous dbase had only two tables, I am completely blown away but what I've been able to do by breaking out the unique data into separate tables

Keep going... <g>

If you have six date fields in your Review table YOUR TABLE IS WRONG.

If you have a one to many relationship, you should have many
*records*, not many *fields*.

Your Review table would much better have *two* fields - the ClientID
and the ReviewDate. If a client has had six reviews there would be six
records for that client; if they've had eleven reviews, eleven
records.

You could then *calculate* the NextReviewDate dynamically without
storing it in your table at all, with an expression like

DateAdd("m", 6, DMax("[ReviewDate]", "[ClientReviews]", "ClientID = "
& [ClientID])

as a calculated field in a query.

 

[Rene]

I am learning sooooo much

I'm reading Running Access 2000 and am trying to find the answers to some of these things but I'm not sure what they are called to find the

Currently, I have a subform [Review Schedule] off my main [Client Info] form. It is a form with all seven fields. What you said makes sense, but I am unsure of the application (what it would look like). We currently are looking at the Main form, although I run a report which shows on the my opening switchboard to show who is due for a visit

The way you described will also allow us to not have to empty the fields periodically to start again. Yipee

So if I did need a visual (form) on my main form, it would be query driven? I think I understand what you are saying. Any other suggestions

 

[John Vinson]

I am learning sooooo much!

I'm reading Running Access 2000 and am trying to find the answers to some of these things but I'm not sure what they are called to find them

Currently, I have a subform [Review Schedule] off my main [Client Info] form. It is a form with all seven fields. What you said makes sense, but I am unsure of the application (what it would look like). We currently are looking at the Main form, although I run a report which shows on the my opening switchboard to show who is due for a visit.

The way you described will also allow us to not have to empty the fields periodically to start again. Yipee!

So if I did need a visual (form) on my main form, it would be query driven? I think I understand what you are saying. Any other suggestions?

You would have a continouous Subform on the mainform, based on the
schedule table. It would show seven (or more or fewer, as you wish)
rows, each with a review date and a calculated next-review date, or
you could have the next-review date in a textbox on the mainform.

 

[John Spencer (MVP)]

Rene,

No further advice at this time, just a comment.

Thank you. Your attitude and gratitude is an example of what makes it worth my
effort to invest my time and skill in these newsgroups. I hesitate to speak for
the others that do so, but I think that the majority also share my feeling on this.

Keep coming back with specific questions. Try to apply what you have learned
and when you get stuck, ask a specific question.

I am learning sooooo much!

I'm reading Running Access 2000 and am trying to find the answers to some of these things but I'm not sure what they are called to find them

Currently, I have a subform [Review Schedule] off my main [Client Info] form. It is a form with all seven fields. What you said makes sense, but I am unsure of the application (what it would look like). We currently are looking at the Main form, although I run a report which shows on the my opening switchboard to show who is due for a visit.

The way you described will also allow us to not have to empty the fields periodically to start again. Yipee!

So if I did need a visual (form) on my main form, it would be query driven? I think I understand what you are saying. Any other suggestions?

You would have a continouous Subform on the mainform, based on the
schedule table. It would show seven (or more or fewer, as you wish)
rows, each with a review date and a calculated next-review date, or
you could have the next-review date in a textbox on the mainform.

 

[John Vinson]

Thank you. Your attitude and gratitude is an example of what makes it worth my
effort to invest my time and skill in these newsgroups. I hesitate to speak for
the others that do so, but I think that the majority also share my feeling on this.

Keep coming back with specific questions. Try to apply what you have learned
and when you get stuck, ask a specific question.

You speak for me too, John. Thanks, Rene, and do come back; and John,
as always it's a pleasure joining forces with you!