CODEWITHSUNDEEP
sqlms-accessdistinct
Mabdullah
Mabdullah

Reputation: 1013

SELECT Individual Distinct Column in a Query with multiple Columns in Access

Background: I am creating a Query in a MS Access. My problem is that i have a Table that records the Start Time of an action, the user involved, the Item in Question and the type of transaction. For every event there are four stampings of this event, two for each item and two for each type: Table Below

Table of Data

I want to Create Query that displays one record for each StartTime with the User and each matching Item. However, this is a basic example there could be more Users, Items, or Types.

The query i currently have is below with it's results:

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"));

Query

As you can see the query is close but there are still two stamps per StartTime. Ostensibly, two for each item as the join, grabs each Item value and joins it to the other value.

As Caveats to my problem, I cannot change the table values or raw data, so i'm looking for a programatic solution in my query. I'm wondering if there is a way to Select a Distinct StartTime from the results without the other columns being distinct as well, or some other clever solution to this problem?

Thank you

Upvotes: 4

Views: 2025

Answers (1)

Derek
Derek

Reputation: 23318

Change And (CIN.Item)<>[CIN2].[Item]) to And (CIN.Item)<[CIN2].[Item]). That will avoid the duplicate pairs you're getting by only allowing rows where CIN.Item is before CIN2.Item in ABC order.

Upvotes: 1

Related Questions