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.
Brant KerseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We'd have to know more about your table setup first.

How are you storing Payments? A Payment would be from a single source, and could be applied to one or many Charges. So I would presumably have a setup like this:

tblCharge
----------------------
ChargeID
ChargeDate
ChargeAmount
etc etc

You might also relate that Charge to a Visit, but that's beside the point - a Payment is made to a Charge (or Invoice, or something of that nature).

A Payment is a distinct item, and would be stored in it's own table:

tblPayment
-------------
PaymentID
PaymountDate
PaymentAmount
PaymentFrom
etc etc

Then, you'd "join" those two tables:

tblCharges_Payments
-----------------------------------
PaymentID
ChargeID
DatePosted
etc etc

So if I receive a Check for payments of Charges 3, 5, 9 and 10, I'd have:

1. A record inserted into the Payment Table
2. 4 Records inserted into the Charges_Payments table

From there, you can simply query the Payments table for your check number, and Join the Charges_Payments table to determine which Charges were paid with the Payment. You could further join the Visits table, Patients, etc tables to show more downstream information.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brant KerseyAuthor Commented:
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 .
Brant KerseyAuthor Commented:
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
Brant KerseyAuthor Commented:
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.
Brant KerseyAuthor Commented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.