I can't seem to get rid of duplicates in my query. I have tried using Min/Max, distinct, etc. and still having problems. Some of the tables and fields in my query are:
OCCMAIN - table
OCCNumber - field
TypeCase - field
Name/Plaintiff - field
Defendant - Field
AttorneyID - Field (linked to table called (TblAttorney)
TblLinkCounDate - Table
OCCNumber - field
In OCC - field
Out OCC - field
TblLinkLitDate
OCCNumber - field
To OCC - Field
Closed - field
In my query I want to find all of the cases that were opened before 7/1/04 and left the office between 1/1/04 and 7/1/04 and assigned to a certain attorney. Each case has a unique number field (OCCNumber). A case may come in and out of the office numerous times. However, in my query I only want the case to appear once, even though it may have come in and out 3 times. Below, Case Number 123 should only appear once.
OCCNumber In OCC Out OCC To OCC Closed
123 1/5/04 2/5/04
22 5/5/04 5/6/04
123 6/6/04 6/15/04
123 6/16/04 6/18/04
2 2/2/04 3/3/04
Below is the coding. I did not write it but copied it from the query that I did. Any help would be greatly appreciated.
SELECT TBLOCCMain.Year, TBLOCCMain.AssignType, TBLOCCMain.OCCNumber, TBLOCCMain.[Name/Plaintiff], TblCounTypeCase.CounTypeCase, TBLOCCMain.CounProjectSubType, TBLOCCMain.AttorneyID, TBLLinkCounDate.[In OCC], TBLLinkCounDate.[Out OCC], TBLLinkLitDate.[To OCC], TBLLinkLitDate.Closed, TBLOCCMain.LitProjectSubType, TBLOCCMain.AdminProject, TBLOCCMain.MgmentProjectType, TblLitTypeCase.Typecase, TBLOCCMain.LitDefendant, TblLookSupervisor.LastSup, TblLookAttorney.LastName, TBLOCCMain.SigActivity, (([TblLookAttorney].[firstname] & ' ' & [lastName])) AS [Attorney Name]
FROM TblLookSupervisor RIGHT JOIN (TblLookAttorney INNER JOIN (((((TBLOCCMain LEFT JOIN TBLLinkCounDate ON TBLOCCMain.OCCNumber = TBLLinkCounDate.OCCNumber) LEFT JOIN TblCounTypeCase ON TBLOCCMain.CounTypeID = TblCounTypeCase.CounTypeID) LEFT JOIN TblLitTypeCase ON TBLOCCMain.LitTypeCaseID = TblLitTypeCase.LitTypeCaseID) LEFT JOIN TBLLinkLitDate ON TBLOCCMain.OCCNumber = TBLLinkLitDate.OCCNumber) LEFT JOIN TBLLinkPreAttorney ON TBLOCCMain.OCCNumber = TBLLinkPreAttorney.OCCNumber) ON (TblLookAttorney.AttorneyID = TBLOCCMain.AttorneyID) AND (TblLookAttorney.AttorneyID = TBLOCCMain.AttorneyID)) ON TblLookSupervisor.AttorneyID = TBLOCCMain.SupervisorID
WHERE (((TBLLinkCounDate.[In OCC])<#7/1/2004#) AND ((TBLLinkCounDate.[Out OCC]) Between #1/1/2004# And #7/1/2004#) AND ((TblLookAttorney.LastName)=[Type Last Name])) OR (((TBLLinkLitDate.[To OCC])<#7/1/2004#) AND ((TBLLinkLitDate.Closed) Between #1/1/2004# And #7/1/2004# Or (TBLLinkLitDate.Closed) Is Null) AND ((TblLookAttorney.LastName)=[Type Last Name]));
OCCMAIN - table
OCCNumber - field
TypeCase - field
Name/Plaintiff - field
Defendant - Field
AttorneyID - Field (linked to table called (TblAttorney)
TblLinkCounDate - Table
OCCNumber - field
In OCC - field
Out OCC - field
TblLinkLitDate
OCCNumber - field
To OCC - Field
Closed - field
In my query I want to find all of the cases that were opened before 7/1/04 and left the office between 1/1/04 and 7/1/04 and assigned to a certain attorney. Each case has a unique number field (OCCNumber). A case may come in and out of the office numerous times. However, in my query I only want the case to appear once, even though it may have come in and out 3 times. Below, Case Number 123 should only appear once.
OCCNumber In OCC Out OCC To OCC Closed
123 1/5/04 2/5/04
22 5/5/04 5/6/04
123 6/6/04 6/15/04
123 6/16/04 6/18/04
2 2/2/04 3/3/04
Below is the coding. I did not write it but copied it from the query that I did. Any help would be greatly appreciated.
SELECT TBLOCCMain.Year, TBLOCCMain.AssignType, TBLOCCMain.OCCNumber, TBLOCCMain.[Name/Plaintiff], TblCounTypeCase.CounTypeCase, TBLOCCMain.CounProjectSubType, TBLOCCMain.AttorneyID, TBLLinkCounDate.[In OCC], TBLLinkCounDate.[Out OCC], TBLLinkLitDate.[To OCC], TBLLinkLitDate.Closed, TBLOCCMain.LitProjectSubType, TBLOCCMain.AdminProject, TBLOCCMain.MgmentProjectType, TblLitTypeCase.Typecase, TBLOCCMain.LitDefendant, TblLookSupervisor.LastSup, TblLookAttorney.LastName, TBLOCCMain.SigActivity, (([TblLookAttorney].[firstname] & ' ' & [lastName])) AS [Attorney Name]
FROM TblLookSupervisor RIGHT JOIN (TblLookAttorney INNER JOIN (((((TBLOCCMain LEFT JOIN TBLLinkCounDate ON TBLOCCMain.OCCNumber = TBLLinkCounDate.OCCNumber) LEFT JOIN TblCounTypeCase ON TBLOCCMain.CounTypeID = TblCounTypeCase.CounTypeID) LEFT JOIN TblLitTypeCase ON TBLOCCMain.LitTypeCaseID = TblLitTypeCase.LitTypeCaseID) LEFT JOIN TBLLinkLitDate ON TBLOCCMain.OCCNumber = TBLLinkLitDate.OCCNumber) LEFT JOIN TBLLinkPreAttorney ON TBLOCCMain.OCCNumber = TBLLinkPreAttorney.OCCNumber) ON (TblLookAttorney.AttorneyID = TBLOCCMain.AttorneyID) AND (TblLookAttorney.AttorneyID = TBLOCCMain.AttorneyID)) ON TblLookSupervisor.AttorneyID = TBLOCCMain.SupervisorID
WHERE (((TBLLinkCounDate.[In OCC])<#7/1/2004#) AND ((TBLLinkCounDate.[Out OCC]) Between #1/1/2004# And #7/1/2004#) AND ((TblLookAttorney.LastName)=[Type Last Name])) OR (((TBLLinkLitDate.[To OCC])<#7/1/2004#) AND ((TBLLinkLitDate.Closed) Between #1/1/2004# And #7/1/2004# Or (TBLLinkLitDate.Closed) Is Null) AND ((TblLookAttorney.LastName)=[Type Last Name]));
