Sorting Problem

[luism]

I am having a sorting problem that I can't figure out.

I have table of Contacts and a table of Contracts

Each Contact can have multiple contracts, most of which are renewed but we like to keep tract of old contracts so we keep them.

I created a Search interface that allows us to search our contacts by name, address, zip and phone number and displays such data + Contract Date and an email.

My issue is that the Contract Ends Dates that are showing up are all wrong, I would like it to display the most recent/latest expiring or even if its expired but the latest Contract End Dates. Right now, there are Contacts that have current Contracts that will end within months but the interface is showing that they are already expired, this is a bug we want to fix.

Any help would be appreciated.

 

[MStef]

Hello Louis M !
As I can understand you, you need something like this (Attachment, zip).
I added a Subform, I change Default view (Single form).
Look at RECORD SOURCE in "Form_SearchAllContacts", also look at in
"frmContractSubform", Look at RELATIONSHIPS, Look at "Link child Fields"
and "Link Master Fields" properties (in subform).
Open form "Frm_SearchAllContacts" and see.
I think it is what you need.

 

[luism]

Hi mstef and thank you for responding.

Unfortunately this takes away the ability to search by name, address, zip and phone number which is essential to our needs.

I forgot to add that I also need to display data for users that do not have any contracts. So I am re-attaching my DB. With some more data to show what I'm looking for.

Essentials Needed

1) Search by name, address, phone, zip
2) Find all contacts, with and without contracts. And show "None" if no contract (as attached is showing)
3) Sort/grab only the MOST RECENT contract date that's ending anytime after today or even if it did expire but it has to be the most recent (last)

So far I have 1 and 2, my issue is the contract date. It is grabbing the incorrect one, showing that the contact has an expired contract when in fact the person doesn't.

I was able to grab all correct dates when I did an INNER JOIN and Group By LastofDateEnds but the results omitted people that didn't have contracts, which disabled us from searching our entire database. So I don't know what else to do.

Any help is greatly appreciated!