Next available date 1

[Deano1976]

Access v2000

I have a table that contains site assessment details including the date assessed and the assessor:

Site 1 Assessor 2 01/02/2005
Site 2 Assessor 1 05/02/2005
Site 3 Assessor 1 03/02/2005

I also have a table that contains future site re-assessment details including the date assessed and the assessor:

Site 1 Assessor 2 20/02/2005
Site 2 Assessor 1 21/02/2005
Site 3 Assessor 1 25/02/2005

I want to be able to append to the future site re-assessment table a new site, Assessor 1 or 2 (depending on who is selected) and then the next available, non weekend date, for that assessor.

So i guess what I am really saying is that none of the tables can have have the identical assessors with identical dates.

My main problem is working out the next available non weekend date for the assessor selected in my append query - how do I get that to work?

 

[JonFer]

Write a query that gives you the maximum assess date for an assessor. Create a sequence table with 3 rows in it (values 1, 2, and 3). Add both of these to a query without any joins and build a field:

DateAdd("d",[Sequence],[AssessDate])

Add a criterion using WeekDay() to prevent Saturday and Sunday dates. Get the minimum date left. Instead of the first query, you can alternatively use the original table of assess dates by adding a criterion on the assess date:

(Select Max(AssessDate) from YourTable as A where A.Assessor=YourTable.Assessor)

You can also build an IIf function that checks the WeekDay result of adding 1, 2, and 3 to the maximum assess date.

John

http://www.interautomateconsulting.com