Windows 7 32bit 
Access 2010 32bit
Hey there guys, first time poster. I recently started a new job where one of the first tasks they put on me was to build a fairly complex (to me) database.
One of the current issues I'm struggling with is in my report I have these fields:
Company Name -> linked field
Appointment Date -> mm/dd/yyyy hh:mm AM/PM
Accepted? -> check box
Lender -> linked field
Days Out -> calculated field based on the rounded absolute value of appt date - system date.
The purpose of the report is to identify apointments that lenders have not confirmed sorted and colored by proximity to system date. I got everything working the way I like it except the sorting part.
The goal is to have it sort by the accepted box first. If it's not checked it needs to be up top. Then I would like those (unchecked and ONLY unchecked) records sorted by appointment date oldest to newest. Then I'd like the checked records at the bottom, but I would like their records sorted by newest to oldest.
The reasoning for this is two-fold.
1) If the appointment is not confirmed it's imperative to know why and to have the ones coming soonest prioritized.
2) If the appointment is confirmed, but upcoming I would like those records shown ideally right below the furthest away, to keep track of them and make sure nothing is missed.
3) Lastly if the appointment is confirmed and in the past it's not a priority any longer. The goal for this piece as I begin to manually populate the database with the 500 or so records is to basically hide them once system date passes appointment date. I can tackle that piece when I get there though, unless someone has a solid idea or can point me in the right direction.
Please bear in mind I am a novice with databases. Everything I have built and discovered was through trial and error and various forum posts helping people with similar questions. (Note: I couldn't find ANYTHING on this issue hence the post). I do not know much code.
I thought I could just set 2 groups with 2 different sorts, but the sort/group feature in 2010, doesn't quite work that way. it's like a straight down hierarchy. 1->2->3->4->etc. I was hoping for like a 1->a->2->b kind of setup.
See attached for a picture of what i am working with and kind of what I am going for. I appreciate any and all assistance and apologize for the wall of text.
Reply With Quote
