I am building a database to keep track of when equipment needs to be serviced and my format is something like this:
ID - Date ID - Equipment - Date Serviced - Date Due
1 ------- 1 ----- Item 1 --------- 1/1/09 --- 1/1/10
1 ------- 2 ----- Item 1 --------- 1/1/10 --- 1/1/11
2 ------- 3 ----- Item 2 --------- 2/2/09 --- 2/2/10
2 ------- 4 ----- Item 2 --------- 2/2/10 --- 2/2/11
I am trying to develope a query that would tell me all the items that are due, closest date first. My problem is I don't want I only want the newest due date for each item, so the previous years data would not show up. So my query for the items above would look like:
ID - Date ID - Equipment - Date Serviced - Date Due
1 ------- 2 ----- Item 1 --------- 1/1/10 --- 1/1/11
2 ------- 4 ----- Item 2 --------- 2/2/10 --- 2/2/11
I understand that it would be easier not to hold on to previous years dates but I need to keep them in there for our records. The only queries I've been able to build so far show me every single record. Does anyone have any idea on how I could do this?