Customer ID Srvc Weight Paper Weight Other 1 1/17/2007 8 15 1 2/15/2007 32 85 1
ID: 3780060 • Letter: C
Question
Customer ID
Srvc
Weight Paper
Weight Other
1
1/17/2007
8
15
1
2/15/2007
32
85
1
1/7/2007
12
43
2
1/7/2007
19
0
2
1/22/2007
28
174
2
1/15/2007
18
40
3
1/15/2007
5
8
3
1/22/2007
16
32
3
2/4/2007
7
12
20
1/22/2007
35
60
80
1/15/2007
10
10
80
1/7/2007
9
13
80
1/22/2007
16
18
80
2/4/2007
18
21
86
2/1/2007
34
78
86
1/1/2007
11
23
86
2/25/2007
38
56
87
1/16/2007
23
12
87
1/19/2007
14
22
87
1/2/2007
56
34
88
2/21/2007
54
70
88
2/22/2007
12
45
88
2/23/2007
45
10
89
1/24/2007
11
11
89
2/2/2007
44
24
89
2/2/2007
65
11
90
2/2/2007
13
11
90
2/1/2007
4
78
Customer Record Table
Customer ID
Last_name
First_name
Street
City
State
Zip Code
Phone
First Pickup
1
Wagoner
Sam
5480 Alpine Street
Boulder
CO
80308
(303)165-545
5/25/2004
2
Calahan
Eliza
2140 Edgewood Avenue
Boulder
CO
80308
(303)886-6003
5/25/2004
3
Lake
Jame
701 Eastman Road
Boulder
CO
80308
(303)562-4499
8/25/2005
4
Meadows
sara
Pond Hill drive
Boulder
CO
80308
(303)792-3646
2/28/2004
20
Smith
Alto
114 Lexington Street
Boulder
CO
80308
(303)838-7111
6/2/2004
64
Monarch
Shiela
431 Phillips Lane
Boulder
CO
80308
(303)352-4847
7/17/2005
65
Guo
Amu
1935 Snow Avenue
Boulder
CO
80308
(303)555-6731
5/19/2005
80
Rivera
Juan
482 Weston Ave
Boulder
CO
80308
(303)815-2456
12/28/2004
85
Williams
Max
230 Southpark Circle
Boulder
CO
80308
(303)333-0000
7/19/2003
86
Kattel
Sandhya
15755 Evergreen Ave
Applevalley
MN
55124
(763)742-9528
1/7/2005
87
Mahat
Nirmal
4141 Parklawn Ave
Edina
MN
55435
(580)399-5256
2/4/2005
Customer ID
Srvc
Weight Paper
Weight Other
1
1/17/2007
8
15
1
2/15/2007
32
85
1
1/7/2007
12
43
2
1/7/2007
19
0
2
1/22/2007
28
174
2
1/15/2007
18
40
3
1/15/2007
5
8
3
1/22/2007
16
32
3
2/4/2007
7
12
20
1/22/2007
35
60
80
1/15/2007
10
10
80
1/7/2007
9
13
80
1/22/2007
16
18
80
2/4/2007
18
21
86
2/1/2007
34
78
86
1/1/2007
11
23
86
2/25/2007
38
56
87
1/16/2007
23
12
87
1/19/2007
14
22
87
1/2/2007
56
34
88
2/21/2007
54
70
88
2/22/2007
12
45
88
2/23/2007
45
10
89
1/24/2007
11
11
89
2/2/2007
44
24
89
2/2/2007
65
11
90
2/2/2007
13
11
90
2/1/2007
4
78
Customer Record Table
Part 1: Create a query that spans both tables. Part 2: Modify the Query to select only those Customers that had service dates in 2 specific months (e.g. Jan & Feb 2007). Part 3: Create a report that displays the results of your query. This report should list customer information, and under each customer record should list all related customer service records. Read plug-in T8 Use the Report Wizard Select an appropriate field for the Grouping parameter in the Report wizard. This is probably the most challenging part of this assignment. Your goal is that for the listing of each customer record, the associated Customer Service records (ie. the records from the "Customer (service) Record" table should be listed below it, but only displaying the Service date and Weight fields (not repeating the Customer ID). Reports Reports are used primarily for printing selected database information. A report labels, groups, sorts, and summarizes the data it presents. Like a form, a report can display data directly from one or more tables or it can display the results of a query.Explanation / Answer
I am assuming that first table name is Customer_Service and second table name is Customer.
Part A
This query will simply join both customer and customer service table. I have used left outer join since it is not necessary that all customers will have customer service related information.
select * from Customer Cus left outer join Customer_Service Csr where Cus.CustomerID = Csr.CustomerID
Part B
In this query we are specifically extracting customers who had service in the month Jan, Feb 2007. Customers without service records can't be displayed so we have to use inner join, so that if and only if customer has service record he will considered.
select * from Customer Cus inner join Customer_Service Csr where Cus.CustomerID = Csr.CustomerID and Csr.Srvc between '1/1/2007' and '2/28/2007 23:59:59.999'
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.