Modifying data between records

[Guest]

I have a form (DateofService) which contains fields SSN, Date, NextRefillDate and others that don't apply to the question. Everytime a patient is seen a DateofService form is filled out. I am having a problem making sure that the NextRefillDate field is consistent between records (DatesofService).

For example, on Date of Service of 4/1/04 the patient's NextRefillDate was set to 5/1/04. Sometimes, the patient may need to come in before the NextRefillDate so a new DateofService is created say on 4/15/04 with an edited refill date of 6/1/04 instead of 5/1/04. At the same time, I have a query that pulls the patients that are due for a refill within a certain span of dates. If I run a query for patients with refill dates from 5/1/04 to 6/1/04 that patient will obviously be shown twice, which is redundant seeing how the first date of 5/1/04 has been changed. So, without going back and modifying the previous DateofService record manually and editing the 5/1/04 refill date to 6/1/04 is there a way to have access modify the NextRefillDate in the previous DateofService record to equal the latest NextRefillDate?

Somehow, I need to get Access to make sure that the NextRefillDate of the previous DateofService matches the one that I have just created...and if it doesn't either cue the operator to change the date or have access change it automatically. In the example above, 5/1/04 doesn't equal 6/1/04

Any help would be appreciated! Sorry about the wordy question.

 

[DDM]

Clueless, take a completely different approach. Just take the query you
describe below and make it a Totals query. Include three fields: PatientID,
NextRefillDate, and NextRefillDate (again). Group by PatientID and set the
first NextRefillDate to "Last." Set the second NextRefillDate field to
"Where" and under criteria enter "Between 5/1/04 and 6/1/04." Run the query
to get a list of patients with refill dates in that period but showing only
the latest (=current) refill date.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I have a form (DateofService) which contains fields SSN, Date,

NextRefillDate and others that don't apply to the question. Everytime a
patient is seen a DateofService form is filled out. I am having a problem
making sure that the NextRefillDate field is consistent between records
(DatesofService).

For example, on Date of Service of 4/1/04 the patient's NextRefillDate was

set to 5/1/04. Sometimes, the patient may need to come in before the
NextRefillDate so a new DateofService is created say on 4/15/04 with an
edited refill date of 6/1/04 instead of 5/1/04. At the same time, I have a
query that pulls the patients that are due for a refill within a certain
span of dates. If I run a query for patients with refill dates from 5/1/04
to 6/1/04 that patient will obviously be shown twice, which is redundant
seeing how the first date of 5/1/04 has been changed. So, without going back
and modifying the previous DateofService record manually and editing the
5/1/04 refill date to 6/1/04 is there a way to have access modify the
NextRefillDate in the previous DateofService record to equal the latest
NextRefillDate??

Somehow, I need to get Access to make sure that the NextRefillDate of the

previous DateofService matches the one that I have just created...and if it
doesn't either cue the operator to change the date or have access change it
automatically. In the example above, 5/1/04 doesn't equal 6/1/04.