I have a visits table and a relational payments table. any number of payments, check or cash or echeck, etc could be made against the relational visit. grandpa could even call in and make a payment on the visit. What I am trying to do is select all the information about the visit, but only on visits where there is a check# = X. (and that CHECK could be from an insurance company that pay for 50 patients at a time so the check# is NOT distinct to a visit. I want an sql query (i am finding group By a little difficult to grasp) where I get: I want the visits, but only the unique visits, where that friggin check could have been made on. On my form I display the visits of a patient, and then link the payments subform to it to show ALL payments made to that visit. But I want to search all distinct patient visits where a large insurance company check has been applied(they will pay hundreds of visits in a single large check but with a certain CHECK#.(and payments from other sources could be made against that single visit)
so if i can search by that check#, i want to display only the distinct visits that could all relate to that single check. then on the form, those visits have a table and then I link all payments ever made toward that VISIT using a simple linked subform.