query duplication problem

[Guest]

I have been given the dubious task of developing reports from a db that
someone else did. Unfortunately, there is very little (if any) table
structure to the db. In a query that I am doing I get duplicate values if
the person's name is repeated for a difference discipline, when they should
not be duplicating. I am puzzled why it is occuring. The tables do not have
the duplicate data.

If anyone has thoughts as to why it is occurring, please let me know.

I am posting the SQL statement below.

SELECT
ResiDetail.[O-R],
ResiDetail.Week,
ResiDetail.[Pat #],
ResiDetail.[Pat Last Name],
ResiDetail.[First Name],
ResiDetail.Disability,
ResiRxFreq.Team,
ResiDetail.Therapy,
ResiDetail.Appt,
Count(ResiDetail.Appt) AS [#Appt],
ResiRxFreq.[PT-Clinic],
ResiRxFreq.[PT-FES Bike],
ResiRxFreq.[PT-Pool]

FROM ResiDetail INNER JOIN ResiRxFreq ON ResiDetail.[Pat
#]=ResiRxFreq.[Client #]

GROUP BY
ResiDetail.[O-R],
ResiDetail.Week,
ResiDetail.[Pat #],
ResiDetail.[Pat Last Name],
ResiDetail.[First Name],
ResiDetail.Disability,
ResiRxFreq.Team,
ResiDetail.Therapy,
ResiDetail.Appt,
ResiDetail.Appt,
ResiRxFreq.[PT-Clinic],
ResiRxFreq.[PT-FES Bike],
ResiRxFreq.[PT-Pool]

HAVING (((ResiDetail.[O-R])="R") AND ((ResiDetail.Therapy)="PT"))

ORDER BY ResiDetail.[Pat Last Name];

Try not to be too judgemental on the naming structure.

 

[Guest]

Hi,

Judgemental? I understand completely! Been there.

My guess is the neither ResiDetail.[Pat #] nor ResiRxFreq.[Client #] are
primary key fields. If neither are unique, you could get the Cartesian
product.

You could try changing it to SELECT DISTINCT to see if the dupes go away;
however, the GROUP BY should already take care of that.

It's possible that there's a linking/bridging/joining table already in the
database that needs to be in the query between the two tables.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have been given the dubious task of developing reports from a db that
someone else did. Unfortunately, there is very little (if any) table
structure to the db. In a query that I am doing I get duplicate values if
the person's name is repeated for a difference discipline, when they should
not be duplicating. I am puzzled why it is occuring. The tables do not have
the duplicate data.

If anyone has thoughts as to why it is occurring, please let me know.

I am posting the SQL statement below.

SELECT
ResiDetail.[O-R],
ResiDetail.Week,
ResiDetail.[Pat #],
ResiDetail.[Pat Last Name],
ResiDetail.[First Name],
ResiDetail.Disability,
ResiRxFreq.Team,
ResiDetail.Therapy,
ResiDetail.Appt,
Count(ResiDetail.Appt) AS [#Appt],
ResiRxFreq.[PT-Clinic],
ResiRxFreq.[PT-FES Bike],
ResiRxFreq.[PT-Pool]

FROM ResiDetail INNER JOIN ResiRxFreq ON ResiDetail.[Pat
#]=ResiRxFreq.[Client #]

GROUP BY
ResiDetail.[O-R],
ResiDetail.Week,
ResiDetail.[Pat #],
ResiDetail.[Pat Last Name],
ResiDetail.[First Name],
ResiDetail.Disability,
ResiRxFreq.Team,
ResiDetail.Therapy,
ResiDetail.Appt,
ResiDetail.Appt,
ResiRxFreq.[PT-Clinic],
ResiRxFreq.[PT-FES Bike],
ResiRxFreq.[PT-Pool]

HAVING (((ResiDetail.[O-R])="R") AND ((ResiDetail.Therapy)="PT"))

ORDER BY ResiDetail.[Pat Last Name];

Try not to be too judgemental on the naming structure.