Last Record

[eric333]

Hi Everyone,

I am working with an access control system. The system logs each time a staff member uses a card to access a door. The cards are stored in a Card table and the transactions are stored in the Transaction table.

The Card has an internal ID for the table and an external ID for the number that actually appears on the card. So, for the card that is printed with number 007250, the internal ID is 100. That intenral ID periodically changes, but is never duplicated. So, the internal ID for card 007250 could be 100, 101, 102, etc.

The Transaction table stores the data based on the internal card ID number.

I am attempting to identify the last time a card was used (to see if we have inactive cards). If there were not various internal IDs for each card, I could just use something along the lines of Maximum. But, that's not working for me.

Any ideas?

 

[eric333]

Unfortunately, that's not the case. All of the transactions are appearing int he detail section for each card, not just the last transaction.

I've checked to make sure my record selection formula and group selection formula are as specified in earlier postings.

I've placed both the Transaction field from the TransactionLog table, and the SQL Expression Field in the details section, but neither worked.

I'm sorry to continue to trouble you with this.

 

[lbass]

Please post your current record selection formula and your current group selection formula.

-LB

 

[eric333]

Okay, I'm an idiot. I was not using the correct Group selection formula. Wow. How embarassing.

So, of course, the data looks good. Thank you, thank you, thank you!

I've added a calculation to show the number of days since the card was last used and am including only cards and added a select statement to identify cards that have not been used for more than 30 days.

Due to the groupings, I am unable to sort the report by last name. I suppose I could export the contents to an Excel file and then perform the sort.

 

[lbass]

You can do a group sort. Insert a maximum on the name at the group level that you want sorted by name. Then go to report->group sort->maximum of name->descending (i think).

-LB

 

[eric333]

Wow. All I can say is wow. That worked! Thank you lbass SO much for your patience and help with this. I am indebted to you.