I am writing a query that is checking each record to see if it contains a 

certain status and then checks to see when it switched from that status.

However, since it is using two different aliases of the same table it 

sometimes shows extra records that don't really exist. I don't know if I 

have explained this well enough but any help with this problem would be 

grealy apreciated. Following is the SQL statement from Access:



SELECT BOLINF_CS_INCIDENTS_ALL.INCIDENT_NUMBER, 

BOLINF_CS_INCIDENT_STATUSES.NAME, BOLINF_CS_INCIDENTS_AUDIT.CREATION_DATE, 

BOLINF_CS_INCIDENTS_AUDIT.CREATION_TIME, 

BOLINF_CS_INCIDENT_STATUSES_1.NAME, 

BOLINF_CS_INCIDENTS_AUDIT_1.CREATION_DATE, 

BOLINF_CS_INCIDENTS_AUDIT_1.CREATION_TIME, (TimeValue

(BOLINF_CS_INCIDENTS_AUDIT_1.CREATION_TIME)-TimeValue

([BOLINF_CS_INCIDENTS_AUDIT].[CREATION_TIME]))+((DateValue

(BOLINF_CS_INCIDENTS_AUDIT_1.CREATION_DATE)-DateValue

([BOLINF_CS_INCIDENTS_AUDIT].[CREATION_DATE]))*24) AS SubTime



FROM (((BOLINF_CS_INCIDENTS_AUDIT INNER JOIN BOLINF_CS_INCIDENTS_ALL ON 

BOLINF_CS_INCIDENTS_AUDIT.INCIDENT_ID = 

BOLINF_CS_INCIDENTS_ALL.INCIDENT_ID) INNER JOIN 

BOLINF_CS_INCIDENT_STATUSES ON 

BOLINF_CS_INCIDENTS_AUDIT.INCIDENT_STATUS_ID = 

BOLINF_CS_INCIDENT_STATUSES.INCIDENT_STATUS_ID) INNER JOIN 

BOLINF_CS_INCIDENTS_AUDIT AS BOLINF_CS_INCIDENTS_AUDIT_1 ON 

BOLINF_CS_INCIDENTS_ALL.INCIDENT_ID = 

BOLINF_CS_INCIDENTS_AUDIT_1.INCIDENT_ID) INNER JOIN 

BOLINF_CS_INCIDENT_STATUSES AS BOLINF_CS_INCIDENT_STATUSES_1 ON 

BOLINF_CS_INCIDENTS_AUDIT_1.OLD_INCIDENT_STATUS_ID = 

BOLINF_CS_INCIDENT_STATUSES_1.INCIDENT_STATUS_ID



WHERE (((BOLINF_CS_INCIDENT_STATUSES.NAME)="Pending Close" Or 

(BOLINF_CS_INCIDENT_STATUSES.NAME)="Closed" Or 

(BOLINF_CS_INCIDENT_STATUSES.NAME)="On Hold") AND 

((BOLINF_CS_INCIDENT_STATUSES_1.NAME)=[BOLINF_CS_INCIDENT_STATUSES].

[NAME]) AND ((BOLINF_CS_INCIDENTS_AUDIT_1.CREATION_DATE)>

[BOLINF_CS_INCIDENTS_AUDIT].[CREATION_DATE]) AND 

((BOLINF_CS_INCIDENTS_AUDIT_1.CREATION_TIME)>=[BOLINF_CS_INCIDENTS_AUDIT].

[CREATION_TIME]));



I don't know if that will help at all but if you have any suggestions or 

need more info from me just ask. Thanks.