Filtering by increments of 4

[Guest]

I have an appointment database diary whereby I have allocated attendance
dates for my clients at varying periods in the future. I have now been
tasked with having to call these customers every 4 (working) days prior to
our attendance.

How do I set up my system so that it filters (by whatever appropriate means)
and subsequently displays which customer i need to contact every 4 days.

e.g. Customer A booked in for 20th April 2007. I will need to contact this
customer every 4 working days to let them know current position of works etc
i.e. starting from today need to call on the 10/04/07 and 16/04/07

Customer B booked in for 27th April 2007. Need to cntact on 10/04/07,
16/04/07, 20/04/07 and 26/04/07.

Your help in this matter is, always, most appreciated.

Thanking you in anticipation.

PMK

 

[Allen Browne]

1. Create a new table, containing just one field named CountID, of type
Number (Long Integer). Mark the field as the primary key (toolbar icon).
Save the table as tblCount.

2. Enter the records into this table manually, or use the MakeDate()
function in this link to enter the records for you:
http://allenbrowne.com/ser-39.html

3. Create a query that contains both this table and the table containing
your appointments. If you see any line joining the two tables, delete it. It
is the lack of a join that gives you a record for each combination. This is
known as a Cartesian Product.

4. Type an expression like this into a fresh column in the Field row
CallDate: DateAdd("d", -[HowOften], [AppointmentDate])
This assumes you have a field named HowOften that contains the 4 (for
calling every four days), and a field named AppointmentDate that contains
the 20th April 2007 in your example.
To exclude old dates, enter critiera under this field of:

= Date()

The query now has a record for every 4 days up to the appointment date.

 

[Guest]

Many thanks Allen, I will give that a try

kind regards

PMK

1. Create a new table, containing just one field named CountID, of type
Number (Long Integer). Mark the field as the primary key (toolbar icon).
Save the table as tblCount.

2. Enter the records into this table manually, or use the MakeDate()
function in this link to enter the records for you:
http://allenbrowne.com/ser-39.html

3. Create a query that contains both this table and the table containing
your appointments. If you see any line joining the two tables, delete it. It
is the lack of a join that gives you a record for each combination. This is
known as a Cartesian Product.

4. Type an expression like this into a fresh column in the Field row
CallDate: DateAdd("d", -[HowOften], [AppointmentDate])
This assumes you have a field named HowOften that contains the 4 (for
calling every four days), and a field named AppointmentDate that contains
the 20th April 2007 in your example.
To exclude old dates, enter critiera under this field of:

= Date()

The query now has a record for every 4 days up to the appointment date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an appointment database diary whereby I have allocated attendance
dates for my clients at varying periods in the future. I have now been
tasked with having to call these customers every 4 (working) days prior to
our attendance.

How do I set up my system so that it filters (by whatever appropriate
means)
and subsequently displays which customer i need to contact every 4 days.

e.g. Customer A booked in for 20th April 2007. I will need to contact
this
customer every 4 working days to let them know current position of works
etc
i.e. starting from today need to call on the 10/04/07 and 16/04/07

Customer B booked in for 27th April 2007. Need to cntact on 10/04/07,
16/04/07, 20/04/07 and 26/04/07.

Your help in this matter is, always, most appreciated.

Thanking you in anticipation.

PMK