R
Ray
I have a table which contains both sales info and purchases info. I created a
Query to show me the sales info. The sales are related to a specific Venue
event. The query is used in a subform for that Venue. The sql is as follows:
SELECT SalesTable.*, [ProductQtySold]*[SalesPrice] AS TotalSale,
[PurchaseQty]*[PurchaseCost] AS TotalCost, SalesTable.ProductQtySold,
[ProductQtySold]*[AvgCost] AS NetProfit
FROM (ProductTable INNER JOIN SalesTable ON ProductTable.ProductPriKey =
SalesTable.ProductPriKey) INNER JOIN ProductQuery2 ON
ProductTable.ProductPriKey = ProductQuery2.ProductPriKey;
I created two summary queries that work together to provide average purchase
cost based on the same table. They are as folows:
ProductQuery1:
SELECT ProductTable.ProductPriKey, Sum([purchaseqty]*[purchasecost]) AS
TotalCost, Sum(SalesTable.PurchaseQty) AS SumOfPurchaseQty
FROM ProductTable INNER JOIN SalesTable ON ProductTable.ProductPriKey =
SalesTable.ProductPriKey
GROUP BY ProductTable.ProductPriKey;
ProductQuery2:
SELECT ProductQuery1.ProductPriKey, [TotalCost]/[SumOfPurchaseQty] AS AvgCost
FROM ProductQuery1;
I have added ProductQuery2 to the SalesQuery so I can calculate the net
profit from a sale. It all works EXCEPT I cannot add any new records to the
SalesQuery. The Sales query allows new records to be added without
ProductQuery2 included. It wont allow new records when ProductQuery2 is
added. Can you tell me why??
Query to show me the sales info. The sales are related to a specific Venue
event. The query is used in a subform for that Venue. The sql is as follows:
SELECT SalesTable.*, [ProductQtySold]*[SalesPrice] AS TotalSale,
[PurchaseQty]*[PurchaseCost] AS TotalCost, SalesTable.ProductQtySold,
[ProductQtySold]*[AvgCost] AS NetProfit
FROM (ProductTable INNER JOIN SalesTable ON ProductTable.ProductPriKey =
SalesTable.ProductPriKey) INNER JOIN ProductQuery2 ON
ProductTable.ProductPriKey = ProductQuery2.ProductPriKey;
I created two summary queries that work together to provide average purchase
cost based on the same table. They are as folows:
ProductQuery1:
SELECT ProductTable.ProductPriKey, Sum([purchaseqty]*[purchasecost]) AS
TotalCost, Sum(SalesTable.PurchaseQty) AS SumOfPurchaseQty
FROM ProductTable INNER JOIN SalesTable ON ProductTable.ProductPriKey =
SalesTable.ProductPriKey
GROUP BY ProductTable.ProductPriKey;
ProductQuery2:
SELECT ProductQuery1.ProductPriKey, [TotalCost]/[SumOfPurchaseQty] AS AvgCost
FROM ProductQuery1;
I have added ProductQuery2 to the SalesQuery so I can calculate the net
profit from a sale. It all works EXCEPT I cannot add any new records to the
SalesQuery. The Sales query allows new records to be added without
ProductQuery2 included. It wont allow new records when ProductQuery2 is
added. Can you tell me why??