Conversion Query

[David123]

Good Afternoon Ladies and Gentlemen of this forum, I had a question for all of you.

I am trying to write a query. The idea of the query is showing when a person needs a new appointment. I now have a query which gives the Last Name, the first date they visited, and a so called 'visit number'.

The visits follow a set pattern. The second appointment is always 10 days after the first one. Then, the third appointment is 2 months after the first appointment. The fourth appointment is half a year after the first appointment, and that goes on for about 8 appointments.

The 'visit number' is a number which indicates this. For instance, the first visit goes with a visit number of 1, the second visit with visit number 2, etc.

So, as said, I now have a query which shows me the last name of a patient, the date for the first visit, and the visit number. What I want is to add an amount of time that first date, depending on the visit number. I then want to output the last name, and the first date plus the amount of time added.

What I imagine is this. I have the first date, and the study number. Somewhere (this is one of the problems, where?) I have a list that says,

if visit number = 1,
then VariableX = 10 days
if visit number = 2,
then VariableX = 2 months


Etc.

I would then make VariableX a field in the query.

Finally, I would make another field, which would be the first_date field plus VariableX, and then output that.

My question consists of 2 parts.

First off, is this the smart way to do this? Is there an easier way? It seems that this is a reasonably straightforward way to me, but I'm not a very experienced programmer.

Secondly, if this is a good idea, then how do I do that? I need to have a list somewhere where that conversion happens, where do I put that? Do I make it a separate table? Can I put this in a query?

I hope someone can help me, because it seems like this might be a common problem.

Thanks on beforehand,

David.

PS: If there is any more information that you need to properly answer my question, I'd be happy to supply, just ask!

[TheSmileyCoder]

Hi there

Overall you give alot of detail in your question, and that does give alot to go on. There are several quite different ways to approach this, but before we start on the solution, lets make sure the problem is even better defined.

The main problem in any advanced application is the human aspect. I think you need to atleast consider some of the questions below:

What if a person cannot schedule a visit on said day? What if he is sick,(or forgets) and never shows up?

Do you want to "book" all visits at that same day (I.e. make all 10 appointments at the beginning), or do you book the following visit during the present visit?

What if the calculation X+Y days schedules a visit for christmas day? Is it ok to move it back/forward 2 days?

Are the visits similar in nature, or are different things supposed to happen on different visits?

Are resources from within your company required to be free and available to monitor the visits? In other words do Person X need to be available for the visit, and should the application factor in his/her availability?


Now, my own suggestion on an approach is going to be somewhat based on what im guesing the answers to the above questions are. I would probably have a button in which I would use code to SUGGEST an apointment. The code would check for the persons last visit, factor in your calculation of days between visits and SUGGEST a day. This day should then be confirmed or perhaps modified a few days back or forward to match when the person can actually fit in a visit. Once that bit is done, I would try to expand the application to take into account when the staff is available for appointments.

I hope this gives basis for some thought. Think it over, and come back with the answers to the questions supplied, and I am sure one of our experts can help with some code.

[Mihail]

First of all I wish to subscribe to this thread.

To calculate the amount of days based on visit number is quit simple:

Public Function AmountOfDays(VisitNumber As Integer) As Integer
    Select case VisitNumber
        Case 1
            AmountOfDays = 10
        Case 2
            AmountOfDays = 60
        Case ....
            AmountOfDays = ...
        Case Else
            MsgBox("Is no amount of days defined for visit " & VisitNumber )
            AmountOfDays = 0
    End Select
End Function

So for now your main problems are that that Smiley show you, not "how to".