SQL Help with Due Dates

Question

Hi,

I currently used this code in my query to find out the most recent date for a given employee.

1. SELECT Q.BookID, Q.BorrowerID FROM TableName As Q WHERE DateOut = (SELECT Max(T.DateOut) FROM TableName As T WHERE T.BookID = Q.BookID)

In this same query I have a field that displays the latest date for each employee plus 1 year (called DueDate). How can I somehow calculate if the dates (field called entrydate) are earlier or later than the duedate field?  The way it is setup, the duedate will always advance to 1 year with the new date entered into the entrydate field.  What makes it hard is that I can just have a field with specified due dates because the next due date for each employee will change to 1 year from the newly entered date.

Thanks

Answer 1

Ok.  This is what I have:

birthdate	duedate1	enterdate	newduedate
10/1/2008	10/1/2009	11/1/2010	11/1/2011
1/1/2008	1/1/2009	2/1/2010	2/1/2011
6/1/2008	6/1/2009	5/15/2010	5/15/2011
5/1/2008	5/1/2009	4/1/2010	4/1/2011

So first it starts off with the birthdate plus 1 year is the first due date.  Then as the user enters a date into the enterdate field, it generates a new due date based off of that date within the newduedate field.  As the years go on the user keeps entering in dates into the enterdate field and the code I previously posted filters this field to display only the most current enterdate per employee. I then need it to somehow check to see if it is late or early and by how many days.  I know that I can't just use the newduedate field as it will return the next years date.  Please advise me on how I can achieve the needed data.  Users can only enter data into the bolded field as the duedates are calculated fields.

THANKS

Answer 2

It sounds like you are trying to use the same record for multiple checkouts.  Wrong way to go.  Use a new record for each checkout with a date-out and date-in.

Make them return the book (show and tell) for a renewed checkout.

Answer 3

Sorry for the delayed response, but yes duedate is a calculated field.  It is the dateout field plus 1 year.  I need to somehow calculate the next dateout to see if it was taken out before the next due date.  The goal is to make sure that the book has been returned prior to the next due date, which is 1 year from the last time it was taken out. 

Example: If the dateout was 10/4/11 then the next duedate is 10/4/12.  So the next time the user enters a dateout date, I need to calculate how many days it is before or after this 10/4/12 date.  So say this next dateout date is 10/8/12 (4 days late).  Then the next duedate would be off this date and should display 10/8/13.   The reason I ask this is because it is currently setup to take the next duedate off the most current dateout + 1 year.  So if someone tried to look back at the previous dateout to see if that one was late/early (and how many days late/early), you won't be able to go off of the duedate as it will reflect the newer date.

Answer 4

Hi

Im having a hard time understanding your logic.

Are employees borrowing books and keeping them for a year?

Perhaps a more detailed description of what you  are doing will help.

Its likely that a bit of table fixing may simplify your problem.

cheers

PaulG

Answer 5

If your DueDate is an actual field the below should work for you --

SELECT Q.BookID, Q.BorrowerID, IIF([entrydate] > DueDate, "Earlier", "Later") AS [DueDate vs Entrydate] FROM TableName As Q WHERE DateOut = (SELECT Max(T.DateOut) FROM TableName As T WHERE T.BookID = Q.BookID);

If DueDate is a calculated field then you need to use the calculation in the query instead.