Table Name: CUSTOMERS_1 CUST_NUM CUST_LNAME CUST_FNAME CUST_BAL 2001 James Willi

Question

Table Name: CUSTOMERS_1 CUST_NUM CUST_LNAME CUST_FNAME CUST_BAL 2001 James William $2,999 2002 Crane Frasier $983

Table Name: CUSTOMERS_2 CUST_NUM CUST_LNAME CUST_FNAME CUST_BAL 1999 Anderson Anne $510 2000 Bryant Juan $21 2002 Crane Frasier $983 2003 Dent George $1,790 Table Name: CUST_INVOICES INV_NUM CUST_NUM INV_DATE INV_AMOUNT 9000 2000 23-Mar-16 245 9001 2001 23-Mar-16 260 9002 2001 30-Mar-16 275 9003 1000 10-Apr-16 286

using this information, i need help formulating an sql query for Microsoft Access: Write a SQL code that will show CUST_NUM, CUST_LNAME, and CUST_FNAME for the one who has minimal INV_AMOUNT

The last solution of :

Ans:- Here we will use UNION ALL to fetch all the results from CUSTOMER_1 and CUSTOMER_2. The query will be SELECT CUST_NUM,CUST_LNAME, CUST_FNAME FROM CUSTOMER WHERE CUST_NUM IN(SELECT cust_num FROM INVOICE WHERE INV_AMOUNT IN (SELECT min(INV_AMOUNT)FROM INVOICE ) ) UNION ALL SELECT CUST_NUM,CUST_LNAME, CUST_FNAME FROM CUSTOMER_2 WHERE CUST_NUM IN(SELECT cust_num FROM INVOICE WHERE INV_AMOUNT IN (SELECT min(INV_AMOUNT)FROM INVOICE ) )

Produces the error of "an action query cannot be used as a row source". Also the tables in the response are named incorrectly for the information provided above.

Explanation / Answer

Hi,

I created below query to create the required tables as per your question.

CREATE TABLE CUSTOMERS_1 (
CUST_NUM int,
CUST_LNAME varchar(255),
CUST_FNAME varchar(255),
CUST_BAL int
);

CREATE TABLE CUSTOMERS_2 (
CUST_NUM int,
CUST_LNAME varchar(255),
CUST_FNAME varchar(255),
CUST_BAL int
);

CREATE TABLE CUST_INVOICES (
INV_NUM int,
CUST_NUM int,
INV_DATE date,
INV_AMOUNT int
);

INSERT INTO CUSTOMERS_1 VALUES( 2001, 'James', 'William', 2999);
INSERT INTO CUSTOMERS_1 VALUES( 2002, 'Crane', 'Frasier', 983);

INSERT INTO CUSTOMERS_2 VALUES( 1999, 'Anderson', 'Anne', 510);
INSERT INTO CUSTOMERS_2 VALUES( 2000, 'Bryant', 'Juan', 21);
INSERT INTO CUSTOMERS_2 VALUES( 2002, 'Crane', 'Frasier', 983);
INSERT INTO CUSTOMERS_2 VALUES( 2003, 'Dent', 'George', 1790);

INSERT INTO CUST_INVOICES VALUES( 9000, 2000, '23-Mar-16', 245);
INSERT INTO CUST_INVOICES VALUES( 9001, 2001, '23-Mar-16', 260);
INSERT INTO CUST_INVOICES VALUES( 9002, 2001, '30-Mar-16', 275);
INSERT INTO CUST_INVOICES VALUES( 9003, 1000, '10-Apr-16', 286);



Now to retrieve the query which you asked for:

SELECT CUST_NUM,CUST_LNAME, CUST_FNAME FROM CUSTOMERS_1 WHERE CUST_NUM IN(SELECT cust_num FROM CUST_INVOICES WHERE INV_AMOUNT IN (SELECT min(INV_AMOUNT) FROM CUST_INVOICES ) )
UNION ALL
SELECT CUST_NUM,CUST_LNAME, CUST_FNAME FROM CUSTOMERS_2 WHERE CUST_NUM IN(SELECT cust_num FROM CUST_INVOICES WHERE INV_AMOUNT IN (SELECT min(INV_AMOUNT) FROM CUST_INVOICES ) )

Above query produces the below results:

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.