Need a little SQL help please

[Lol999]

Okay, so I have an SQL string to use as the recordsource for a subform.
What I would like to do though is sort the data by date and group it by it's category.
If I just use the ORDER BY then the string works well, but as soon as I bring the GROUP BY into the equation then I start getting errors "Your query does not include the specified expression Issue_Employee_ID as part of an aggregate function"
Here is the code:

"SELECT Tbl_Issue.Issue_Employee_ID, Tbl_Employees.Employee_Employee_Name, Tbl_PPE.PPE_PPE_Category, Tbl_PPE.PPE_PPE_Description, Tbl_Issue.Issue_PPE_Size, Tbl_Issue.Qty_Issue, Tbl_Issue.Date_Issued " & _
"FROM Tbl_Employees INNER JOIN (Tbl_PPE INNER JOIN Tbl_Issue ON Tbl_PPE.[PPE_ID] = Tbl_Issue.[Issue_PPE_ID]) ON Tbl_Employees.Employee_Employee_ID = Tbl_Issue.Issue_Employee_ID " & _
"WHERE Tbl_PPE.PPE_PPE_Category= Cbo_PPE_Category.Value AND Tbl_Issue.Date_Issued Between Txt_Start_Date.Value  And Txt_End_Date.Value GROUP BY Tbl_Issue.Issue_PPE_ID ORDER BY Tbl_Issue.Date_Issued;"

To be honest this is beyond me so if someone could point out where I'm going wrong it would be much appreciated!

many thanks, Lol

 

[isladogs]

Sorry lol but I remember your 'horrible' naming convention and its stopping me reading your SQL.

I believe you will get a better response when you fix it
...as has been pointed out more than once before

I suspect others may feel the same as me

 

[jdraw]

When you do a GROUP BY you are aggregating the data, so you lose some details of the individual items.

A GROUP BY clause works on the rows returned by a query by summarizing identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.

 

[plog]

I do not. I don't see anything horrible with your naming. I do however see what's horrible about your SQL:

Every field in the SELECT must either be in the GROUP BY or be inside an aggregate function (COUNT, MAX, MIN, etc.)

You cannot add whatever you want to the GROUP BY. What exactly are you expecting datawise and what are you getting? Can you demonstrate your issue with data?

 

[isladogs]

hi plog

Of course your explanation is correct BUT...

If all the repetition and underscores were removed, it would be about half the length and much easier to read/follow

E.g tbl_PPE.PPE_PPE_CATEGORY => TblPPE.Category or perhaps tblPPE.PPECategory ...etc

 

[jdraw]

As asked in previous posts, do you have a specification or description of what you are trying to do?
I also don't see anything (in this SQL) that is horrible/raising a great red flag. However, I do see you building things physically without a clear plan or specification.

Good luck.

 

[Lol999]

Therein lies the solution, despite all my searching on the internet I didn't come across anything that told me I had to have every field in my SELECT statement in the GROUP BY, in fact there was very little except the most basic syntax.

I can go away now and have a further go and see where I get to.

@Ridders - I use the naming convention so I know just which field I am working with and from which table it originated. It works for me, but like I said before, I'm strange

 

[isladogs]

That's ok, i'll just stay out of it in future

 

[Lol999]

This is from a form which allows users to search the db for PPE issued between two user defined dates and a category the user selects from a combo.
Plan? in my head same as the specification.
Same situation as before, customer doesn't know what they want so I'm trying to anticipate their needs.
No biggie.

As asked in previous posts, do you have a specification or description of what you are trying to do?
I also don't see anything (in this SQL) that is horrible/raising a great red flag. However, I do see you building things physically without a clear plan or specification.

Good luck.

 

[Lol999]

That's ok, i'll just stay out of it in future

If I try to be more "normal" do you promise to come back and play nicely?

 

[isladogs]

Its more fun throwing my toys out of the pram

 

[kevlray]

Anyhow back to Lol999 question. If you use Group By you have either include the field in the group by list or in an aggregate. Of course if you do not have an aggregate then the Group By works more like a Order By.

 

[plog]

ridders, you made it seem like he committed a war crime with his naming. I just said it wasn't horrible (of course being on this board for a bit has lowered my horrible standards) . Discussing naming conventions on the internet is as usually as fruitful as discussing religion, but I'll indulge for a bit. I think your solution is penultimately as bad:

tbl_PPE.PPE_PPE_CATEGORY => TblPPE.Category or perhaps tblPPE.PPECategory ...etc

1. There's no need for the 'tbl' prefix.
2. Definitely no need for the double 'PPE'.
3. People seem to think caps have a mystical computer power.
4. And most of all-->there's no need for the table reference at all:

tbl_PPE.PPE_PPE_CATEGORY => Category

 

[isladogs]

ridders, you made it seem like he committed a war crime with his naming. I just said it wasn't horrible (of course being on this board for a bit has lowered my horrible standards) . Discussing naming conventions on the internet is as usually as fruitful as discussing religion, but I'll indulge for a bit. I think your solution is penultimately as bad:

tbl_PPE.PPE_PPE_CATEGORY => TblPPE.Category or perhaps tblPPE.PPECategory ...etc

1. There's no need for the 'tbl' prefix.
2. Definitely no need for the double 'PPE'.
3. People seem to think caps have a mystical computer power.
4. And most of all-->there's no need for the table reference at all:

tbl_PPE.PPE_PPE_CATEGORY => Category

All true except the table ref is needed in the context of the SQL supplied.
I only used tbl and caps as they were in the original post.

As I'm now playing nicely I won't mention the 'penultimately'.
Oops sorry too late

Anyway I really will drop out now

 

[plog]

1. You mentioned mentioning penultimately, yet you gave no context for mentioning it. What's the matter with it?

2. In the context given, it's at best unclear if the table reference is needed. We were discussing succint code, mine was the succintest.

3. Please mention mentioning succintest in your reply and provide context.

 

[isladogs]

Lol
Can't think of a witty reply

Ultimately yours is the most succinct

 

[jdraw]

lol99,

Check this out re new work/project, much better than trying to anticipate/outguess the client task by task.

Good luck.

 

[Lol999]

lol99,

Check this out re new work/project, much better than trying to anticipate/outguess the client task by task.

Good luck.

Bless you but this is my original client, for whom I work pro-bono, who hasn't the time to sit and discuss what they want they are simply relying on me to provide them with something and amend it as and when needs dictate.
I know I must be a frustration at times, and I DO appreciate the help and suggestions offered, but I know the bounds within which I am operating and see no point in wasting time trying to change that which will not change.

I studied systems analysis at college and believe me there is nothing it offers that would make any difference to the environment I operate in.

It doesn't matter though, he is a great friend and so I work happily (when not frustrated by my lack of knowledge) in the dark knowing I am improving his situation at least somewhat

Thanks for your help, Lol

 

[jdraw]

Understood.
Good luck.