Access query

[kkt]

Hi, need some help. what is the Access query to get the desired result for below scenario ? Thank you...

Conditions to delete rows:
Remove 0 spend for company repeated more than once.
Do not remove valid company with 0 spend.
Do not aggregate the spend

Company Spend ($)


A 1,000
A 2,000
A remove
B 3,000
B remove
C 3,000
C 2,000
C remove
D do not remove. valid company 0 spend
E remove
E 8,000
F do not remove. valid company 0 spend

Desired Result
Company Spend ($)
A 1,000
A 2,000
B 3,000
C 3,000
C 2,000
D
E 8,000
F

[June7]

This will require a Totals query to get count of records for each company. Here is nested query.

DELETE FROM Table1 WHERE Spend Is Null And Company In(SELECT Table1.Company
FROM (SELECT Company, Count(Company) AS CountOfCompany
FROM Table1 GROUP BY Company) As CompanyCount INNER JOIN Table1 ON CompanyCount.Company = Table1.Company
WHERE CountOfCompany>1 AND Spend Is Null);

[kkt]

It works!! Thanks !!