Arg81
Well-known member
Hey guys,
Here's a quick table layout;
Parent Table
ID No
Child Table
ID No, Rev No, RequiredDate
Basically the child table will have matching ID No, and then Rev No would be 1, 2, 3, 4 etc etc to infinity (normally 10!!) - this is programmatically assigned (looks at the last Rev No for that ID No, then + 1 to it)
What I need to do, is select all ID Numbers from the parent table that have a RequiredDate < next 7 days for the latest Rev No (the highest).
This is so I can create a popup to the user to tell them what ID Numbers need working on as their RequiredDate is less than a week away.
Not sure how to properly write this out, I'm hoping it can be done in one simple SQL query, so that the results look like;
ID No RequiredDate
27 - 10/01/07
54 - 12/01/07
135 - 14/01/07
The issue I have at the moment when trying to do this, is that all previous Rev No for the ID have a requiredDate, but obviously I need the latest and only the latest Rev No, and all other Rev No for that ID to be ignored....
Any help much appriciated, thanks.
Luke
Here's a quick table layout;
Parent Table
ID No
Child Table
ID No, Rev No, RequiredDate
Basically the child table will have matching ID No, and then Rev No would be 1, 2, 3, 4 etc etc to infinity (normally 10!!) - this is programmatically assigned (looks at the last Rev No for that ID No, then + 1 to it)
What I need to do, is select all ID Numbers from the parent table that have a RequiredDate < next 7 days for the latest Rev No (the highest).
This is so I can create a popup to the user to tell them what ID Numbers need working on as their RequiredDate is less than a week away.
Not sure how to properly write this out, I'm hoping it can be done in one simple SQL query, so that the results look like;
ID No RequiredDate
27 - 10/01/07
54 - 12/01/07
135 - 14/01/07
The issue I have at the moment when trying to do this, is that all previous Rev No for the ID have a requiredDate, but obviously I need the latest and only the latest Rev No, and all other Rev No for that ID to be ignored....
Any help much appriciated, thanks.
Luke