SELECT Separate distinguishing column in a query with multiple columns available

Question

Reference Information. I am creating a query in MS Access. My problem is that I have a table in which the Start time of the action, the user involved, the Subject in question and the type of transaction are recorded. For each event, there are four stampings of this event, two for each element and two for each type: Table below

I want to create a query that displays one entry for each StartTime with the user and each corresponding element. However, this is a basic example in which there may be more users, elements, or types.

I currently have a request:

SELECT DISTINCT (CIN.StartTime), CIN.User, CIN.Item, CIN2.Item FROM Query2 AS CIN INNER JOIN Query2 AS CIN2 ON CIN.StartTime = CIN2.StartTime WHERE (((CIN.Item)<>"" And (CIN.Item)<>[CIN2].[Item]) AND ((CIN.Type)="ITEM" Or (CIN.Type)="CALL") AND ((CIN2.Type)="ITEM" Or (CIN2.Type)="CALL")); 

As you can see, the request is close, but there are still two brands in StartTime. Allegedly, two for each item as a union, captures each Item value and attaches it to another value.

As Caveats for my problem, I cannot change the values ​​of the table or the raw data, so I am looking for a software solution in my query. I am wondering if there is a way to select Distinct StartTime from the results, not counting other columns different from each other, or some other smart solution to this problem?

thanks

Answer 1

Change And (CIN.Item)<>[CIN2].[Item]) to And (CIN.Item)<[CIN2].[Item]) . This will avoid duplicating the pairs that you get by only allowing lines where CIN.Item to CIN2.Item is in ABC order.