Invoice_No = Line_Item.Invoice_No
WHERE Date LIKE '*11*2007'
AND Line_item.item_id IN
(SELECT Item.item_id FROM Item INNER JOIN
Consignment_Seller ON
Item.Consignment_Seller_ID =
Consignment_Seller.Seller_ID
WHERE name = 'Parker Smith'
AND Price_Sold IS NOT NULL);
e Report the amount due to ???Parker Smith??? for all sales of his items this month.
SELECT SUM (Line_Item_Price * fee_percent) as
Smith_comission
FROM ( ( ( Sale JOIN Line_Item ON Invoice_No )
JOIN Item ON Item_ID )
JOIN Consignment_Seller ON
Consignment_Seller_ID = Seller_ID )
WHERE name = 'Parker Smith' AND Date LIKE '%2007-11%';
In Access:
SELECT SUM(Line_Item.Line_Item_Price *
Consignment_Seller.fee_percent) AS Smith_comission
FROM ((Sale INNER JOIN Line_Item ON
Sale.Invoice_No = Line_item.Invoice_No)
INNER JOIN Item ON
Item.Item_ID = Line_Item.Item_Id)
INNER JOIN Consignment_Seller ON
Consignment_Seller.Seller_ID =
Item.Consignment_Seller_ID
WHERE Consignment_Seller.name = 'Parker Smith'
AND Sale.Date LIKE '*11*2007*';
f Report all sales of items not on consignment (Consignment_Seller_ID is NULL) this month.
SELECT Item_ID
FROM ( ( Sale JOIN Line_Item ON Invoice_No )
JOIN Item ON Item_ID )
WHERE Consignment_Seller_ID IS NULL
AND Price_sold IS NOT NULL
AND Date LIKE '%2007-11%';
In Access:
SELECT Line_item.
Pages:
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540