Calculate 6 months out

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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!

 

[Guest]

Hey guys, no doubt you can answer this with your eyes closed but here goe

I created a query and entered NextReviewDate which made it change that to Expr1: [NextReveiwDate

I then put the following in the first criteria ro
DateAdd("m",6,DMax("[Review Date]"," [ClientReviews]","Customers.CustomerID =" & [Customers.CustomerID])

It then asked me for the parameter for NextReviewDate, I click through and it says that the jet can't find [ClientReviews]

I have the continuous form set up as a subform and that seems to be working great. I created a simple report to view the clients and their previous report dates and again that all seems in order

How does the above code give me 6 months out from the latest date connected to my CustomerID

Back to the books!

 

[Guest]

Do I need to create the module or do any of the steps that were described previous by either Randal or the Johns

I'm kinda stuck, thanks ahead of time

 

[John Vinson]

Do I need to create the module or do any of the steps that were described previous by either Randal or the Johns?

I'm kinda stuck, thanks ahead of time

Randal's suggestion would use your current (non-normalized) table, and
would require creating a Module.

John and I agree that you should consider stepping back, and radically
changing the structure of the Tables. You won't be able to do this on
the Form - you'll need to scrap this form, restructure your tables,
and build a new Form with a Subform.

It sounds harder but believe me - this will be much better in the long
run.

 

[Guest]

You have the patience of a Saint

I set up a separate table as follow

Review:Tabl
I
CustomerI
Review Dat
Purpos

The form that I set up is based off this new table. There is some basic information in the Form Header and in the detail section is -[Review Date] and [Purpose] only. In my practice dbase, I put this on my main Client Info form linked by the Customer ID.(subform, for data entry

After I got your reply, I assumed that I needed to reset my tables and the module thing was for before I separated the table out. Is the table structure above correct

Will this need to be a stand alone form, or can it be attached to the Client Info form. I'm trying to keep all the data entry places centralized. If this is not attached should/can this form be opened with command button. This is what I'm picturing

Thanks so much

 

[John Vinson]

I set up a separate table as follows

Review:Table
ID
CustomerID
Review Date
Purpose

Looks good!

The form that I set up is based off this new table. There is some basic information in the Form Header and in the detail section is -[Review Date] and [Purpose] only. In my practice dbase, I put this on my main Client Info form linked by the Customer ID.(subform, for data entry)

After I got your reply, I assumed that I needed to reset my tables and the module thing was for before I separated the table out. Is the table structure above correct?

You won't need the module with this Table, and the structure is
correct. I'd strongly suggest (if you haven't done so) opening the
Relationships window and creating a Relationship between your Client
Info table to this table, joining on CustomerID; check the "Enforce
Referential Integrity" checkbox.

Will this need to be a stand alone form, or can it be attached to the Client Info form. I'm trying to keep all the data entry places centralized. If this is not attached should/can this form be opened with command button. This is what I'm picturing.

It will work best as a Subform on the Client Info form; set the Master
and Child Link Fields to CustomerID and you'll see only reviews for
that client, and new reviews entered will automatically fill in the
correct CustomerID. If I recall correctly, you wanted to be able to
see the next scheduled review six months after the the most recent
review; if so, put a textbox on the main form (not the subform!) and
set its Control Source to

=DateAdd("m", 6, DMax("[Review Date]", "[Review]", "[CustomerID] = " &
[CustomerID]))

 

[Guest]

I would say can I kiss your toes now, but someone might think that I have a fettish

Is that the same formula that I would use in a text box on a report

Thank You, Thank You etc.

 

[John Vinson]

I would say can I kiss your toes now, but someone might think that I have a fettish!

There was a young lady named Reaux
With erogenous zones in her teaux.
She remained onanistic
Till a foot-fetishistic
Young man became one of her beaux.

Is that the same formula that I would use in a text box on a report?

Sure. Just put an = in front of it.

Thank You, Thank You etc.

You're welcome!

 

[Tom Ellison]

That ought to teach you not to mess with our resident pun-master and
head jokester, the Wysart (wise-acre?), Mr. John.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

That ought to teach you not to mess with our resident pun-master and
head jokester, the Wysart (wise-acre?), Mr. John.

<mock-modest bow>