how to set up

  • Thread starter Thread starter nydia
  • Start date Start date
N

nydia

i have a database that keeps track of clients that start
and end a sepcific program (a client may choose to leave
the program and start again in the same month). tblclient
has client name address. tblclientdates has startdate,
enddate. tblfollowup has follow-up date, follow-up method
(method could be phone, letter or face2face). Each time a
client leaves the program, the manager has to do 4 follow-
up's (1 month, 3 months, 6 months and 1 year after the
termination date).

I need to create a report that will show the manager who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this up
because a follow-up date is not entered until it has been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
 
I do the exact post monitoring. Since I need to store the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the program-end
date which adds the four follow-up records to the follow-up
table. The only information that is added is the date
follow-up scheduled. The dates are calculated by add 1
month, 3 month, 6 months and 1year to the program-end date.

Once you have the follow-up dates added to you table you
can create forms and reports.

This method has worked well for my users.

Jim
 
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a client
leaves 4 dates are added, but if the client comes back and
leaves again, will it add another 4 dates (i want it to)
 
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date + 1 month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2 month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of the
Program End date Control on the form
 
so create 4 different qry's? when i create the macro what
would be the action? I've never done a macro, so, im
alittle confused. yes, everytime the client comes back a
new entry date is logged in. all the tables are related
by client id, do i have to add the clientid field in the
queries? how does it know what clientid to put in the
follow-up table? thanks a million for all your help...
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date + 1 month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2 month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of the
Program End date Control on the form

-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a client
leaves 4 dates are added, but if the client comes back and
leaves again, will it add another 4 dates (i want it to) follow-
up time
a
.
Click to expand...
.
Click to expand...
 
i tried doing this, the big problem is that if you enter a
end date, you get a message stating it is going to append,
but it appends all the fields that have a end date, so it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date + 1 month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2 month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of the
Program End date Control on the form

-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a client
leaves 4 dates are added, but if the client comes back and
leaves again, will it add another 4 dates (i want it to) follow-
up time
a
.
Click to expand...
.
Click to expand...
 
Here is the macro
SetWarnings to no
OpenQuery
OpenQuery
OpenQuery
OpenQuery
SetWarnings to yes

The SetWarnings command suppresses prompts

The queries
in the criteria for record id put
[Forms]![NameOfForm]![record id]
This will just add the 4 record for the current record on
the form

Jim
-----Original Message-----
i tried doing this, the big problem is that if you enter a
end date, you get a message stating it is going to append,
but it appends all the fields that have a end date, so it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date + 1 month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2 month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of the
Program End date Control on the form

-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a client
leaves 4 dates are added, but if the client comes back and
leaves again, will it add another 4 dates (i want it to)
-----Original Message-----
I do the exact post monitoring. Since I need to store the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the program- end
date which adds the four follow-up records to the follow-
up
table. The only information that is added is the date
follow-up scheduled. The dates are calculated by add 1
month, 3 month, 6 months and 1year to the program-end
date.

Once you have the follow-up dates added to you table you
can create forms and reports.

This method has worked well for my users.

Jim


-----Original Message-----
i have a database that keeps track of clients that start
and end a sepcific program (a client may choose to leave
the program and start again in the same month).
tblclient
has client name address. tblclientdates has startdate,
enddate. tblfollowup has follow-up date, follow-up
method
(method could be phone, letter or face2face). Each time
a
client leaves the program, the manager has to do 4
follow-
up's (1 month, 3 months, 6 months and 1 year after the
termination date).

I need to create a report that will show the manager
who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this up
because a follow-up date is not entered until it has
been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
2 problems

1. if a client has more than 1 end date (because they have
started and ended the program more than once) when you
enter a new end date, it creates new dates for all the end
dates for this client, creating duplicates.

2. in my form, i have a subform with scheduled follow-up
dates and completed follow-up dates, but when the end date
is entered, the scheduled follow-up date doesnt show in
the form, you have to close the form and reopen it for it
to show the scheduled date.

-----Original Message-----
Here is the macro
SetWarnings to no
OpenQuery
OpenQuery
OpenQuery
OpenQuery
SetWarnings to yes

The SetWarnings command suppresses prompts

The queries
in the criteria for record id put
[Forms]![NameOfForm]![record id]
This will just add the 4 record for the current record on
the form

Jim
-----Original Message-----
i tried doing this, the big problem is that if you enter a
end date, you get a message stating it is going to append,
but it appends all the fields that have a end date, so it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date +
Click to expand...
1
month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2 month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of the
Program End date Control on the form


-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a client
leaves 4 dates are added, but if the client comes back and
leaves again, will it add another 4 dates (i want it to)
-----Original Message-----
I do the exact post monitoring. Since I need to
Click to expand...
store
the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the program- end
date which adds the four follow-up records to the follow-
up
table. The only information that is added is the date
follow-up scheduled. The dates are calculated by add 1
month, 3 month, 6 months and 1year to the program-end
date.

Once you have the follow-up dates added to you table you
can create forms and reports.

This method has worked well for my users.

Jim


-----Original Message-----
i have a database that keeps track of clients that start
and end a sepcific program (a client may choose to leave
the program and start again in the same month).
tblclient
has client name address. tblclientdates has startdate,
enddate. tblfollowup has follow-up date, follow-up
method
(method could be phone, letter or face2face). Each time
a
client leaves the program, the manager has to do 4
follow-
up's (1 month, 3 months, 6 months and 1 year after the
termination date).

I need to create a report that will show the manager
who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this up
because a follow-up date is not entered until it has
been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
1.
I was thinking that if a client ended and restarted that it
would be a new record. What you could do is to check to
see if there are follow-up records for that client; if
there are run a delete query for those records and add the
new records refelcting the new followup dates. Is this
what you want?

2.
in the same macro that adds the records add a Requery
command for the schedule follow-up date control.
Action: Requery
Control: schedule follow-up date control name

Jim

-----Original Message-----
2 problems

1. if a client has more than 1 end date (because they have
started and ended the program more than once) when you
enter a new end date, it creates new dates for all the end
dates for this client, creating duplicates.

2. in my form, i have a subform with scheduled follow-up
dates and completed follow-up dates, but when the end date
is entered, the scheduled follow-up date doesnt show in
the form, you have to close the form and reopen it for it
to show the scheduled date.

-----Original Message-----
Here is the macro
SetWarnings to no
OpenQuery
OpenQuery
OpenQuery
OpenQuery
SetWarnings to yes

The SetWarnings command suppresses prompts

The queries
in the criteria for record id put
[Forms]![NameOfForm]![record id]
This will just add the 4 record for the current record on
the form

Jim
-----Original Message-----
i tried doing this, the big problem is that if you enter a
end date, you get a message stating it is going to append,
but it appends all the fields that have a end date, so it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date + 1
month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2 month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of the
Program End date Control on the form


-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a client
leaves 4 dates are added, but if the client comes back
and
leaves again, will it add another 4 dates (i want it to)
-----Original Message-----
I do the exact post monitoring. Since I need to store
the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the program-
end
date which adds the four follow-up records to the
follow-
up
table. The only information that is added is the date
follow-up scheduled. The dates are calculated by add 1
month, 3 month, 6 months and 1year to the program-end
date.

Once you have the follow-up dates added to you table you
can create forms and reports.

This method has worked well for my users.

Jim


-----Original Message-----
i have a database that keeps track of clients that
start
and end a sepcific program (a client may choose to
leave
the program and start again in the same month).
tblclient
has client name address. tblclientdates has startdate,
enddate. tblfollowup has follow-up date, follow-up
method
(method could be phone, letter or face2face). Each
time
a
client leaves the program, the manager has to do 4
follow-
up's (1 month, 3 months, 6 months and 1 year after the
termination date).

I need to create a report that will show the manager
who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this up
because a follow-up date is not entered until it has
been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I need to keep the previous follow-up dates, because i
need to know how many times a client has been in the
program. and when a follow-up is done, there are comments
added, which i need. sorry if i wasnt clear on that. it
is a new record, but it keeps the old one. the frm is like
a spreadsheet

EXAMPLE:
clientID Projf-update f-upcompleteddate comments
7 01/01/03 01/02/03 unable to contact
7 05/01/04 05/05/04 spoke to mother,in jail
etc.....
-----Original Message-----
1.
I was thinking that if a client ended and restarted that it
would be a new record. What you could do is to check to
see if there are follow-up records for that client; if
there are run a delete query for those records and add the
new records refelcting the new followup dates. Is this
what you want?

2.
in the same macro that adds the records add a Requery
command for the schedule follow-up date control.
Action: Requery
Control: schedule follow-up date control name

Jim

-----Original Message-----
2 problems

1. if a client has more than 1 end date (because they have
started and ended the program more than once) when you
enter a new end date, it creates new dates for all the end
dates for this client, creating duplicates.

2. in my form, i have a subform with scheduled follow-up
dates and completed follow-up dates, but when the end date
is entered, the scheduled follow-up date doesnt show in
the form, you have to close the form and reopen it for it
to show the scheduled date.

-----Original Message-----
Here is the macro
SetWarnings to no
OpenQuery
OpenQuery
OpenQuery
OpenQuery
SetWarnings to yes

The SetWarnings command suppresses prompts

The queries
in the criteria for record id put
[Forms]![NameOfForm]![record id]
This will just add the 4 record for the current record on
the form

Jim

-----Original Message-----
i tried doing this, the big problem is that if you
Click to expand...
enter
a
end date, you get a message stating it is going to append,
but it appends all the fields that have a end date, so it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date
Click to expand...
+
1
month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2 month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event
Click to expand...
of
the
Program End date Control on the form


-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a client
leaves 4 dates are added, but if the client comes back
and
leaves again, will it add another 4 dates (i want it to)
-----Original Message-----
I do the exact post monitoring. Since I need to store
the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the program-
end
date which adds the four follow-up records to the
follow-
up
table. The only information that is added is the date
follow-up scheduled. The dates are calculated by
Click to expand...
add
1
month, 3 month, 6 months and 1year to the program- end
date.

Once you have the follow-up dates added to you
Click to expand...
table
you
can create forms and reports.

This method has worked well for my users.

Jim


-----Original Message-----
i have a database that keeps track of clients that
start
and end a sepcific program (a client may choose to
leave
the program and start again in the same month).
tblclient
has client name address. tblclientdates has startdate,
enddate. tblfollowup has follow-up date, follow-up
method
(method could be phone, letter or face2face). Each
time
a
client leaves the program, the manager has to do 4
follow-
up's (1 month, 3 months, 6 months and 1 year after the
termination date).

I need to create a report that will show the manager
who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this up
because a follow-up date is not entered until it has
been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I not sure I understand. If a client ends Jan 5th. You
would add the followup records. If the client reenters and
end Jan 25th 4 more records would be added with the
followup dates based on the new Program end date. Do you
have more than one field for the program end date?(how
many) If so, you would check to see if that date is
occupied and base the follow-up records on that one. You
can see it is getting more complicated. I am not sure what
you want to follow up? The first or second or both program
end dates.

Jim

-----Original Message-----
I need to keep the previous follow-up dates, because i
need to know how many times a client has been in the
program. and when a follow-up is done, there are comments
added, which i need. sorry if i wasnt clear on that. it
is a new record, but it keeps the old one. the frm is like
a spreadsheet

EXAMPLE:
clientID Projf-update f-upcompleteddate comments
7 01/01/03 01/02/03 unable to contact
7 05/01/04 05/05/04 spoke to mother,in jail
etc.....
-----Original Message-----
1.
I was thinking that if a client ended and restarted that it
would be a new record. What you could do is to check to
see if there are follow-up records for that client; if
there are run a delete query for those records and add the
new records refelcting the new followup dates. Is this
what you want?

2.
in the same macro that adds the records add a Requery
command for the schedule follow-up date control.
Action: Requery
Control: schedule follow-up date control name

Jim

-----Original Message-----
2 problems

1. if a client has more than 1 end date (because they have
started and ended the program more than once) when you
enter a new end date, it creates new dates for all the end
dates for this client, creating duplicates.

2. in my form, i have a subform with scheduled follow-up
dates and completed follow-up dates, but when the end date
is entered, the scheduled follow-up date doesnt show in
the form, you have to close the form and reopen it for it
to show the scheduled date.


-----Original Message-----
Here is the macro
SetWarnings to no
OpenQuery
OpenQuery
OpenQuery
OpenQuery
SetWarnings to yes

The SetWarnings command suppresses prompts

The queries
in the criteria for record id put
[Forms]![NameOfForm]![record id]
This will just add the 4 record for the current record on
the form

Jim

-----Original Message-----
i tried doing this, the big problem is that if you enter
a
end date, you get a message stating it is going to
append,
but it appends all the fields that have a end date, so
it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to
append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date +
1
month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2
month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of
the
Program End date Control on the form


-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a
client
leaves 4 dates are added, but if the client comes back
and
leaves again, will it add another 4 dates (i want it
to)
-----Original Message-----
I do the exact post monitoring. Since I need to
store
the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the program-
end
date which adds the four follow-up records to the
follow-
up
table. The only information that is added is the date
follow-up scheduled. The dates are calculated by add
1
month, 3 month, 6 months and 1year to the program- end
date.

Once you have the follow-up dates added to you table
you
can create forms and reports.

This method has worked well for my users.

Jim


-----Original Message-----
i have a database that keeps track of clients that
start
and end a sepcific program (a client may choose to
leave
the program and start again in the same month).
tblclient
has client name address. tblclientdates has
startdate,
enddate. tblfollowup has follow-up date, follow-up
method
(method could be phone, letter or face2face). Each
time
a
client leaves the program, the manager has to do 4
follow-
up's (1 month, 3 months, 6 months and 1 year after
the
termination date).

I need to create a report that will show the manager
who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this up
because a follow-up date is not entered until it has
been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
.

.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
my follow-up tbl contains clientid,scheduled follow-up
date, follow-up method (could be phone, letter), date
follow-up completed and then comments.

my dates table has clientID, interview date, startdate,
terminationdate, comments.

my dates subform and follow-up subform, look like excel
spreadsheets. they show all the times the client entered,
ended, comments. so if a client leaves jan 5 there should
be 4 follow-up dates added to scheduledfollow-update and
if the client comes back and leaves again jan 25 there
should be another 4 dates. it's not likely that a client
would leave and come back a few days later(they might come
back a month later. hope i'm explain this right. thanks
for all your help and being patient :)
-----Original Message-----
I not sure I understand. If a client ends Jan 5th. You
would add the followup records. If the client reenters and
end Jan 25th 4 more records would be added with the
followup dates based on the new Program end date. Do you
have more than one field for the program end date?(how
many) If so, you would check to see if that date is
occupied and base the follow-up records on that one. You
can see it is getting more complicated. I am not sure what
you want to follow up? The first or second or both program
end dates.

Jim

-----Original Message-----
I need to keep the previous follow-up dates, because i
need to know how many times a client has been in the
program. and when a follow-up is done, there are comments
added, which i need. sorry if i wasnt clear on that. it
is a new record, but it keeps the old one. the frm is like
a spreadsheet

EXAMPLE:
clientID Projf-update f-upcompleteddate comments
7 01/01/03 01/02/03 unable to contact
7 05/01/04 05/05/04 spoke to mother,in jail
etc.....
-----Original Message-----
1.
I was thinking that if a client ended and restarted
Click to expand...
that
it
would be a new record. What you could do is to check to
see if there are follow-up records for that client; if
there are run a delete query for those records and add the
new records refelcting the new followup dates. Is this
what you want?

2.
in the same macro that adds the records add a Requery
command for the schedule follow-up date control.
Action: Requery
Control: schedule follow-up date control name

Jim


-----Original Message-----
2 problems

1. if a client has more than 1 end date (because they have
started and ended the program more than once) when you
enter a new end date, it creates new dates for all the end
dates for this client, creating duplicates.

2. in my form, i have a subform with scheduled follow- up
dates and completed follow-up dates, but when the end date
is entered, the scheduled follow-up date doesnt show in
the form, you have to close the form and reopen it for it
to show the scheduled date.


-----Original Message-----
Here is the macro
SetWarnings to no
OpenQuery
OpenQuery
OpenQuery
OpenQuery
SetWarnings to yes

The SetWarnings command suppresses prompts

The queries
in the criteria for record id put
[Forms]![NameOfForm]![record id]
This will just add the 4 record for the current
Click to expand...
record
on
the form

Jim

-----Original Message-----
i tried doing this, the big problem is that if you enter
a
end date, you get a message stating it is going to
append,
but it appends all the fields that have a end date, so
it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to
append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4 records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end
Click to expand...
date
+
1
month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2
month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event of
the
Program End date Control on the form


-----Original Message-----
this makes sense, how do i add the event, how does the
event know to add 4 dates to the table? and if a
client
leaves 4 dates are added, but if the client comes back
and
leaves again, will it add another 4 dates (i want it
to)
-----Original Message-----
I do the exact post monitoring. Since I need to
store
the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the program-
end
date which adds the four follow-up records to the
follow-
up
table. The only information that is added is the date
follow-up scheduled. The dates are calculated by add
1
month, 3 month, 6 months and 1year to the program- end
date.

Once you have the follow-up dates added to you table
you
can create forms and reports.

This method has worked well for my users.

Jim


-----Original Message-----
i have a database that keeps track of clients that
start
and end a sepcific program (a client may choose to
leave
the program and start again in the same month).
tblclient
has client name address. tblclientdates has
startdate,
enddate. tblfollowup has follow-up date, follow- up
method
(method could be phone, letter or face2face). Each
time
a
client leaves the program, the manager has to do 4
follow-
up's (1 month, 3 months, 6 months and 1 year after
the
termination date).

I need to create a report that will show the manager
who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this up
because a follow-up date is not entered until it has
been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
.

.

.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Then the macro would be triggered after a date is put in
the dates subform in your AfterUpdate event of the
terminationdate filed. Right click on that field and click
properties. Goto the AfterUpdate event and select the
macro created earlier. The query would use that row
termination date.

Jim
-----Original Message-----
my follow-up tbl contains clientid,scheduled follow-up
date, follow-up method (could be phone, letter), date
follow-up completed and then comments.

my dates table has clientID, interview date, startdate,
terminationdate, comments.

my dates subform and follow-up subform, look like excel
spreadsheets. they show all the times the client entered,
ended, comments. so if a client leaves jan 5 there should
be 4 follow-up dates added to scheduledfollow-update and
if the client comes back and leaves again jan 25 there
should be another 4 dates. it's not likely that a client
would leave and come back a few days later(they might come
back a month later. hope i'm explain this right. thanks
for all your help and being patient :)
-----Original Message-----
I not sure I understand. If a client ends Jan 5th. You
would add the followup records. If the client reenters and
end Jan 25th 4 more records would be added with the
followup dates based on the new Program end date. Do you
have more than one field for the program end date?(how
many) If so, you would check to see if that date is
occupied and base the follow-up records on that one. You
can see it is getting more complicated. I am not sure what
you want to follow up? The first or second or both program
end dates.

Jim

-----Original Message-----
I need to keep the previous follow-up dates, because i
need to know how many times a client has been in the
program. and when a follow-up is done, there are comments
added, which i need. sorry if i wasnt clear on that. it
is a new record, but it keeps the old one. the frm is like
a spreadsheet

EXAMPLE:
clientID Projf-update f-upcompleteddate comments
7 01/01/03 01/02/03 unable to contact
7 05/01/04 05/05/04 spoke to mother,in jail
etc.....
-----Original Message-----
1.
I was thinking that if a client ended and restarted that
it
would be a new record. What you could do is to check to
see if there are follow-up records for that client; if
there are run a delete query for those records and add the
new records refelcting the new followup dates. Is this
what you want?

2.
in the same macro that adds the records add a Requery
command for the schedule follow-up date control.
Action: Requery
Control: schedule follow-up date control name

Jim


-----Original Message-----
2 problems

1. if a client has more than 1 end date (because they
have
started and ended the program more than once) when you
enter a new end date, it creates new dates for all the
end
dates for this client, creating duplicates.

2. in my form, i have a subform with scheduled follow- up
dates and completed follow-up dates, but when the end
date
is entered, the scheduled follow-up date doesnt show in
the form, you have to close the form and reopen it for
it
to show the scheduled date.


-----Original Message-----
Here is the macro
SetWarnings to no
OpenQuery
OpenQuery
OpenQuery
OpenQuery
SetWarnings to yes

The SetWarnings command suppresses prompts

The queries
in the criteria for record id put
[Forms]![NameOfForm]![record id]
This will just add the 4 record for the current record
on
the form

Jim

-----Original Message-----
i tried doing this, the big problem is that if you
enter
a
end date, you get a message stating it is going to
append,
but it appends all the fields that have a end date, so
it
duplicates the scheduled follow-up date. also, i don't
want it to give the message asking if they want to
append,
because if the user selects no, then this wont work
-----Original Message-----
If the client comes back don't you add a new record.
Everytime you change the program end date the 4
records
would be added.

You could run 4 append queries in a Macro
1st query would add a date using the program end date
+
1
month
ex in the query Follow-up
Date:[Forms]![NameOfForm]![Program End Date] + 30

2nd would add a date using the program end date + 2
month
Date:[Forms]![NameOfForm]![Program End Date] + 60

Add the name of the macro to the after update event
of
the
Program End date Control on the form


-----Original Message-----
this makes sense, how do i add the event, how does
the
event know to add 4 dates to the table? and if a
client
leaves 4 dates are added, but if the client comes
back
and
leaves again, will it add another 4 dates (i want it
to)
-----Original Message-----
I do the exact post monitoring. Since I need to
store
the
follow-up info I have done the following

Create a follow-up table with these fields
Date follow-up scheduled
Date of follow-up
follow-up method
follow-up comment

Add an afterUpdate event to the form with the
program-
end
date which adds the four follow-up records to the
follow-
up
table. The only information that is added is the
date
follow-up scheduled. The dates are calculated by
add
1
month, 3 month, 6 months and 1year to the program-
end
date.

Once you have the follow-up dates added to you
table
you
can create forms and reports.

This method has worked well for my users.

Jim


-----Original Message-----
i have a database that keeps track of clients that
start
and end a sepcific program (a client may choose to
leave
the program and start again in the same month).
tblclient
has client name address. tblclientdates has
startdate,
enddate. tblfollowup has follow-up date, follow- up
method
(method could be phone, letter or face2face). Each
time
a
client leaves the program, the manager has to do 4
follow-
up's (1 month, 3 months, 6 months and 1 year after
the
termination date).

I need to create a report that will show the
manager
who's
follow-up is coming up (based on the most recent
termination date). i don't know how to set this
up
because a follow-up date is not entered until it
has
been
done.

ex. John smith ended on 01/01/04

March's 1,3,6,12 month follow-up

john smith 03/01/04

any help is greatly appreciated :)
.

.

.

.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top