Subquery Q.

[zozew]

Base:
3 tables linked like this:

Data Table 1 (tblInmatesProfile):
InmateID, Name, etc

Data Table 2 (tblInmateCases):
CaseID, InmateID, Branch, Crime, Etc

Data Table 3 (tblInmateHearings)
CaseID, HearingID, Hearings

I have a query that returns a recordset for a specific date (Hearings)

Right now i get this data with my query for the date 1/1/2011:
(just showing the important fields)

InmateID:1, Name:Ben, CaseID:1, Branch:25, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:2, Branch:25, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:3, Branch:25, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:4, Branch:25, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:5, Branch:25, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:6, Branch:25, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:7, Branch:30, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:8, Branch:30, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:9, Branch:30, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:10, Branch:30, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:11, Branch:30, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:12, Branch:30, Hearings:1/1/2011
InmateID:2, Name:John, CaseID:13, Branch:25, Hearings:1/1/2011
InmateID:2, Name:John, CaseID:14, Branch:25, Hearings:1/1/2011
InmateID:2, Name:John, CaseID:15, Branch:25, Hearings:1/1/2011
InmateID:2, Name:John, CaseID:16, Branch:25, Hearings:1/1/2011
InmateID:2, Name:John, CaseID:17, Branch:25, Hearings:1/1/2011
InmateID:3, Name:Sean, CaseID:18, Branch:40, Hearings:1/1/2011

I would like it to return this only:

InmateID:1, Name:Ben, CaseID:1, Branch:25, Hearings:1/1/2011
InmateID:1, Name:Ben, CaseID:7, Branch:30, Hearings:1/1/2011
InmateID:2, Name:John, CaseID:13, Branch:25, Hearings:1/1/2011
InmateID:3, Name:Sean, CaseID:18, Branch:40, Hearings:1/1/2011

So even if a InmateID has several CaseID's in the same branch and the specific date i only whant to display the first record for each branch

I believe i have to fix my WHERE clause but im not sure how to subQuery or filter the result....?

This is my WHERE clause right now

WHERE (((tblInmateHearings.Hearings)=[Forms]![scheduleChooserForm]![dateForSchedule]));

 

[thhui]

I could only figure out the somehow brutal method to use DAO or ADO
to loop over the Branch Column and
add one column (new-col) to store the key value of 1 or 0
based on the last row value of Branch Column.
if last row of Branch Column = current row value,
new-col value =0, otherwise new-col =1.

Then select all rows with new-col key =1

 

[zozew]

HI thanks for the suggestion, I have managed to come close to my goal by just using SELECT DISTINCT then i get the Unique values. Problem there though is that If you have other fields in your table that are unique for each row like i have a CaseNo field, you get the duplicates again...so i removed that field from the query and it works.

So now I just have to figure out how to run the query with that field being ignored in the SELECT DISTINCT clause....

 

[boblarson]

There is a way with subqueries. See this page (find the Top N per Group for the how-to).

 

[zozew]

Ok great thanks ill try to see if i can make it work. I presume the ORDER BY(in red) in the example should be the field i want to be unique?

I want my Branch & InmateID per record to be unique the rest of the fields don't matter



Copied from your link:

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   [COLOR=Red]ORDER BY[/COLOR] Dupe.OrderDate DESC, Dupe.OrderID DESC) 
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

Im Just a little lost on how to rewrite this so i get the SELECT TOP 1 to work in conjunction with my date check...below

WHERE (((tblInmateHearings.Hearings)=[Forms]![scheduleChooserForm]![dateForSchedule]));

 

[thhui]

Try the following:-

SELECT InmateID, First(Name) AS FName, First(CaseID) AS FCaseID, Branch, First(Hearings) AS FHearings
FROM table1
GROUP BY InmateID, Branch;

 

[zozew]

Hi thanks for the reply...im not sure how i would do that...with my query...
The complete query looks like this..

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)=[Forms]![scheduleChooserForm]![dateForSchedule]));

There are actually some other tables in that query that i omitted in my description before...but the result is the same.

 

[thhui]

Alternative answer using the Top-n Subquery Approach

SELECT InmateID, Name, CaseID, Branch, Hearings
FROM table1
WHERE table1.CaseID IN
   (SELECT TOP 1 CaseID                            
   FROM table1 AS dupe                              
   WHERE dupe.InmateID = table1.InmateID and dupe.Branch = table1.Branch       
   ORDER BY dupe.CaseID)