NZ and Sum by Group

[Jerseynjphillypa]

Here is my SQL. i am trying to Sum the Stock and Used data by DateEnter and if its nothing I want it to show up as a 0.



But I get the following error
You tried to execute a query that does not include the specified expression 'Item' as part of an aggregate function

INSERT INTO AllDataTogether ( DateEnter )
SELECT RawInfoUNION.*, [enter date] AS DateEnter
FROM (SELECT Information.Item, Location, Supplier, Used, Sum(Nz(Raw Data]![Stock],0)) AS Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item
UNION SELECT [Information 2].Item, Location, Supplier, Sum(Nz(Raw Data 2]![Used],0)) AS Used, Sum(Nz(Raw Data 2]![Stock],0)) AS Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item) AS RawInfoUNION
WHERE (((RawInfoUNION.Location)="NJ")) OR (((RawInfoUNION.Location)="CA"));

[RayMilhon]

Since your using a sum aggregate function you need to include the Group By

SELECT Information.Item, Location, Supplier, Used, Sum(Nz(Raw Data]![Stock],0)) AS Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item Group By Item, Location, Supplier
UNION SELECT [Information 2].Item, Location, Supplier, Sum(Nz(Raw Data 2]![Used],0)) AS Used, Sum(Nz(Raw Data 2]![Stock],0)) AS Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item Group By Item, Location,Supplier) AS RawInfoUNION

Also in the first part of the union you have the field Used In the Second Part it's a calculation. If it's a field in the first part of the union include it in the group by. If not you need to put in a calculation such as Sum(0) As Used. instead of just used.

[Jerseynjphillypa]

I get the following error and I can't figure where to remove/add the ")".

If I keep the ")" to complete he FROM, I get the error Extra ) in query expression 'Supplier) AS RawInfoUnion WHERE (((RawInfo UNION.Location)="NJ")) OR (((RawInUnion.Location)="CA"))'

and if I remove it I get missing operator

INSERT INTO AllDataTogether ( DateEnter )
SELECT RawInfoUNION.*, [enter date] AS DateEnter
FROM (SELECT Information.Item, Location, Supplier, Used, Sum(Nz([Raw Data]![Stock],0)) AS Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item Group By Item, Location, Supplier
UNION SELECT [Information 2].Item, Location, Supplier, Sum(Nz([Raw Data 2]![Used],0)) AS Used, Sum(Nz([Raw Data 2]![Stock],0)) AS Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item) Group By Item, Location,Supplier) AS RawInfoUNION
WHERE (((RawInfoUNION.Location)="NJ") OR (((RawInfoUNION.Location)="CA"));

[RayMilhon]

In the second part of the Union in the On part of the left join you have a ) prior to the Group by Item, Location,supplier) Take that one out.

[Jerseynjphillypa]

Now I'm getting a error sayng Syntax error in query expression '((RawInfoUnion.Locattion)="NJ") OR (((RawInfoUNION.Location)="CA")):'

I called the query RayTest and attached the dbm Make sure you copy the code into raytest query since its missing the last WHERE statement

INSERT INTO AllDataTogether ( DateEnter )SELECT RawInfoUNION.*, [enter date] AS DateEnter
FROM (SELECT Information.Item, Location, Supplier, Used, Sum(Nz([Raw Data]![Stock],0)) AS Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item Group By Item, Location, Supplier
UNION SELECT [Information 2].Item, Location, Supplier, Sum(Nz([Raw Data 2]![Used],0)) AS Used, Sum(Nz([Raw Data 2]![Stock],0)) AS Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item Group By Item, Location,Supplier) AS RawInfoUNION
WHERE (((RawInfoUNION.Location)="NJ") OR (((RawInfoUNION.Location)="CA"));

If I remove the WHERE statement but I wish to include this I get the following error message
The specified field 'Item' could refer to more than one table listed in the FROM clause of your SQL statement

[Jerseynjphillypa]

Thank you for all your help RayMilhon

I was able to figure it out. The table was not being properly called

[RayMilhon]

Glad you got it figured out.