aggregate function help

[ltcarlisle]

I think that my intentions here are pretty clear -- I'd like to filter a table. No records with same first/last name, no records with "test" in first or last name, no blank records, no first/last name records with only one character, and no first/last name records with numbers. I get the error "Your query does not include the specified expression "Id" as part of an aggregate function". "Id" is the first column of the table that I"m filtering. What am I doing wrong here?



SELECT *
FROM [V2 Loan Applications] AS t1
WHERE t1.[User First Name] <> t1.[User Last Name]
AND t1.[User First Name] <> 'test' or t1.[User Last Name] <> 'test'
AND t1.[User First Name]<>"False"
AND t1.[User Last Name]<>"False"
AND t1.[User Email] Is Not Null AND t1.[User Email] <>' '


HAVING count(t1.[User First Name]) > 1 or count(t1.[User Last Name]) > 1
AND ISNUMERIC(t1.[User First Name]) = 0
AND ISNUMERIC(t1.[User Last Name]) = 0;

[June7]

Don't use the wildcard *. All fields would have to be in a GROUP BY clause. Instead, need the fields constructed with Count in the SELECT.

SELECT Count([User First Name) AS CountFirst, Count([User Last Name]) AS CountLast

The result will be 1 record with two fields. If you want to return full records then can't use the SQL aggregate function this way. I think you will have to use domain aggregation function DCount() or nested subqueries.

Also, the OR operator in the WHERE will probably not give you the desired result. Shouldn't it be an AND? Mixing AND and OR operators is tricky - have to get parens () just right.

[hapm]

Why do you even use having here? You don't need the count function, but the Len() function to get the length of the names. As you don't need the count, you don't have any aggregates at all and don't need a having clause. All your conditions go into the where clause. For the mixing of AND and OR: its like with multiplication and addition in math. Multiplication takes precedence over addition, so 4+2*3 = 10. In boolean algebra AND normally takes precedence over OR: True OR False AND True = False. If you want it the other way round, you will need to add brackets as June7 said.

[June7]

Good point. I did not recognize what was trying to be accomplished with the Count() function. Len() is the appropriate function.

Then the fields wildcard would be okay.

[ltcarlisle]

Why do you even use having here? You don't need the count function, but the Len() function to get the length of the names. As you don't need the count, you don't have any aggregates at all and don't need a having clause. All your conditions go into the where clause. For the mixing of AND and OR: its like with multiplication and addition in math. Multiplication takes precedence over addition, so 4+2*3 = 10. In boolean algebra AND normally takes precedence over OR: True OR False AND True = False. If you want it the other way round, you will need to add brackets as June7 said.

Would the brackets be around the OR clause?

Such as,

AND (t1.[User First Name] <> 'test' or t1.[User Last Name] <> 'test')

[hapm]

Yes, as you want the OR to be evaluated first.

[ltcarlisle]

Yes, as you want the OR to be evaluated first.

For some reason this code is not filtering out the entries with any variation of 'test'. Is it even necessary to include these different capitalized variations? I thought that Access was case insensitive by default

SELECT *
FROM [V2 Loan Applications] AS t1
WHERE t1.[User First Name] <> t1.[User Last Name]
AND (t1.[User First Name] <> 'test' or t1.[User Last Name] <> 'test' or t1.[User First Name] <> 'Test' or t1.[User Last Name] <> 'Test' or t1.[User First Name] <> 'TEST' or t1.[User Last Name] <> 'TEST')

[hapm]

The comparission of text is case sensitive when using the = or <> operator. You can use the LIKE operator or convert the string to all upper or all lower case (I prefere that solution):
SELECT *
FROM [V2 Loan Applications] AS t1
WHERE t1.[User First Name] <> t1.[User Last Name]
AND UCase(t1.[User First Name]) <> 'TEST'

[ltcarlisle]

That makes sense, but it's still not working for me. Any other ideas?

[hapm]

What does "not working for me" mean? Btw I forgot to add the condition for the last name:
SELECT *
FROM [V2 Loan Applications] AS t1
WHERE t1.[User First Name] <> t1.[User Last Name]
AND UCase(t1.[User First Name]) <> 'TEST' AND UCase(t1.[User Last Name]) <> 'TEST'

[ltcarlisle]

Test and its variations still show up in the first and last name fields

[hapm]

Only a guess:
SELECT *
FROM [V2 Loan Applications] AS t1
WHERE t1.[User First Name] <> t1.[User Last Name]
AND Trim(UCase(t1.[User First Name])) <> 'TEST' AND Trim(UCase(t1.[User Last Name])) <> 'TEST'

[ltcarlisle]

Still didn't do it -- I'll keep playing around with it. Also,

WHERE t1.[User First Name] <> t1.[User Last Name]

is not working. I've tried using VARCHAR, CAST...I can't seem to figure it out

[hapm]

What are the types of the fields?

[ltcarlisle]

Short text