CODEWITHSUNDEEP
sqlms-access
stitch70
stitch70

Reputation: 143

Access SQL Query for All Records (non-zero)

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

Above is a sample table in Access. My intent is to capture all unique Names that have strictly value 3.39 for 'Charge', but if any name has a value other than 3.39, then the query would not return the Name at all. For example, based on sample data, a correctly write query would result in only "Beta" showing up.

How can a SQL query be written for the above requirements?

Upvotes: 0

Views: 351

Answers (2)

user5886152
user5886152

Reputation:

Well I got beat to the answer, but here's another way:

SELECT [Name] FROM dbo.charges
WHERE [Charge] = '3.39'
GROUP BY [Name]
HAVING [Name] NOT IN (
    SELECT [Name] FROM dbo.charges
    WHERE [Charge] <> '3.39'
    GROUP BY [Name]
)

Okay, that was the SQL Server version, here is the very slightly different Access version:

SELECT [Name] FROM charges
WHERE [Charge] = 3.39
GROUP BY [Name]
HAVING [Name] NOT IN (
    SELECT [Name] FROM charges
    WHERE [Charge] <> 3.39
    GROUP BY [Name]
)

The table name here is "charges" - change the table and field names to your table and field names, although based on the above conversation I'm not sure if this is what you need any more.

Here is an update with the parameters added:

PARAMETERS [Start Date] DateTime, [End Date] DateTime; 
SELECT charges.Name
FROM charges
WHERE (((charges.Date)>=[Start Date] And (charges.Date)<=[End Date]) AND ((charges.Charge)=3.39))
GROUP BY charges.Name
HAVING (((charges.Name) Not In (SELECT [charges].[Name] FROM [charges] WHERE [charges].[Charge] <> 3.39 GROUP BY [charges].[Name])));

Upvotes: 1

sigil
sigil

Reputation: 9546

select distinct Name from thisTable s1
left join
(select * from thisTable where Charge<>3.39) s2
on
s1.Name=s2.Name
where
s2.Name is null
and
s1.Charge=3.39

Upvotes: 0

Related Questions