filter last record for each Guest and display in report

[Aspen77]

Hello, I need assistance, I am trying to display in a report the last record for each guest based on Date restriction is over. Following is the table structure and a sample of data:

Guest Admittance Status Table
ID
Guest Name
Date Restricted
Date Restriction is over
Admittance Status
Restricted Reason

1
Smith, Joe
09/01/12
09/05/12
DNR
Drugs

1
Smith, Joe
09/07/12
09/14/12
PERM DNR
Violent

2
Nelson, Sally
09/07/12
09/14/12
DNR
Drinking

3
Doe, Jane
09/12/12
09/20/12
PERM DNR
Violent

What I would like to display in the report is:
Group by Admittance Status, the last record for each guest based on Date restriction is over. (Although Joe Smith has multiple records only the last record is displayed)

GROUP PERM DNR Date Restriction is Over
Smith, Joe 09/14/12
Doe, Jane 09/20/12

GROUP DNR
Nelson, Sally 09/14/12

Any assistance is greatly appreciated. Thank you!

 

[Aspen77]

Also, I want to exclude the Admittance status that is equal to Clear. Thank you!

 

[Aspen77]

I got it, I was using the dmax instead of using the last function.

Next question is there a way to make a status the default but only for new records. I have tried the default value on the table and row source but it adds for all records.

Any assistance is greatly appreciated. Thank you!

 

[MacroScope]

I'm not sure I understand your question. A default value will always be inserted into a control in the absence of any other value. Why not use the default value at the control instead of table level?

 

[Aspen77]

Hello,

Using the Guest Admittance Status Table described above, is there a way that once the "Date Restriction is over" is less than the current date a new record is added to the Table for that guest with the Admittance Status of clear and the Date Restriction is over to a future date such as 04/30/2013?

Thank you!