SQL: join and exists are combined

[gdrop]

I've got 3 tables. The irrelevant columns are omitted.
Table1:Items
ID ItemsName
1 Food
2 Beverage
Table2:Invoice
ID User Amount
1 1 20
2 1 30
Table3:Invoice_details
ID Invoice_id Items_id DetailsAmount
1 1 1 10
2 1 2 10
3 2 1 30

Aim:
Food 40
Beverage 10

My query:

SELECT ItemsName, SUM(DetailsAmount) 
FROM Invoice_details 
INNER JOIN Items ON Items_id = Items.ID 
WHERE EXISTS (SELECT * FROM Invoice WHERE User = 1 AND Invoice_details.Invoice_id = Invoice.ID) 
GROUP BY ItemsName

Error: You tried to execute a query that does not include the specified expression 'ItemsName' as part of an aggregate function.
Does anyone have a clue where I went wrong?

[Seth Schrock]

The word Name is a reserved word in Access. The best solution would be to change the field name to something else, like ItemName. Another, less desirable, option would be to place the field name in square brackets.

GROUP BY [Name];

[gdrop]

Actually my real query mentions "PostNaam" instead of "Name". For reasons of simplicity and translation I changed the orginal query a bit. Apologies for the misunderstanding. I updated my question accordingly.

[Seth Schrock]

Please do a copy and paste of your query WITHOUT changing the original query. Also, please use code tags (the [CODE/] button will add them for you) when you post it.

[NeoPa]

@gdrop.
The idea to simplify is a good one that many intelligent and helpful people come up with when posting their questions. Unfortunately, in practice, it seems to cause more problems than it avoids. Sometimes it's necessary as the original is extremely long or complicated in some way. If/when ever you do so (avoid where possible) then please always indicate that you have (Just as you did with fields left out of your tables).

Your table design.
The table [Invoice] should definitely not contain an amount field. It isn't a problem here - but will be later. See Database Normalisation and Table Structures.
NB. If you learn nothing else about databases at this site, but learn about normalisation, then we will have helped you more than you can imagine. I cannot overstate the importance of this issue.

Your SQL.
May I suggest a simpler approach that links the tables together logically and which allows you to filter what you want in the WHERE clause more straightforwardly :

SELECT   [Invoice].[User]
       , [Items].[ItemsName]
       , SUM([Invoice_Details].[DetailsAmount]) AS [Amount]
FROM     ([Invoice_Details]
         INNER JOIN
         [Invoice]
  ON     [Invoice_Details].[Invoice_ID]=[Invoice].[ID])
         INNER JOIN
         [Invoice_Details].[Items_ID]=[Items].[ID]
WHERE    ([Invoice].[User]=1)
GROUP BY [Invoice].[User]
       , [Items].[ItemsName]

With this approach you can lose the WHERE clause and still filter the results externally, or even not filter at all if that's what you find you need.

[gdrop]

Hello NeoPa,
Your reply is very helpful to improve my insight in normalisation. Unfortunately the outcome of the query fails to the same error... (Note that I need to group by [ItemsName], not by [User], [ItemsName].)

[Seth Schrock]

Every field that isn't included in an expression/aggregate function (such as the Sum() function) has to be included in the GROUP BY clause. That is why you are getting the error. This is why we need the whole SQL code from your query so that we can figure out which field is missing.

[gdrop]

"SELECT Post.PostNaam, SUM(PostBoeking.Bedrag) " +
            "FROM " +
                "(PostBoeking " + 
                "INNER JOIN LeverancierBoeking " +
                "ON PostBoeking.LeverancierBoeking_id = LeverancierBoeking.ID) " +
            "INNER JOIN Post " +
                " ON PostBoeking.Post_id = Post.ID " +
            "WHERE LeverancierBoeking.Actief = 1  " +
            "AND LeverancierBoeking.Gebruiker_id = " + ID + " " +
            "AND LeverancierBoeking.Valutadatum BETWEEN #" + vanaf + "# AND #" + tot + "# " +
            "GROUP BY Post.PostNaam";

[NeoPa]

Did you use the SQL as I posted it or did you only test it after removing the [User] field from the GROUP BY clause?

NB. When posting SQL it is helpful to post the actual SQL and not some part of the VBA code you have used to produce the SQL.

[gdrop]

The latest SQL that I posted, was the SQL adapted to your suggestion. That's the last version that I tested with. I'm not sure what VBA code you are referring to. The SQL is just a String that I wrote myself for my Java program (Eclipse IDE), the language is Dutch, in case you're wondering :-). I've only just graduated as a bachelor in software development, and sometimes I still seem to struggle with writing SQL statements...

[NeoPa]

gdrop:
The latest SQL that I posted, was the SQL adapted to your suggestion.

Let me try to explain. Being a graduate I expect your difficulty is the language, though I find the Dutch education system for English is very good. Most young people from The Netherlands speak English very well. Still, it's a foreign language so harder than speaking in Dutch of course.

Here goes...

What you posted is not SQL - but text formatted as SQL strings (in VBA style) that should, when executed, result in a string which can be used as SQL. This is not the same as posting the SQL. The SQL would have the values for [vanaf] and [tot] resolved already and embedded in the string. See Before Posting (VBA or SQL) Code.

From what I could see of the SQL that was still contained within the VBA strings it absolutely does not reflect what I suggested. One glaring omission is the [User] reference within the GROUP BY clause. There may be (I suspect there are) more that will be easier to spot when we have the actual SQL to look at.

My Dutch is pretty rudimentary, but I can recognise (and even pronounce to a fashion) many of the terms used and I can see easily which are references to named items and which are SQL words. I suspect we can help just as much in Dutch as we could in English.

Please try again to :

1. Update the SQL to match what I suggested.
2. Test that SQL.
3. Separate out the actual SQL code for posting what you tried and what result you got.