W
WStoreyII
have a query: as follows
Tables
-Vendors
Id
Name
Description
Code
-Products
Id
Vendor Id
Code
Description
Cost
ReOrder
-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity
Relationships
-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id
Sql View
SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;
Here is the problem though.
The query is not grouping like it should be.
What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.
What am i doing wrong?
Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.
thanks
WStoreyII
Tables
-Vendors
Id
Name
Description
Code
-Products
Id
Vendor Id
Code
Description
Cost
ReOrder
-Inventory
Id
Date
Product Id
Employee Id
Type
Quantity
Relationships
-Vendors is Related to Products By The Vendor Id
-Inventory is related to products by the product id
Sql View
SELECT tbl_Vendors.Code AS Vendor, tbl_Products.Code AS Product,
Sum(tbl_Inventory.Quantity) AS [Count], tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity])) AS Need
FROM tbl_Inventory INNER JOIN (tbl_Products LEFT JOIN tbl_Vendors ON
tbl_Products.[Vendor Id] = tbl_Vendors.Id) ON tbl_Inventory.[Product Id] =
tbl_Products.Id
GROUP BY tbl_Vendors.Code, tbl_Products.Code, tbl_Products.ReOrder,
IIf(([ReOrder]-[Quantity])<=0,0,([ReOrder]-[Quantity]))
ORDER BY tbl_Vendors.Code;
Here is the problem though.
The query is not grouping like it should be.
What i want is that for each product it will take the sum of the quantity
fields for each record for that product but for some reason it is showing
each item for each product.
What am i doing wrong?
Also need a good tutorial for explaining queries, joins,relationships
crosstab queries, ect.
thanks
WStoreyII