I'm having a problem making my queries work properly. I'm using Microsoft Access. First, here's a look at my tables:
Customers:
customerID
customerName
customerAddress
customerCredit
customerBalance
Items:
itemID
itemName
itemPrice
itemQuantity
Orders:
orderID
itemID
repID
customerID
orderQuantity
orderDate
Reps:
repID
repFirstName
repLastName
Okay, this is what I'm trying to do. I'm attempting to calculate the inventory after orders are processed. I don't care if it's just a query, or if it's a new table, whether it's done by INNER JOIN or by a nested query ... I just need help figuring out how to make it calculate the remaining inventory. Here is my SQL:
SELECT item.itemID, item.itemName, item.itemQuantity AS Quantity_Before_Sales, orders.orderQuantity AS Number_Sold, Sum(item.itemQuantity-orders.orderQuantity) AS Quantity_After_Sales
FROM item INNER JOIN orders ON item.itemID=orders.itemID
GROUP BY item.itemID, item.itemName, item.itemQuantity, orders.orderQuantity;
Here is the results, the results are not what I'm looking for. The mistakes are outlined after the XML section (exported as an XML, because it was easier to copy and paste to the BBS):
<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Inventory.xsd" generated="2009-09-12T01:48:49">
- <Inventory>
<itemID>CT120</itemID>
<itemName>Scissors</itemName>
<Quantity_Before_Sales>5</Quantity_Before_Sales>
<Number_Sold>3</Number_Sold>
<Quantity_After_Sales>2</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT121</itemID>
<itemName>Dell Computer</itemName>
<Quantity_Before_Sales>12</Quantity_Before_Sales>
<Number_Sold>5</Number_Sold>
<Quantity_After_Sales>7</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT122</itemID>
<itemName>Couch</itemName>
<Quantity_Before_Sales>3</Quantity_Before_Sales>
<Number_Sold>20</Number_Sold>
<Quantity_After_Sales>-17</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT122</itemID>
<itemName>Couch</itemName>
<Quantity_Before_Sales>3</Quantity_Before_Sales>
<Number_Sold>3</Number_Sold>
<Quantity_After_Sales>0</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT123</itemID>
<itemName>DSL Modem</itemName>
<Quantity_Before_Sales>50</Quantity_Before_Sales>
<Number_Sold>3</Number_Sold>
<Quantity_After_Sales>47</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT123</itemID>
<itemName>DSL Modem</itemName>
<Quantity_Before_Sales>50</Quantity_Before_Sales>
<Number_Sold>4</Number_Sold>
<Quantity_After_Sales>46</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT124</itemID>
<itemName>Speakers</itemName>
<Quantity_Before_Sales>12</Quantity_Before_Sales>
<Number_Sold>17</Number_Sold>
<Quantity_After_Sales>-5</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT125</itemID>
<itemName>Potatoes</itemName>
<Quantity_Before_Sales>200</Quantity_Before_Sales>
<Number_Sold>13</Number_Sold>
<Quantity_After_Sales>187</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT126</itemID>
<itemName>Picture Frames</itemName>
<Quantity_Before_Sales>40</Quantity_Before_Sales>
<Number_Sold>2</Number_Sold>
<Quantity_After_Sales>38</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT127</itemID>
<itemName>Window Shades</itemName>
<Quantity_Before_Sales>33</Quantity_Before_Sales>
<Number_Sold>1</Number_Sold>
<Quantity_After_Sales>32</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT128</itemID>
<itemName>WebCam</itemName>
<Quantity_Before_Sales>7</Quantity_Before_Sales>
<Number_Sold>60</Number_Sold>
<Quantity_After_Sales>-53</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT129</itemID>
<itemName>Paint</itemName>
<Quantity_Before_Sales>74</Quantity_Before_Sales>
<Number_Sold>12</Number_Sold>
<Quantity_After_Sales>62</Quantity_After_Sales>
</Inventory>
- <Inventory>
<itemID>CT130</itemID>
<itemName>Teddy Bear</itemName>
<Quantity_Before_Sales>40</Quantity_Before_Sales>
<Number_Sold>50</Number_Sold>
<Quantity_After_Sales>-10</Quantity_After_Sales>
</Inventory>
</dataroot>
Every place that contains two listings for the same item demonstrates the problem with my inventory SQL code. As far as I can tell, only two items have more than one listing, all the others have a single listing. So, I need to compensate for all items that have more than one listing as well as the items that have a single listing, because I want to show all of these in the same query .... then make a report of it.