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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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.
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.
ASKER
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...
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...
ASKER