Help!

[gabej]

My teacher is awful....I need help with questions G and H

[jzwp11]

The first step is to sum up all of the orders by customer number. In question E, you had to create a query that shows all of the order details, so we can use that query in another query that calculates the sum.

So create a new query and when prompted, use the query you created for question E. Select only the customer number field and the extended price field. In the totals row of the lower grid (if you don't see the totals row, click on either the customer number or price column, once highlighted do a right click and select Totals from the popup menu), set the customer number to Group By and the extended price field to Sum. Run the query, you should get the sum of all orders for all customer by customer number.
Save the query.

Now, create a query similar to the query you created for question B, but this time only select the customer number field and all of the fields from the sales rep table. Save the query.

Create a third query and when prompted, bring in both of the queries you just created. In the upper part of the grid, make a join between the customer number fields in both queries (click and drag the field from one query to the other to make the join). Select the fields as instructed. You will need a calculated field to determine the commission from open orders by multiplying the commission rate field by the sum of the extended prices. Access probably named that sum field as SumOfExtendedPrice (or something along those lines). So the expression will be COMMRATE* SumOfExtendedPrice. You can give that expression a name by preceeding the expression with a name followed by a colon, something like this:

CommOnNewSales:COMMRATE* SumOfExtendedPrice

Now to find the updated commission just create another field with the expression CommOnNewSale+TOTCOMM

This expression will yield the sales reps new total commission, so you can use that for the expression name

NewTOTCOM: CommOnNewSale+TOTCOMM

For question H, you would use a similar approach, you would start with a query similar to that created for question E but include the partnumber field (since that was not included in question E).

Create a query based on the query described above, bring in the part number and quantity fields. Add the totals row, (Group By part number and sum the quantity field). Save the query.

Create another query that brings in the part table and the query you just created. Join the two via the part number field. Select the fields you want and add the calculated fields as instructed. For the field that says whether to reorder or not you will probably need the IIF() function. (Check the Access help section for more on that).

In terms of structure, I would not store the quantity on hand in the part table, nor would I have the total commission field in the sales rep table, nor the beginning balance in the customer table. These all represent dynamic values so they should be calculated on the fly when needed. I assume that they are there just for teaching purposes (I hope).