Help with "WHERE" criteria

[zozew]

Hi All, I have a problem i can't solve with this SQL string (below). What it does is a recordset of people (Names and some other info) based on a date .

My problem is that on a specific date there can be multiple values of the same person (InmateID in tblIbmateHearings) and in my result i only want to display a unique value of the person disregarding if there is multiple entries in tblIbmateHearings of that person (InmateID in tblIbmateHearings).

So i cant figure out the WHERE criteria...right now it only checks for the date (in red Below).

How would i add a Unique InmateID check for the table tblInmateHearing?



Maybe i need to change the structure??

SELECT DISTINCT tblBranches.Branch, tblBranchEscorts.Branch, tblInmateCases.CaseNo, tblInmatesProfile.InmateID, tblInmatesProfile.LastName, tblInmatesProfile.FirstName, tblInmatesProfile.MiddleName, tblInmatesProfile.CellLocation
FROM tblEscortsName INNER JOIN ((tblBranches INNER JOIN tblBranchEscorts ON tblBranches.BranchID = tblBranchEscorts.Branch) INNER JOIN (tblInmatesProfile INNER JOIN (tblInmateCases INNER JOIN tblInmateHearings ON tblInmateCases.CaseNumberID = tblInmateHearings.CaseNumberID) ON tblInmatesProfile.InmateID = tblInmateCases.InmateID) ON tblBranches.BranchID = tblInmateCases.Branch) ON tblEscortsName.EscortID = tblBranchEscorts.EscortID
WHERE (((tblInmateHearings.Hearings)=[COLOR=Red][B][Forms]![scheduleChooserForm]![dateForSchedule][/B][/COLOR]));

 

[RuralGuy]

WHERE (<condition1>) AND (<condition2>) AND (<condition3>)

 

[zozew]

ok I get that you can use "AND" for more criteria, but is there a way of specifying a UNIQUE value for a field? or to specify that i only want one instance of (InmateID) value if there are more then one...

 

[RuralGuy]

You may be able to utilize SELECT TOP 1 to limit the selection.

 

[zozew]

Hi again let me re-explain my problem better....

BASE:
first table (tblInmatesProfile) is a person table with names adress etc. Second table is a table with cases (tblInmateCases) with caseNo, Branch, crime etc. that can hold Multiple cases for each person (linked by InmateID from the first table). Third table is a date table (tblInmateHearings) that can hold multiple dates for each Case in the second table (linked by the second tables CaseID)

I have a query for a specific date picking out all persons with hearings at a specific date.

The result is grouped (in my report) by tblInmateCases.Branch. So i get a result where i have a bunch of names (INmateID's) grouped by what branch they have in tblInmateCases if they have a date corresponding to the date im running the query on. So i can get the same InmateID repeated under the Branch grouping if that InmateID has CaseID's with the same Branch and Dates.

So i would like the result to display only ONE InmateID per Branch grouping even if that InmateID might have 20 CaseID's with the same Branch on the specific date.

Question:
Where do i do this filtering/removing in the WHERE criteria of the SQL? And how would it look?