Query returning duplicate records

[jennwilson]

Using Access 2000 - I have a query that is suppose to return the records from table [ Per Billable Log] within specified time range and find matching data from another table [ Clinician].

Table [Clinicians] houses Clinician name, location and rate information
Table [Per Billable Log] houses Clinician name, location and number of units for each type of service rendered on each day. The two tables are connected by field [ID]

Please see the code below

SELECT [Per Billable Log].Clinician, [Per Billable Log].Location, [Per Billable Log].Date, [Per Billable Log].EmployeeType, [Per Billable Log].[Multiple Locations], [Per Billable Log].[DOC Intake], [Per Billable Log].[DOC FT], [Per Billable Log].[DOC IT-S], [Per Billable Log].[DOC IT], [Per Billable Log].[DOC TESTING], [Per Billable Log].[DOMHA Intake], [Per Billable Log].[DOMHA FT], [Per Billable Log].[DOMHA IT], [Per Billable Log].[DOMHA TESTING], [Per Billable Log].[EAP Intake], [Per Billable Log].[EAP FT], [Per Billable Log].[EAP IT-S], [Per Billable Log].[EAP IT], [Per Billable Log].[EAP Training], [Per Billable Log].[TDPRS TESTING], [Per Billable Log].[TDPRS Intake], [Per Billable Log].[TDPRS FT], [Per Billable Log].[TDPRS IT-S], [Per Billable Log].[Staff Training], [Per Billable Log].GAP, [Per Billable Log].[TRDPRS IT], [Per Billable Log].ID, Clinician.[$DOC Intake], Clinician.[$DOMHA Intake], Clinician.[$EAP Intake], Clinician.[$TDPRS Intake], Clinician.[$DOC FT], Clinician.[$DOMHA FT], Clinician.[$EAP FT], Clinician.[$TDPRS FT], Clinician.[$DOC IT-S], Clinician.[$DOMHA IT-S], Clinician.[$TDPRS IT-S], Clinician.[$EAP IT-S], Clinician.[$DOC TESTING], Clinician.[$DOMHA TESTING], Clinician.[$EAP Training], Clinician.[$Staff Training], Clinician.[$GAP], Clinician.[$EAP IT], Clinician.[$DOMHA IT], Clinician.[$DOC IT], Clinician.[$TDPRS IT], Clinician.[TDPRS TESTING], [Per Billable Log].[DOMHA IT-S]
FROM Clinician, [Per Billable Log]
WHERE ((([Per Billable Log].Date) Between forms![Report Date Range]![Beginning Date] And forms![Report Date Range]![Ending Date]));

I want the query to search for records in the [per billable log] table within the specified range and then return that record 1 time only and search the [clinician table ] for the rate information and return that as well.

My end goal is to use the query to build a report that would list the clinician's name in alpha order with his/her rates in the header area and in the footer list the service dates and types of service rendered for each day, then summing. It is returning the same record from [per billable log] 150 or so times in the query of which is causing a slight problem.

Any help offered would be greatly appreciated. Thanks so much!

[HiTechCoach]

How are you joining the tables Clinician and [Per Billable Log]?

[jennwilson]

Per Billable Log.ProviderID joined to Clinician.ID

Does that answer your question?

[jennwilson]

After reading my initial posed questio.... I am left thinking you all have know idea what I am asking.

This is the problem ---- The query I built of which the code is listed above is running without any error messages, but it is returning the same exact record umpteen hundred times. I tried to change the group by and it is now returning 20 or so of the same exact record for the same date, same clinician when I run the query. I know that there is only one date for each clinician listed because the table is indexed to avoid duplicate entry for the same day.

Does that clarify my question? S.O.S. please help... I just cannot figure out why it not working.

Thanks again for any potential help or advice!

-Jennifer

[jennwilson]

I still need help with this issue.

As I am looking at the returned results more closely it is returning different information for the rates that are retrieved from clinician table. One individual doesn't even have rates listed in her record and in the 166 duplicate records returned it is showing various rates. Figuring that it is pulling it from the Clinician table, I must have something connected wrong, but don't know what.

Please help!!!!

Thank you in advance for any advice or right direction pointers.

-Jennifer

[jennwilson]

I figured out the issue. I didn't have the two ID Fields joined in the actual query. I only joined them in the table. The query is working fine now.