B
B Karthick
Dear,
I have the following tables ....
Integer in front indicates name of table.
+ in front indicates Primary key
- in front indicates Foreign key
-------- indicates that there are more fields in the tables
1. Indent
+ Indent ID
------------
2. Products
+ ProductID
-------------
3. IndentLineItem
+ IndentLineItemID
- IndentID
- ProductID
------------
4. DeliveryLineItem
+ DeliveryLineItemID
- IndentLineItemID
- DeliveryID
Quantity
--------------
5. Delivery
+ DeliveryID
------------------
6 RawMaterial
- IndentID
- ConversionID
ProductID
Quantity
-----------
7 Conversion
+ ConversionID
- ProductID
---------
I need to calculate the Stock in hand by running the following query.
SELECT DISTINCTROW IndentLineItem.ProductID, Product.Description AS Product,
Product.ProductType AS Type,
Sum([DeliveryLineItem].[Quantity]-[RawMaterial].[Quantity]) AS StockInHand
FROM RawMaterial, Product INNER JOIN (IndentLineItem INNER JOIN
DeliveryLineItem ON IndentLineItem.IndentLineItemNo =
DeliveryLineItem.IndentLineItemNo) ON Product.ProductID =
IndentLineItem.ProductID
GROUP BY IndentLineItem.ProductID, Product.Description, Product.ProductType;
When I include the RawMaterial Table in the QBE, the query does not return
any recordset. There is a relationship between RawMaterial.ProductID and
Product.ProductID which I never created.
Shall be thankful if you could help me resolve this.
Best Regards
B Karthick
I have the following tables ....
Integer in front indicates name of table.
+ in front indicates Primary key
- in front indicates Foreign key
-------- indicates that there are more fields in the tables
1. Indent
+ Indent ID
------------
2. Products
+ ProductID
-------------
3. IndentLineItem
+ IndentLineItemID
- IndentID
- ProductID
------------
4. DeliveryLineItem
+ DeliveryLineItemID
- IndentLineItemID
- DeliveryID
Quantity
--------------
5. Delivery
+ DeliveryID
------------------
6 RawMaterial
- IndentID
- ConversionID
ProductID
Quantity
-----------
7 Conversion
+ ConversionID
- ProductID
---------
I need to calculate the Stock in hand by running the following query.
SELECT DISTINCTROW IndentLineItem.ProductID, Product.Description AS Product,
Product.ProductType AS Type,
Sum([DeliveryLineItem].[Quantity]-[RawMaterial].[Quantity]) AS StockInHand
FROM RawMaterial, Product INNER JOIN (IndentLineItem INNER JOIN
DeliveryLineItem ON IndentLineItem.IndentLineItemNo =
DeliveryLineItem.IndentLineItemNo) ON Product.ProductID =
IndentLineItem.ProductID
GROUP BY IndentLineItem.ProductID, Product.Description, Product.ProductType;
When I include the RawMaterial Table in the QBE, the query does not return
any recordset. There is a relationship between RawMaterial.ProductID and
Product.ProductID which I never created.
Shall be thankful if you could help me resolve this.
Best Regards
B Karthick