Help choosing most recently dated record in an Access table
I don't know if I'm missing something basic or if I've set the tables up wrong in the first place.
I need to choose the most recent status record from the tblProviderStatus for each combination of AssignmentID & ProviderID.
It works until I add the field for Status (either ID or Code or Description - any of them). Then, it doesn't work anymore. I get all dates for each combination of ProviderID and AssignmentID instead of only getting the most recent one.
I don't know if the problem is in my table design, in my table relationships or simply in my query design.
Any ideas?
Day-Tracker-v6.accdb
I need to choose the most recent status record from the tblProviderStatus for each combination of AssignmentID & ProviderID.
It works until I add the field for Status (either ID or Code or Description - any of them). Then, it doesn't work anymore. I get all dates for each combination of ProviderID and AssignmentID instead of only getting the most recent one.
I don't know if the problem is in my table design, in my table relationships or simply in my query design.
Any ideas?
Day-Tracker-v6.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pawan, that worked. Thank you for such a quick solution!!!
Welcome !!
ASKER
Crystal, thank you for the advice. I will certainly keep it in mind.
right-click on the link line joining Assignments to Provider Status and edit the join properties. Choose to show all records from Assignments, and only those from ProviderStatus where they match.
Do the same for the join line between ProviderStatus and Status -- all from ProviderStatus. Same for Providers. Watch the arrows -- they will point to the table that can be dropped.
For the problem with ID... Do you mean OrderID? Be sure that the DefaultValue for OrderID in Assignments is not set to 0, as Access does for you (which is usually not desired!) -- this, most likely, won't match anything (and also indicates that you did not define relation ships with referential integrity)
don't feel bad about not doing things that need to be done ... Access is powerful and, because so, can be hard to learn.
Do these things and then come back with your questions ~