Query returning incorrect results

[monkeysee]

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?

 

[SkipVought]

Hi,

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.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

Monkeysee,

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.

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