Nesting Left Joins

[Margarita]

Hello, I am working in Access 2003. I have a Vendors Table and a PurchaseOrders table in a one to many relationship, though not every vendor has a purchase order entered so far. The purchase orders tracks the fiscal year in which a PO was issued through BHSRequisitionNum. What I want to do is create a report which will provide an at-a-glance vendor profile detailing the amount spent in FY12, amount spent in previous fiscal years, the total amount closed and the amount remaining on the budget (each vendor has an associated yearly budget).

The way I am trying to accomplish this is through a query which does the following:
([Vendors] LEFT JOIN [select statement which captures the sum of amount spent and amount closed in FY12])
LEFT JOIN
[select statement which captures the sum of the amount spent and closed in previous fiscal years]

That way, I will have all the relevant fields which I can then sum, subtract from budget, etc straight on the report.

What I am having a lot of trouble with is the formatting of the nested left joins. I feel like I am very close to what I want, but there is a bracket in there somewhere that I am missing (getting a join statement syntax error). The following is my sql statement. Can anyone help me edit my syntax? Thank you!!!

SELECT Vendors.VendorName, 
Vendors.Current_FY_Budget, 
SumofPOAmountActual1, SumofClosed,
NonContractVendors.MultYearContract,
SumofAmountActualPrev, SumofClosedPrev
 
FROM 
[(Vendors as Vend) 
LEFT JOIN 
(SELECT V.VendorName, Sum(IIf(P.BHSRequisitionNum Like '2012*',P.POAmountActual,Null)) AS SpentFY12, Sum(IIf(P.BHSRequisitionNum Like '2012*',ClosedAmount,Null)) AS ClosedFY12
FROM Vendors AS V LEFT JOIN PurchaseOrders AS P ON V.VendorName = P.VendorName
GROUP BY V.VendorName as CurrentVend) on Vend.VendorName= CurrentVend.VendorName as FirstJoin]
 
LEFT JOIN
 
(SELECT V.VendorName, Sum(IIf(P.BHSRequisitionNum Not Like '2012*',P.POAmountActual,Null)) AS Spent_previous, Sum(IIf(P.BHsRequisitionNum Not Like '2012*',P.ClosedAmount,Null)) AS Closed_previous
FROM Vendors AS V LEFT JOIN PurchaseOrders AS P ON V.VendorName=P.VendorName
GROUP BY V.VendorName as PrevVend)
 
on FirstJoin.VendorName= PrevVend.VendorName
Group by Vendors.VendorName, Vendors.Current_FY_Budget;

 

[Big Pat]

Hi,

I'm far from being an expert at this stuff, but I can see square brackets in your FROM statement. As far as I know square brackets are used to define fields which have a space [in the name] and not as part of the SQL syntax. Try replacing them with round barckets and see if you get the same problem.

Alternatively, have you tried the query design grid to build the query? I always find the visual method a more intuitive way to get correct joins.

 

[Margarita]

Hi Big Pat, thanks for your reply. I realized that the square brackets were a problem, but replacing them with regular brackets didn't help either. I abandoned ship on doing all of this in one query and decided to add the FY12 and Previous select statements as sub-reports in the main report which displays just the fields from the main Vendors table. The two subreports display the SumSpent and SumClosed fields and I will sum them on the report and subtract from the Current_FY_Budget field in the main report.

Still, if anyone could correct just where I was going wrong with the original query syntax (other than parentheses), I would really appreciate it. I could use the advice for future reference.

Thank you!

 

[Guus2005]

What i would do is split up the problem into pieces.
Create a subquery and save it. Create another subquery and again save it.
In the main query use the tables and the subqueries as you normally would.

Each subquery can be debugged separately. it narrows down the problem.

doing it the way you do, you must know what you are doing!

I believe that the alias of the first subquery is wrong.

(SELECT V.VendorName, Sum(IIf(P.BHSRequisitionNum Like '2012*',P.POAmountActual,Null)) AS SpentFY12, Sum(IIf(P.BHSRequisitionNum Like '2012*',ClosedAmount,Null)) AS ClosedFY12
FROM Vendors AS V LEFT JOIN PurchaseOrders AS P ON V.VendorName = P.VendorName
GROUP BY V.VendorName [COLOR="Red"]as CurrentVend)[/COLOR]

the alias is not part of the query. it should be outside the parenthesis.

Check the rest also.

HTH

 

[Margarita]

Thank you for giving this a shot. Unfortunately, this way is still giving me an error... And thanks for the advice on saving the subqueries separately- I always try to do that, but somehow end up into trouble with syntax when I try to put it together in the main query...

 

[Guus2005]

Is your problem solved?
Do you still get the error?
If so what does the error say?
Did you use saved subqueries?
Can you post a sample database?

 

[Margarita]

Well, my problem is more or less solved via the method I mentioned two posts ago: the ultimate goal of this query was a vendor profile report, so I am simply using the two subqueries as subreports and summing the fields on the main report. However, I left the post open to see if anyone could suggest a way to fix the sql syntax, so I can see my sql error for future reference.

I don't see a way to mark the thread as solved...