Link to home
Start Free TrialLog in
Avatar of Brant Kersey
Brant Kersey

asked on

relational DB and group by

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.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brant Kersey
Brant Kersey

ASKER

the form will show all payments against the visit#..thanks to subform links. but the problem is selecting the main form info..I want visit#, name, paid, charge , etc(all fields in the visits table) to show but only want the distinct payments.visit# to tabulate the rst. if i can get the distinct payments.visit# against the constraint check#= cboCheck# then the main form will show the visits.visit# and the subform will show the payments.visit# where the check#= cboCheck#. GROUP BY is hard to understand...and im stuggling with embedded select statements .
btw, i have visits table which are identity and then payments table linked to it. the payments could come from anywhere. i want my office manager to be able to enter a check# and then search for all related visit# where that payments.[check#] could apply. then the main form would show distinct visits.visit# where that check applied and the subform would show all payments.check# which she could then edit
I know the earlier posts were probably confusing :) but the answer turned out to be simple, I just didnt understand how GROUP BY worked very well. I had to play with it until I figured how it worked. this was my fix:

       Set rstSort = GrabRst("SELECT Visits.[Visit#], Visits.MRN, Visits.Wholename, Visits.[Date], Visits.Charge, Visits.Paid, Visits.AmtAllowed, Visits.Responsibility, Visits.InvoiceMessage, " & _
            "Payments.[Visit#] AS expr1, SUM(Payments.Amount) AS expr2 FROM dbo.Visits INNER JOIN dbo.Payments ON Visits.[Visit#] = Payments.[Visit#] GROUP BY Visits.[Visit#], Visits.MRN, " & _
            "Visits.Wholename, Visits.[Date], Visits.Charge, Visits.Paid, Visits.AmtAllowed, Visits.Responsibility, Visits.InvoiceMessage, Payments.[Visit#], Payments.[Check#] HAVING " & _
            "(Payments.[Check#] = '" & cboCheck & "') ORDER BY visits.[date] DESC")

so whenever my user puts a check # in I populate the visits subform with these distinct visit# visits, and then the payments subform is linked, so all payments against these visit are displayed and, of course, include all the ones that came from that check plus others like cash, credit card, etc.
I've requested that this question be closed as follows:

Accepted answer: 0 points for Brant Kersey's comment #a40781386

for the following reason:

i solved my own problem before and expert got to it...