For Tutor daniel
Selvon
sc_access2010_c9_l1a_fahedalhasan_1.accdb| Verify Name | Do Not Delete |
|---|---|
| Fahed Alhasan�������������������������� | 81EBA32022962E9D-50F9A7263BAE2684 |
| Customer Number | Customer Name | Street | City | State | Postal Code | Customer Type | Balance | Amount Paid | Sales Rep Number |
|---|---|---|---|---|---|---|---|---|---|
| AM23 | Amy's Store | 223 Johnson | Oxford | TN | 37021 | WMN | ¤ 195.00 | ¤ 1,695.00 | 44 |
| BL15 | Blondie's on Main | 3294 Main | Oxford | TN | 37021 | WMN | ¤ 555.00 | ¤ 1,350.00 | 49 |
| CM09 | Casual by Marie | 3140 Halsted | Ashton | VA | 20123 | WMN | ¤ 295.00 | ¤ 1,080.00 | 51 |
| CY12 | Curlin Yoga Studio | 1632 Clark | Georgetown | NC | 28794 | SAL | ¤ 145.00 | ¤ 710.00 | 49 |
| DG14 | Della's Designs | 312 Gilham | Granger | NC | 27036 | WMN | ¤ 340.00 | ¤ 850.00 | 44 |
| EC07 | Environmentally Casual | 1805 Broadway | Pineville | VA | 22503 | WMN | ¤ 0.00 | ¤ 1,700.00 | 44 |
| FN19 | Fitness Counts | 675 Main | Oxford | TN | 37021 | SAL | ¤ 345.00 | ¤ 1,950.00 | 51 |
| JN34 | Just Natural | 2200 Lawrence | Ashton | VA | 20123 | WMN | ¤ 360.00 | ¤ 700.00 | 49 |
| LB20 | Le Beauty Salon & Spa | 13 Devon | Lowton | TN | 37084 | SAL | ¤ 200.00 | ¤ 1,250.00 | 49 |
| NC25 | Nancy's Place | 1027 Wells | Walburg | NC | 28819 | WMN | ¤ 240.00 | ¤ 550.00 | 44 |
| RA21 | Ralph's | 72 Main | Lowton | TN | 37084 | SPG | ¤ 0.00 | ¤ 0.00 | 55 |
| RD03 | Rose's Day Spa | 787 Monroe | Pineville | VA | 22503 | SAL | ¤ 0.00 | ¤ 975.00 | 51 |
| TT21 | Tan and Tone | 1939 Congress | Ashton | VA | 20123 | SAL | ¤ 160.00 | ¤ 725.00 | 44 |
| TW56 | The Workout Place | 34 Gilham | Granger | NC | 27036 | SAL | ¤ 680.00 | ¤ 125.00 | 51 |
| WS34 | Woody's Sporting Goods | 578 Central | Walburg | NC | 28819 | SPG | ¤ 1,235.00 | ¤ 0.00 | 49 |
| Order Number | Amount | Customer Number |
|---|---|---|
| 303 | ¤ 175.00 | AM23 |
| 304 | ¤ 125.00 | AM23 |
| 306 | ¤ 235.00 | CM09 |
| 307 | ¤ 150.00 | CY12 |
| 308 | ¤ 248.00 | FN19 |
| 310 | ¤ 126.00 | JN34 |
| 311 | ¤ 340.00 | JN34 |
| 312 | ¤ 164.00 | LB20 |
| 313 | ¤ 532.00 | RD03 |
| 315 | ¤ 324.00 | RD03 |
| 317 | ¤ 225.00 | TT21 |
| Sales Rep Number | Last Name | First Name | Street | City | State | Postal Code | Base Pay YTD | Comm Rate | Start Date | Notes |
|---|---|---|---|---|---|---|---|---|---|---|
| 44 | Jones | Pat | 43 Third | Oxford | TN | 37021 | ¤ 13,000.00 | 0.05 | 5/10/10 | Excellent computer skills. Helps to train new employees. |
| 49 | Gupta | Pinn | 678 Hillcrest | Georgetown | NC | 28794 | ¤ 15,000.00 | 0.06 | 6/14/10 | Has a BBA in Marketing. Working on an MBA in Management. |
| 51 | Ortiz | Gabe | 982 Victoria | Ashton | VA | 20123 | ¤ 12,500.00 | 0.05 | 5/23/11 | Certified Personal Trainer. Enjoys working with health and fitness centers. |
| 55 | Sinson | Terry | 45 Elm | Walburg | NC | 28819 | ¤ 500.00 | 0.04 | 1/16/12 | Has an AA degree. Working on a BBA in Management. |
PARAMETERS __Sales Rep Number Value;
SELECT DISTINCTROW *
FROM Customer AS [Sales Rep Master Form]
WHERE ([__Sales Rep Number] = [Sales Rep Number]);
PARAMETERS __Customer Number Value;
SELECT DISTINCTROW *
FROM [SELECT [Open Orders].[Order Number], [Open Orders].[Amount], [Open Orders].[Customer Number] FROM [Open Orders]]. AS [Sales Rep Master List]
WHERE ([__Customer Number] = [Customer Number]);
SELECT DISTINCTROW *
FROM Customer;
SELECT [Sales Rep].[Sales Rep Number], [Sales Rep].[First Name], [Sales Rep].[Last Name], [Sales Rep].[Start Date], [Sales Rep].[Base Pay YTD], [Sales Rep].[Comm Rate], [Sales Rep].Notes
FROM [Sales Rep];
SELECT DISTINCTROW *
FROM Customer;
SELECT [Sales Rep].[Sales Rep Number], [Sales Rep].[First Name], [Sales Rep].[Last Name], [Customer].[Customer Number], [Customer].[Customer Name], [Customer].[Balance]
FROM [Sales Rep] INNER JOIN Customer ON [Sales Rep].[Sales Rep Number] =[Customer].[Sales Rep Number];
SELECT DISTINCTROW *
FROM Customer;
SELECT [Sales Rep].[Sales Rep Number], [Sales Rep].[First Name], [Sales Rep].[Last Name], [Sales Rep].Notes
FROM [Sales Rep]
WHERE ((([Sales Rep].Notes) Like "*computer*") AND (([Sales Rep].[Start Date])<#1/1/2012#));
SELECT Customer.[Customer Number], Customer.[Customer Name], Customer.[Balance], Customer.[Amount Paid], Customer.[Sales Rep Number]
FROM Customer;
UPDATE Customer
SET Customer.[Customer Type] = "WMN";
SELECT [Sales Rep].[Sales Rep Number], [Sales Rep].[Last Name], [Sales Rep].[First Name], Customer.[Customer Number], Customer.[Customer Name], Customer.Street, Customer.City, Customer.State, Customer.[Postal Code], Customer.[Customer Type], Customer.Balance, Customer.[Amount Paid]
FROM [Sales Rep] INNER JOIN Customer ON [Sales Rep].[Sales Rep Number]=Customer.[Sales Rep Number];
SELECT [Sales Rep].[Sales Rep Number], Customer.[Customer Number], [Open Orders].[Order Number], [Open Orders].Amount
FROM ([Sales Rep] INNER JOIN Customer ON [Sales Rep].[Sales Rep Number] = Customer.[Sales Rep Number]) INNER JOIN [Open Orders] ON Customer.[Customer Number] = [Open Orders].[Customer Number]
ORDER BY [Sales Rep].[Sales Rep Number], Customer.[Customer Number], [Open Orders].[Order Number];
TRANSFORM Sum(Customer.[Amount Paid]) AS [SumOfAmount Paid]
SELECT Customer.[State], Sum(Customer.[Amount Paid]) AS [Total Of Amount Paid]
FROM Customer
GROUP BY Customer.[State]
PIVOT Customer.[Sales Rep Number];
