Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Query returning incorrect results

Query returning incorrect results

Query returning incorrect results

I am attempting to build a query that will give a dollar balance for medical services rendered. Ie: A client may return many times for services. The clients visit may have multiple services. The client may have different methods of payments for one date of service. In other words, one visit may show a personal payment plus an insurance payment. This is what I have so far.
Client Table with the Client demographic information; and the following:

Visit table has the following fields: VisitID (primary); ClientID; Date

Service Table has the following fields: ServiceID (primary); Description; Fee

Payment Table has the following fields: PaymentID;(primary); VisitID; Amount; Date

VisitDetail table has the following fields: DetailID; VisitID; ServiceID

When I build the query, the results show a payment occurring multiple times instead of once. Here is my query:

SELECT VisitDetails.VisitDetals, Service.ServiceTypeID, Visit.VisitID, Visit.ClientID, Payments1.PaymentAmount
FROM (Visit RIGHT JOIN (VisitDetails LEFT JOIN Service ON VisitDetails.ServiceID = Service.ServiceTypeID) ON Visit.VisitID = VisitDetails.VisitID) LEFT JOIN Payments1 ON Visit.VisitID = Payments1.VisitID
WHERE (((Visit.VisitID)=[Enter]));

What am I missing?

RE: Query returning incorrect results


Why left and right joins? I can see an outer join between visits and payments but not services. Each visit must have at least one service it would seem to me, but you might not have a payment.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query returning incorrect results


You should provide a few records from each of the tables and then try to show us how they should appear in the query. I expect one of the detail tables either VisitDetails or Payments will need to be grouped by VisitID.

Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close