Query not accepting GROUP BY

[colintis]

I have a query here which works without the GROUP BY clause

SELECT A.USERNAME, Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,")",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4)) AS [SC]
FROM qryAlarmLog AS A
WHERE A.Date >= (Date()-240);

Generally, this query is getting the user names, and specific numbers called SC which are in a part of the ALARMDETAIL. Both fields are from the query qryAlarmLog, which the result it generates with many duplicate user names and SC. I tried GROUP BY so I can get rid of the duplicated records, but it pops the following error:

You tried to execute a query that does not include the specified expression 'Mid(A.AL.....<skipped>....)' as part of an aggregate function.

I also tried using DISTINCT function but its not working, and even putting this as a subquery like this:

SELECT A.USERNAME, A.SC
FROM (SELECT A.USERNAME, Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,")",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4)) AS [SC]
FROM qryAlarmLog AS A
WHERE A.Date >= (Date()-240)) A
GROUP BY A.USERNAME, A.SC;

But this cause another error : Invalid Procedure Call

Any help would be greatly appreciate.

[MMcCarthy]

Try this ...

SELECT A.USERNAME, Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,")",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4)) AS [SC] 
FROM qryAlarmLog AS A 
WHERE A.Date >= (Date()-240)
GROUP BY A.USERNAME, Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,")",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4))

Essentially Access doesn't recognise the Alias SC

[colintis]

Still having the Invalid procedure call...is it because the source is a query, so there's some limitations to the groupings?

[MMcCarthy]

I doubt it. I would guess it has more to do with the length of the SC field. Only other option is to run the query without the group by and then run a second query off it with a group by.

Try one other thing first, put the SC field in brackets as follows:

SELECT A.USERNAME, (Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,")",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4))) AS [SC] 
FROM qryAlarmLog AS A 
WHERE A.Date >= (Date()-240)
GROUP BY A.USERNAME, (Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,")",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4)))

Both in the query and the group by.

[colintis]

Still the same error message. I also just tried to put this SC to the query source qryAlarmLog, and extract it back in this query, but still showing the same error...

The ALARMDETAIL contains a long string (around 100~120 characters), which SC only looks for a particular part of this string field.

[MMcCarthy]

If you create this query without the group by ..

SELECT A.USERNAME, (Mid(A.ALARMDETAILS,(InStr(1,A.ALARMDETAILS,"User",1)+4),(InStr(InStr(1,A.ALARMDETAILS,"User",1),A.ALARMDETAILS,")",1))-(InStr(1,A.ALARMDETAILS,"User",1)+4))) AS [SC] 
FROM qryAlarmLog AS A 
WHERE A.Date >= (Date()-240)

Assuming we call this Query1 then create another query.

SELECT Username, SC
FROM Query1
GROUP BY Username, SC

Does that work?

[colintis]

Nope....I want to slam that error message....

[MMcCarthy]

LOL, I know that feeling.

You said the query works fine when you don't add the group by?

[MMcCarthy]

Can you tell me the logic of what you are trying to do with the SC field. Break it down into steps and lets see if we can reduce the size of it.

[colintis]

Yes it works fine with the duplicating usernames and SCs.

The SC as seen is obtaining a part of data in the ALARMDETAILS that looks like this:

Chall 1 (RIGHT WING) Access Granted with Region: Door 777 (RECEPTION FLYER IN), Region 4 (region 4) (George Bush, User 218)

The 218 is SC, and the result of this query will shows a duplication of such SC numbers in >1k times to each users. This query is used by a form and a report to list the access record of users within a period of time. Or by user, whereas simply showing the SC on the form and generate another report. As this query will make the report listing duplicate user names so I need to group that up without wasting more papers..

[Oralloy]

I don't think you can use GROUP BY without specifing an aggrigate, like COUNT(*) among the selected fields.

Does that make sense to you?

[colintis]

Yes of course, so do you suggest I should save those results into a table and group them from that table?

[MMcCarthy]

@Orally

No you can use group by without an aggregate field as long as you group by all fields.

@Colintis
Is user always at the end of the field and is the number always 3 digits?

[colintis]

@MMcCarthy
Yes the number is always behind the word 'user', but the numbers are 2 to 4 digits long which is not stable.

[Oralloy]

@Mary,

I just learned something. Thank you.

[MMcCarthy]

OK try this one ...

LEFT(RIGHT(A.ALARMDETAILS, InstrRev (A.ALARMDETAILS, "User", -1, vbTextCompare)+5),LEN(RIGHT(A.ALARMDETAILS, InstrRev (A.ALARMDETAILS, "User", -1, vbTextCompare)+5))-1)
 

[colintis]

What's this vbTextCompare, it asked me to enter a parameter for it.

[MMcCarthy]

Just leave it out, it's optional

[MMcCarthy]

Without the vbTextCompare

LEFT(RIGHT(A.ALARMDETAILS, InstrRev (A.ALARMDETAILS, "User", -1)+5),LEN(RIGHT(A.ALARMDETAILS, InstrRev (A.ALARMDETAILS, "User", -1)+5))-1)
 

[colintis]

The qurey runs, but SC is showing back the whole line of ALARMDETAILS..

That's what it becomes
ll 1 (RIGHT WING) Access Granted with Region: Door 777 (RECEPTION FLYER IN), Region 4 (region 4) (George Bush, User 218

[MMcCarthy]

OK let me play with it for a bit. Give me 10 mins.

[MMcCarthy]

OK try this ...

Left(Right([ALARMDETAILS],Len([AlarmDetails])-InStrRev([ALARMDETAILS],"User",-1)-4),Len(Right([ALARMDETAILS],Len([AlarmDetails])-InStrRev([ALARMDETAILS],"User",-1)-4))-1)

Although it looks as long if not longer the fact that it's using InStrRev instead of InStr should make the query load less.

[colintis]

Works great with charm~ many thanks for your help Mary =D

[MMcCarthy]

You're welcome, glad we figured it out.

Mary