Creating a pivot table looking report……..please turn on the lights....

[kcdoell]

Fish:

It took me six queries to get my final one. My next step was to create the report. So I started to use the Report Wizard, choose the crosstab query as the record source, and then when I moved to the next step in the process, I had no fields to choose for the report?? I noticed that if I choose any of the crosstab queries, the same thing happens??? Have you ever seen that?

Keith.

[kcdoell]

Fish:

For some reason the report wizard does not show the fields. My solution was not to use the wizard. I created the report from scratch, and then used the field list to drop in the fields......... ....

Does it seem right to you that I would need to create 6 queries to make my ultimate one given what I was trying to do? This particular report shows just the values of my [GWP]. I have to also do the same thing for my value [NWP]. That would mean an additional 7 queries making a total of 14 to make one report! It seems silly in a way......

Any ideas on the most efficient approach or is this some limitation on Access's part?

Thanks for all your help!!

Keith

[FishVal]

Hi, Keith.

Post, please the queries as they look now. Also post the tables metadata.
Here is an example of how to post table MetaData :
Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

I will look what may be done to optimize them.

Regards,
Fish

[kcdoell]

Hi, Keith.

Post, please the queries as the............ .to optimize them.

Regards,
Fish

Okay: Let me set it up and get back to you.

Thanks,

Keith.

[kcdoell]

Fish:

The Tables:

[code=text]

Table Name = tblStaticAllFor ecast
Field Type IndexInfo
DivisionIDFK; Number; Foreign Key for the Division ID
YearID; Number; Year
MonthID; Number; Foreign Key for the Month ID
ProductIDFK; Number; Foreign Key for the Product ID
GWP; Number; Gross Premium
NWP; Number; Gross Premium
Fweek; Number; Week number of a month (1-5)
Binding_Percent age; Number; Binding Percentage

Table Name = tblPrior
Field Type IndexInfo
DivisionIDFK; Number; Foreign Key for the Division ID
YearID; Number; Year
MonthID; Number; Foreign Key for the Month ID
GWP; Number; Gross Premium
NWP; Number; Gross Premium
ProductIDFK; Number; Foreign Key for the Product ID
ProductName; Text; Product Name


Table Name = tblbudget
Field Type IndexInfo
DivisionIDFK; Number; Foreign Key for the Division ID
YearID; Number; Year
MonthID; Number; Foreign Key for the Month ID
GWP; Number; Gross Premium
NWP; Number; Gross Premium
ProductIDFK; Number; Foreign Key for the Product ID
ProductName; Text; Product Name


Table Name = tblproduct
Field Type IndexInfo
ProductID; AutoNumber; The Product ID
ProductName; Text; Product Name
[/code]

The Queries:

ctqQueryP:
[code=sql] TRANSFORM Nz(Sum(tblStati cAllForecast.GW P),"0") AS GWP_Total
SELECT tblStaticAllFor ecast.ProductID FK
FROM tblStaticAllFor ecast
WHERE (((tblStaticAll Forecast.Divisi onIDFK)=[forms].[Rpt_Sum_Product].[cbodivision]) AND ((tblStaticAllF orecast.YearID) =[forms].[Rpt_Sum_Product].[cboyear]) AND ((tblStaticAllF orecast.MonthID )=[forms].[Rpt_Sum_Product].[cbomonth]) AND ((tblStaticAllF orecast.Binding _Percentage)>=7 5))
GROUP BY tblStaticAllFor ecast.ProductID FK
PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");[/code]

ctqQueryP_B:
[code=sql] PARAMETERS [forms].[Rpt_Sum_Product].[cboyear] Short, [forms].[Rpt_Sum_Product].[cbodivision] Short, [forms].[Rpt_Sum_Product].[cbomonth] Short;
TRANSFORM Nz(Sum(tblbudge t.GWP),"0") AS GWP_BUD
SELECT tblbudget.Produ ctIDFK, tblbudget.Produ ctName
FROM tblbudget
WHERE (((tblbudget.Ye arID)=[forms].[Rpt_Sum_Product].[cboyear]) AND ((tblbudget.Mon thID)=[forms].[Rpt_Sum_Product].[cbomonth]) AND ((tblbudget.Div isionIDFK)=[forms].[Rpt_Sum_Product].[cbodivision]))
GROUP BY tblbudget.Produ ctIDFK, tblbudget.Produ ctName
PIVOT tblbudget.YearI D;[/code]

ctqQueryP_P:
[code=sql] PARAMETERS [forms].[Rpt_Sum_Product].[cboyear] Short, [forms].[Rpt_Sum_Product].[cbodivision] Short, [forms].[Rpt_Sum_Product].[cbomonth] Short;
TRANSFORM Nz(Sum(tblPrior .GWP),"0") AS GWP_PRI
SELECT tblPrior.Produc tIDFK, tblPrior.Produc tName
FROM tblPrior
WHERE (((tblPrior.Yea rID)=[forms].[Rpt_Sum_Product].[cboyear]-1) AND ((tblPrior.Mont hID)=[forms].[Rpt_Sum_Product].[cbomonth]) AND ((tblPrior.Divi sionIDFK)=[forms].[Rpt_Sum_Product].[cbodivision]))
GROUP BY tblPrior.Produc tIDFK, tblPrior.Produc tName
PIVOT tblPrior.YearID ;[/code]

qryAllProductsC tq:
[code=sql] SELECT tblProduct.Prod uctID, tblProduct.Prod uctName, Nz([Week 1],0) AS Week_1, Nz([Week 2],0) AS Week_2, Nz([Week 3],0) AS Week_3, Nz([Week 4],0) AS Week_4, Nz([Week 5],0) AS Week_5
FROM ctqQueryP RIGHT JOIN tblProduct ON ctqQueryP.Produ ctIDFK = tblProduct.Prod uctID;[/code]

qryAllProductsC tq_B:
[code=sql] SELECT tblProduct.Prod uctID, tblProduct.Prod uctName, Nz([2008],0) AS GWP_BUD
FROM tblProduct LEFT JOIN ctqQueryP_B ON tblProduct.Prod uctID = ctqQueryP_B.Pro ductIDFK
ORDER BY tblProduct.Prod uctID;[/code]

qryAllProductsC tq_P:
[code=sql]SELECT tblProduct.Prod uctID, tblProduct.Prod uctName, Nz([2007],0) AS GWP_PRI
FROM tblProduct LEFT JOIN ctqQueryP_P ON tblProduct.Prod uctID = ctqQueryP_P.Pro ductIDFK
ORDER BY tblProduct.Prod uctID;[/code]

qryF_byProd_PB (FINAL Query that drives my report):
[code=sql]SELECT qryAllProductsC tq.ProductID, qryAllProductsC tq.ProductName, qryAllProductsC tq.Week_1, qryAllProductsC tq.Week_2, qryAllProductsC tq.Week_3, qryAllProductsC tq.Week_4, qryAllProductsC tq.Week_5, Nz([GWP_BUD],0) AS GWPBUD, Nz([GWP_PRI],0) AS GWPPRI
FROM (qryAllProducts Ctq LEFT JOIN qryAllProductsC tq_B ON qryAllProductsC tq.ProductID = qryAllProductsC tq_B.ProductID) INNER JOIN qryAllProductsC tq_P ON qryAllProductsC tq.ProductID = qryAllProductsC tq_P.ProductID
GROUP BY qryAllProductsC tq.ProductID, qryAllProductsC tq.ProductName, qryAllProductsC tq.Week_1, qryAllProductsC tq.Week_2, qryAllProductsC tq.Week_3, qryAllProductsC tq.Week_4, qryAllProductsC tq.Week_5, Nz([GWP_BUD],0), Nz([GWP_PRI],0);
[/code]

That is it... Like I said the main focus so far has been the value of [GWP] but I also need the value of [NWP]. It would visually look like one identical table below the other on the report which I thought I could achieve by placing two subforms on the report each pointing to there respective queries. Worst case scenario, they would be two separate reports.

Thanks for your help, in the meantime I am experimenting on my side as well


Keith.

[FishVal]

Ok, Keith.

Here is what I've noticed having taken a quick look.

• ctqQueryP:
  looks ok
• ctqQueryP_B and ctqQueryP_P:
  I see no reason why it has to be a crosstab query pivoted by [YearID] as soon as query criteria allows records with a single value of [YearID]. I'm sure a simple GroupBy query will do the job for less money.
• qryAllProductsC tq and qryAllProductsC tq_P and qryAllProductsC tq_B:
  There is no reason to LeftJoin all them with [tblProduct] before LeftJoining them altogether. Actually, I would suggest you to LeftJoin [tblProduct], [ctqQueryP], [ctqQueryP_B] and [ctqQueryP_P] in one query which will be the final one.

Regards,
Fish

[kcdoell]

Ok, Keith.

Here is what I've noticed having taken a quick look........... ..
.............. I would suggest you to LeftJoin [tblProduct], [ctqQueryP], [ctqQueryP_B] and [ctqQueryP_P] in one query which will be the final one.[/list]

Regards,
Fish

Fish:

Double thanks for your insight. I changed the two ctq queries to simple queries as you suggested along with your left join idea. I went from needing 7 queries to 4 and to include the other value [NWP] will only require an additional 2! Bottom line was that instead of needing 14 to do the whole thing (NWP & GWP Values] I now need only 6!!

This sure was a long learning process. Thank you very much for sticking around until I solved it.

Much appreciated :-).....

Keith.

P.S.:

Below was my final join:
[Code=sql] SELECT tblProduct.Prod uctName, QryProd_Bud.GWP _BUD, Nz([Week 1],0) AS Week_1, Nz([Week 2],0) AS Week_2, Nz([Week 3],0) AS Week_3, Nz([Week 4],0) AS Week_4, Nz([Week 5],0) AS Week_5, QryProd_Prior.G WP_PRI
FROM ((tblProduct LEFT JOIN ctqQueryP ON tblProduct.Prod uctID = ctqQueryP.Produ ctIDFK) LEFT JOIN QryProd_Bud ON tblProduct.Prod uctID = QryProd_Bud.Pro ductIDFK) LEFT JOIN QryProd_Prior ON tblProduct.Prod uctID = QryProd_Prior.P roductIDFK;[/code]

[FishVal]

Congratulations , Keith. :)
You've done a great job and I guess you've had a great IT fun ;).
Good luck and happy coding.

Best regards,
Fish