Fairly Complex Transform Pivot Query

[kwilliam10]

Hi all,

This might be a bit too much...but I'll give it a try with you guys. I have an excel spreadsheet, where I have 3 different tabs (each with a query) which I want to join into one Transform-Pivot result. The reason I have the Transform-Pivot...is because I am aging Accounts Receivable..and using the Transform-Pivot to reflect the Aged Balances, by project. The other queries are pulling in additional information for each project.

The result would be similar to this:

Aged AR Query.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
7	WBS1	Currency	ClientName	Entity	Center	Sector	ClientType	FederalInd	MultAmt	RevType	Retainer	Balance	Current	31-60	61-90	91-120
8	00-02070.28	USD	NewYorkState	00	02	30	S	N	-65000	PROFIT		112,315.00
9	00-03077.13	USD	JohnsHopkinsUniversity	00	01	30	P	N	0	B		17,193.31		17,193.31
10	00-04062.33	USD	MPO	00	22	30	G	Y	0	COMPLETE		99,124.75
11	00-04062.36	USD	MarylandProcurementOffice	00	22	30	S	Y	0	COMPLETE		35,134.79
12	00-04062.46	USD	MarylandProcurementOffice	00	02	30	S	N	0	B		600.00		600.00
13	00-04062.51	USD	MarylandProcurementOffice	00	02	30	S	N	0	PCT_EAC		16,717.29		16,717.29
14	00-04068.02	USD	AlGhurairCentreLLC	00	21	50	I	N	0	PCT_EAC		140,988.80		8,000.03	71,190.21	9,098.56
15	00-04068.03	USD	AlGhurairCentreLLC	00	21	50	I	N	0	LS_UNDER		10,000.00
TotalDetail

Here is my code for the Transform-Pivot query:

Transform sum(qryARBals.Balance)
SELECT qryARBals.Entity, qryARBals.WBS1, qryARBals.BillingCurrencyCode, qryProjInfo.ClientName, qryProjInfo.ClientType, qryProjInfo.FederalInd, qryProjInfo.MultAmt, qryProjInfo.RevType, qryRetainer.Retainer, sum(qryARBals.Balance) as [Balance], qryProjInfo.Center, qryProjInfo.Sector
FROM (`V:\Finance\AR\2009\Aged AR Query`.qryARBals qryARBals inner join
`V:\Finance\AR\2009\Aged AR Query`.qryProjInfo qryProjInfo on qryARBals.WBS1 = qryProjInfo.WBS1)
LEFT OUTER JOIN `V:\Finance\AR\2009\Aged AR Query`.qryRetainer qryRetainer ON qryARBals.WBS1 = qryRetainer.WBS1
Group by qryARBals.Entity, qryARBals.WBS1, qryARBals.BillingCurrencyCode, qryProjInfo.ClientName, qryProjInfo.ClientType, qryProjInfo.FederalInd, qryProjInfo.MultAmt, qryProjInfo.RevType, qryRetainer.Retainer, qryProjInfo.Center, qryProjInfo.Sector
Pivot qryARBals.Age



My dilema is that right now the code (above) is based on a one-to-one relationship between qryARBals and qryProjInfo. I really need to change this to be a Left Outer Join of qryARBals to qry ProjInfo. This is because the last query I am joining to the Transform-Pivot (qryRetainer) has activity for projects that are not on qryARBals query. The qryProjInfo query includes ALL projects. I need to change this up so that the qryProjInfo is the left most data ...and two Outer Joins for the other queries. HOWEVER...this would mean that on the Transform-Pivot...I would have some nulls. I believe that NZ() does not work in excel sql (at least I was not able to get it to work.) Anyone have any ideas how I can get the two lines of this code to work? Even if I could get the nz() to work...I would also have to do some sort of case statement for the "Pivot qryARBals.Age" line...because there would be no age for that data either.

Hope there is someone that can help me.

Thanks,

Keith W.

 

[kwilliam10]

Anyone have an alternative to NZ()??

Thanks,

Keith W.