Relationships

[funkygoorilla]

Hi,


I need to make a query that pulls data from two different tables according to a start date of when the records were entered, and an end ate of the last record entered. But I just cant get the relationships right so it pulls the wrong data and sometimes duplicate. If I upload a example would anyone be willing to take a look?
FunkyG.

[RuralGuy]

How about starting with the SQL you have tried and maybe someone will see how to help.

[funkygoorilla]

Ok, good suggestion. Here is the SQl view;

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

[A S MANN]

The SQL statement shows Three Table
1:tblCalls
2:tblShiftInfo
3:tblCallType
Your Query shows these table are simply Join (Inner Join) but no relationship between them.
1:You need to Define Relationship betwen Tables as 1-1 , 1-many, many-many etc.
that need DATA in table and how these data are related to data in other table.
2:Redifine your table in your query. ( Delete and add table) Modify the fields again.
That should show the correct DATA in table
Hope that will solve your issue.