UtterAccess Discussion Forums > Nested Sub-Queries

nafi

Dec 26 2008, 02:21 AM

I have an Access database which keeps track of Invoices and Payments. The schema of the db is as below

Clients (CustomerID, ......)
Invoice (CustomerID, InvoiceNumber, DatePurchased, Discount)
InvoiceItem (Product, InvoiceNumber, UnitPrice, Quantity)
Payment (InvoiceNumber, Payment Received, DateReceived)

I am trying to list down clients which have invoices that has not been settled. There is a 10% tax charged and the final amount would be rounded off. Here is what I've gotten so far

CODE

SELECT * FROM Clients WHERE [CustomerID] IN (
                                            SELECT [CustomerID] FROM Invoice WHERE [InvoiceNumber] IN (
                                SELECT a.[InvoiceNumber] FROM Invoice a, InvoiceItem b, Payment c WHERE
                                                    a.[InvoiceNumber] = b.[InvoiceNumber] AND
                                a.[InvoiceNumber] = c.[InvoiceNumber]
                                                    GROUP BY a.[InvoiceNumber]
                                    HAVING (ROUND(1.1*SUM(b.[UnitPrice] * b.[Quantity]), 0) - a.[Discount] - SUM(c.[Payment Received]) > 0
                                )
                        );

I've been working on this for days and all i got is Missing ), ], or Item in query expression

Any help is greatly appreciated.

BananaRepublic

Dec 26 2008, 02:36 AM

I think you need an extra ) at the end of this statement:

QUOTE

HAVING (ROUND(1.1*SUM(b.[UnitPrice] * b.[Quantity]), 0) - a.[Discount] - SUM(c.[Payment Received])

But I would say that instead of frying your eyeballs, try this approach I use for building the subquery:

Open a new query in query builder, and build the SQL for innermost subquery there. Make sure it's valid. Open another new query then build the main query without the subquery (use a bogus criteria if you need to) to verify that SQL is valid and once its valid, copy & paste in the subquery. This way, you don't have to go on a wild () chase.

HTH.

nafi

Dec 26 2008, 02:48 AM

QUOTE

I think you need an extra ) at the end of this statement:

QUOTE

HAVING (ROUND(1.1*SUM(b.[UnitPrice] * b.[Quantity]), 0) - a.[Discount] - SUM(c.[Payment Received])

After adding ) as u mentioned, I got this error

CODE

You tried to execute a query that does not include the specified expression 'ROUND(1.1*SUM(b.[Don Gia/UnitPrice]*b.[So Luong/ Quantity]),0)-a.[Chiet khau/ Discount]-SUM(c.[Sýò tra tiên ðýõòc thýÌa/ Payment Received])>0 And [So Hoa Dan/ InvoiceNumber]=' as part of an aggregate function.

What does it mean?

QUOTE

But I would say that instead of frying your eyeballs, try this approach I use for building the subquery:

Open a new query in query builder, and build the SQL for innermost subquery there. Make sure it's valid. Open another new query then build the main query without the subquery (use a bogus criteria if you need to) to verify that SQL is valid and once its valid, copy & paste in the subquery. This way, you don't have to go on a wild () chase.

HTH.

I'm not proficient in Access and I have no idea on how to navigate through its GUI.

BananaRepublic

Dec 26 2008, 02:56 AM

Regarding the error, it looks like the SQL is now well-formed but not valid because you have to include all non-aggregated columns in a GROUP BY clause.

As for GUI, it's really easy.

On the ribbon, there should be "Create"; click on this then click on "Query Design" which is on far right. A new query builder will pop up. Press OK for the Add tables (e.g. don't add any tables just yet). Then right-click on the gray area on the query builder to get a context menu then select "SQL View"; you can then paste that SQL into there then right-click to get same menu again and get "Design View" to go back to the initial view. You can then drag'n'drop and experiment with it.

HTH.

nafi

Dec 26 2008, 03:46 AM

QUOTE

Regarding the error, it looks like the SQL is now well-formed but not valid because you have to include all non-aggregated columns in a GROUP BY clause.

As for GUI, it's really easy.

On the ribbon, there should be "Create"; click on this then click on "Query Design" which is on far right. A new query builder will pop up. Press OK for the Add tables (e.g. don't add any tables just yet). Then right-click on the gray area on the query builder to get a context menu then select "SQL View"; you can then paste that SQL into there then right-click to get same menu again and get "Design View" to go back to the initial view. You can then drag'n'drop and experiment with it.

HTH.

The GROUP BY is only used in the sub query and there's only 1 selected column there.

I tried the Query Design u mentioned and it gives me the same error message.