Table Design Problem?

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
 
G

Guest

Hello B. Karthick,
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.
Click to expand...

It sounds like two issues are going on here. First, I'm guessing that the
relationship that shows up in the QBE is due to a user-configurable option,
which you can set using Tools > Options... | Tables/Queries tab. You should
see an item that reads "Enable AutoJoin" in the lower left corner. This is
likely checked. Toggle this selection, and press F1 to read context sensitive
help.

The default join between two tables is an inner join. This means that
records will only be returned when matching key values are present in BOTH
tables. I suspect that you do not have any ProductID values that are common
to both tables.

Tom
____________________________________

:

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
 
Top