Im doing an assignment for my database class and the examples that we are given are in Oracle, but we are told to do the problems in MS Access. There are some functions that I do not know the equivalent of in Access. If you guys could help me out I would greatly appreciate it. The functions are "NOT IN" and "UNIQUE"

30) FIGURE P5.30 The Customer Balance Characteristics for Customers Who
Did Not Make Purchases During the Invoice Period


The command line is shown on the screen, just above its output, i.e.,

SELECT CUS_CODE, CUS_BALANCE
FROM CUSTOMER
WHERE CUS_CODE NOT IN (SELECT UNIQUE CUS_CODE FROM INVOICE);


31) Now find the customer balance summary for all customers who have not made purchases during the current invoicing period. The results are shown in Figure P5.31.

FIGURE P5.31 A Summary of Customer Balance Characteristics for Customers Who Did Not Make Purchases During the Current Invoice Period

The required SQL command is shown on the screen, just above its output, i.e.,

SELECT SUM(CUS_BALANCE), MIN(CUS_BALANCE), MAX(CUS_BALANCE),
AVG(CUS_BALANCE)
FROM CUSTOMER
WHERE CUS_CODE NOT IN
(SELECT UNIQUE CUS_CODE FROM INVOICE);

 

 

Thanks bro, I needed this

 

Try distinct for unique

 

cool that worked

 

NOT IN can be replicated by using a join.

 

SELECT CUSTOMER.CUS_CODE, CUSTOMER.CUS_BALANCE FROM CUSTOMER JOIN INVOICE WHERE DISTINCT INVOICE.CUS_CODE != CUSTOMER.CUS_CODE;

 

now, since i prorate by the hour, that's $60. you can use paypal, josh@3io.com