Group by query - case sensitive 1

[rjoubert]

I have a Group By SQL statement I am trying to run against an Excel spreadsheet (via a VB.NET app). I want the query to return two records if it finds data like "Test" and "test". Instead, it's only returning one record. Suggestions?

SELECT [FieldName], Count([FieldName]) as DataCount 
FROM [TheData]
GROUP BY [FieldName]
ORDER BY Count([FieldName])

 

[genomon]

SELECT [FieldName], Count([FieldName]) as DataCount
FROM [TheData]
WHERE [FieldName] LIKE "*test*"
GROUP BY [FieldName]
ORDER BY Count([FieldName])

might work, but would also return "testing" "Testy", etc.

10 is company, 11 is a crowd

 

[rjoubert]

The purpose of my query is to gather unique values from [FieldName], not to query for a particular value. I want the query to treat "Test" and "test" as unique values. Those were only provided as examples.

 

[genomon]

Gotcha! Dunno if DISTINCT works with COUNT though:

SELECT DISTINCT [FieldName], Count([FieldName]) as DataCount
FROM [TheData]
GROUP BY [FieldName]
ORDER BY Count([FieldName])


10 is company, 11 is a crowd

 

[SkipVought]

Hi,

"...if it finds data like "Test" and "test". ..."

Where is your criteria?

WHERE ... UCase([Fieldname]) Like '*TEST*'

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[PHV]

Typed, untested:
GROUP BY [FieldName], Asc([FieldName])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rjoubert]

I don't need a WHERE clause because, as I stated above, I'm retrieving a list of unique values in [FieldName], not searching for a particular value.

 

[rjoubert]

PHV...that didn't work. It said there was a syntax error in the Group By clause. I should add that I'm using this to query a spreadsheet.

 

[PHV]

Are you sure the DISTINCT predicate does that you want ?
It would be the first time I'd see it useful in an aggregate query ....
BTW, did you try my suggestion ?

 

[SkipVought]

SELECT [FieldName], Count([FieldName]) as DataCount 
FROM [TheData[b][red]$[/red][/b]]
GROUP BY [FieldName]
ORDER BY Count([FieldName])

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[rjoubert]

PHV...I took DISTINCT out of my query, as it was not originally in there. And yes, I tried your suggestion, and it did not work.

Skip...[TheData] is a named range in my excel spreadsheet. Putting the "$" after it in the name causes it to error out.

 

[rjoubert]

I will have to check back with you all tomorrow morning. I have to leave now, and I won't be able to check on this while at home (newborn at home).

 

[PHV]

and it did not work
The VERY SAME result as when no Asc() in the GROUP BY clause ????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

The $ is used for Sheet Names.

I read, "...I am trying to run against an Excel spreadsheet ..."

You did not say, "...I am trying to run against named range in an Excel spreadsheet ..."

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[dhookom]

Since your question is

Excel spreadsheet (via a VB.NET app)

then I am wondering why you are asking this question in an Access forum. You have thoroughly confused some good TT [blue]forators[/blue].

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rjoubert]

Duane...if I'm not mistaken, this is the MS Access queries AND JET SQL forum. I tried the .NET forum with no luck.

 

[PHV]

why not answer my question 11 Jul 07 16:00 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rjoubert]

PHV...I'll have to verify that when I get back to work in the morning. Thanks for your patience.

 

[rjoubert]

PHV...There is no error when I run the SQL in my OP (without the Asc([FieldName]) added to the Group By clause). It just doesn't distinguish between values like "Test" and "test".

 

[rjoubert]

FYI, I imported the data into Access and tried the query there, but I got the same results.

SELECT [FieldName], 
Count([FieldName]) AS DataCount
FROM TheData
GROUP BY [FieldName]