Results from query are duplicated

[funkygoorilla]

Hi,
I created a query to pull data from two tables with two parameters. When I run the query, it returns the correct results, but times three. Call it triplicate results if you will. I cant figure out. Can it possibly be a relationship issue?
FunkyG

[jzwp11]

Can it possibly be a relationship issue?

More than likely.

Could you provide the SQL text of the query?
What is the relationship between the 2 tables involved?

[funkygoorilla]

Hi, I think I figured it out. But, I don't understand why it would give me those results in the first place? it was a relationship that was not linked correctly.
Thanks anyway.

[jzwp11]

I'm glad to hear that you got it worked out. If you could provide the SQL text of the original query, we could take a look at it and offer an explanation to help you better understand why it returned the duplicate results.

[funkygoorilla]

Hi, here is the SQL for the query.

SELECT tblCalls.[Time Received], tblCalls.[Time Arrived], tblCalls.[Time Cleared], CDate(Left((Right("0000" & [Time Received],4)),2) & ":" & Right((Right("0000" & [Time Received],4)),2)) AS RecTime, CDate(Left((Right("0000" & [Time Cleared],4)),2) & ":" & Right((Right("0000" & [Time Cleared],4)),2)) AS ClearTime, IIf([Time Cleared]<[Time Received],1440+DateDiff("n",[rectime],[cleartime]),DateDiff("n",[rectime],[cleartime])) AS ElapsedTime, tblCalls.[Seniority ID], tblCalls.[Dispatch Date and Time], tblCalls.Location, tblCalls.Source, tblCalls.Activity, tblCalls.Disposition, tblCalls.[Criminal Reports], tblCalls.[Traffic Citation #], tblCalls.[Misd Arrests], tblCalls.[FI's Issued], tblCallType.[Call Type], tblCalls.[Felony Arrests], tblCalls.[Other Reports], tblCalls.[DUI Arrests], tblShiftInfo.[Vehicle Number], tblShiftInfo.District, tblShiftInfo.[Starting Mileage], tblShiftInfo.[Ending Mileage], tblShiftInfo.[Beginning Shift Date], tblShiftInfo.Watch, tblShiftInfo.[Team Number], tblShiftInfo.[Duty Type], tblShiftInfo.[Ending Shift Date]
FROM (tblShiftInfo INNER JOIN tblCalls ON tblShiftInfo.[Seniority ID] = tblCalls.[Seniority ID]) INNER JOIN tblCallType ON tblCalls.[Call Type] = tblCallType.[Call Type ID]
WHERE (((tblCalls.[Seniority ID])=[Please enter your seniority ID:]) AND ((tblCalls.[Dispatch Date and Time]) Between [Please enter your shift start date:] And ([tblCalls].[Dispatch Date and Time])=Date()));

I need to be able to use a start date and an end date to pull all data that was input between those dates. Hopefully this makes sense.
FunkyG

[jzwp11]

WHERE (((tblCalls.[Seniority ID])=[Please enter your seniority ID:]) AND ((tblCalls.[Dispatch Date and Time]) Between [Please enter your shift start date:] And ([tblCalls].[Dispatch Date and Time])=Date()));

The BETWEEN section should be like this (I left out all of the parentheses Access put in for easier reading):

tblCalls.[Dispatch Date and Time] BETWEEN [Please enter your shift start date:] AND Date()

Are you still getting duplicate results returned from the query? If so, that might have to do with the FROM clause. One option is to create a query that shows all results and then use that as a basis for a second query that does the date and seniority ID filtering.

For example do this query first

qryCallLog

SELECT tblCalls.[Time Received], tblCalls.[Time Arrived], tblCalls.[Time Cleared], CDate(Left((Right("0000" & [Time Received],4)),2) & ":" & Right((Right("0000" & [Time Received],4)),2)) AS RecTime, CDate(Left((Right("0000" & [Time Cleared],4)),2) & ":" & Right((Right("0000" & [Time Cleared],4)),2)) AS ClearTime, IIf([Time Cleared]<[Time Received],1440+DateDiff("n",[rectime],[cleartime]),DateDiff("n",[rectime],[cleartime])) AS ElapsedTime, tblCalls.[Seniority ID], tblCalls.[Dispatch Date and Time], tblCalls.Location, tblCalls.Source, tblCalls.Activity, tblCalls.Disposition, tblCalls.[Criminal Reports], tblCalls.[Traffic Citation #], tblCalls.[Misd Arrests], tblCalls.[FI's Issued], tblCallType.[Call Type], tblCalls.[Felony Arrests], tblCalls.[Other Reports], tblCalls.[DUI Arrests], tblShiftInfo.[Vehicle Number], tblShiftInfo.District, tblShiftInfo.[Starting Mileage], tblShiftInfo.[Ending Mileage], tblShiftInfo.[Beginning Shift Date], tblShiftInfo.Watch, tblShiftInfo.[Team Number], tblShiftInfo.[Duty Type], tblShiftInfo.[Ending Shift Date]
FROM (tblShiftInfo INNER JOIN tblCalls ON tblShiftInfo.[Seniority ID] = tblCalls.[Seniority ID]) INNER JOIN tblCallType ON tblCalls.[Call Type] = tblCallType.[Call Type ID]

Then this

SELECT *.qryCallLog
FROM qryCallLog
WHERE (qryCalLog.[Seniority ID]=[Please enter your seniority ID:]) AND (qryCallLog.[Dispatch Date and Time] Between [Please enter your shift start date:] AND Date())

[funkygoorilla]

hi thanks for looking into my query for me. I think you are right by splitting up the queries. My only issue with that is that I will need to prompt the use for a lot of criteria, which may wind up being tedious. Do you know if it is possible to use a form, that users can put selections into, and in the background, that form is actually running a query with the users selections? I will then need the query to display the information in a report. Does that make sense?
FunkyG

[jzwp11]

Yes, you can use an unbound (not bound to a table or query) form with textboxes to accept the criteria. You would base the form on the query (less the WHERE clause). You would then use a button on that form to open the report. You would use the criteria entered to filter the report as it opens.