CODEWITHSUNDEEP
sqlms-accesssubquery
stitch70
stitch70

Reputation: 143

Access Query for specific values

Date        Name    Charge
1/15/2015   Alpha   3.39
2/15/2015   Alpha   3.39
2/15/2015   Beta    3.39
2/15/2015   Gamma   2
3/15/2015   Gamma   3
3/15/2015   Alpha   5
4/15/2015   Beta    3.39

How can unique Name records be obtained such that only records with exclusively 3.39 is resulted for the specified table. Expected result for above table is only record 'Beta'. Alpha would not be in results because it has value 5 for one month.

Similar query was done (below) for querying '0' charges but '3.39' is now being attempted:

SELECT Name FROM tbl 
GROUP BY Name HAVING SUM(Charge) = 0

Upvotes: 1

Views: 349

Answers (1)

Andre
Andre

Reputation: 27634

I would first select the records that violate the condition (<> 3.39), then select all names that are not in that group.

SELECT DISTINCT [Name]
FROM tbl
WHERE [Name] NOT IN
    (SELECT [Name]
    FROM tbl
    WHERE Charge <> 3.39)

Note that calculating with floating point numbers can be tricky, you may need to do something like WHERE Abs(Charge - 3.39) < 0.000001.

Name is a reserved word, so needs to be put in square brackets. Even better would be to avoid such field names.

Edit

With a large table, Access doesn't perform too well with NOT IN.

A solution should be to insert the subquery result into a "temp" table with a single column sName.

Query 1:

DELETE * FROM tTemp

Query 2:

INSERT INTO tTemp (sName)
SELECT DISTINCT [Name]
FROM tbl
WHERE Charge <> 3.39

Query 3:

SELECT DISTINCT tbl.[Name]
FROM tbl LEFT JOIN tTemp ON tbl.[Name] = tTemp.sName
WHERE tTemp.sName IS NULL

Upvotes: 1

Related Questions