Access 2010 chapter 7 labe1 question 6. I need help

[bubbly_74]

find the customer numbers, customer names, and sales rep numbers for all customers that have open orders. Use the alias O for the Open Orders table and C for the Customer table. Each customer should appear only once.

This is what I have tried but doesn't work. I am lost!

SELECT O.[Customer Number], O.[Customer Name, O.[sales rep number, C.[Customer Number], C.[Customer Name], C.[sales rep number]
FROM [Open Orders] O, [Customer] C
WHERE O.[Customer Number] = C.[Customer Number]


AND O.[Customer Name]= C.[Customer Name]
AND O.[sales rep number]=C.[sales rep number]
;

[ItsMe]

Perhaps you can use AS to define the text that will be your Alias' name. Try creating an Alias field using the query builder for additional hints.

[bubbly_74]

I have changed my SQL to:

SELECT DISTINCT O.[Customer Number], O.[Customer Name], O.[sales rep number], C.[Customer Number], C.[Customer Name], C.[sales rep number]
FROM [Open Orders] AS O, Customer AS C;

It pulls up the data needed except the customers are pulling up more than once. How can I fix this.

[ItsMe]

Perhaps you can add additional WHERE criteria to better conform your SQL statement to the Business Rules. I think I see some Business Rules in question #6 that are not defined in your WHERE clause.

[bubbly_74]

I changed it to

SELECT DISTINCT O.[Customer Number], O.[Customer Name], O.[sales rep number], C.[Customer Number], C.[Customer Name], C.[sales rep number]
FROM [Open Orders] AS O, Customer AS C
WHERE O.[Customer Number] = C.[Customer Number]
;

I get an enter parameter box O. Customer and I get an enter parameter box O. Sales number and the following result. How do I fix it so the result shows the first column as custnum, 2nd column cust name, 3rd sales rep#?

O.Customer Number	Customer Name	sales rep number	Cust #	C.Customer Name	SR #
AM23			AM23	Amy's Store	44
CM09			CM09	Casual by Marie	51
CY12			CY12	Curlin Yoga Studio	49
FN19			FN19	Fitness Counts	51
JN34			JN34	Just Natural	49
LB20			LB20	Le Beauty Salon & Spa	49
RD03			RD03	Rose's Day Spa	51
TT21			TT21	Tan and Tone	44

[ItsMe]

Are you using the Query Builder or just trying to type the SQL? You are missing a JOIN. So either Post#3 does not include all of the syntax or you never had a functioning query.

I suggest you create a basic SELECT query and worry about the WHERE clause last.

[bubbly_74]

Just trying to use the SQL.

[ItsMe]

Define your JOIN. Define what field(s) to join on. Define the JOIN type, ie RIGHT, INNER, etc.
http://www.w3schools.com/sql/sql_join_inner.asp

[bubbly_74]

Thank you so much for your help and patients.

[bubbly_74]

Know I just have to figure out how to get the customer name to show up only once

Cust #	Customer Name	SR #
AM23	Amy's Store	44
AM23	Amy's Store	44
CM09	Casual by Marie	51
CY12	Curlin Yoga Studio	49
FN19	Fitness Counts	51
JN34	Just Natural	49
JN34	Just Natural	49
LB20	Le Beauty Salon & Spa	49
RD03	Rose's Day Spa	51
RD03	Rose's Day Spa	51
TT21	Tan and Tone	44

[bubbly_74]

I finally got it!!!!!!!!!!!!!!!!!!!

Thanks