Crosstab Query Report Problem Build

[kcdoell]

Hello Everyone:

I have a table called “tblStaticAllFo recast”, below are the fields I am currently focusing on within the table:

LOB [this is my product list that has 22 products; text]
GWP [Gross Written Premium; numeric]
FWeek [Drop down list of numbers 1-5; numeric]

Any given LOB can have a GWP and FWEEK assigned to it. For example:

LOB---------GWP----------FWeek
Trains------1,506---------------1
Trains------1,888---------------2
Trains------2,056---------------1
Cars--------1,577---------------1
Cars--------2,999---------------1
Cars--------1,999---------------2
Cars--------3,999---------------1
etc...

What I am trying to do is create a report that shows all LOB’s (Products) GWP (Amount) for that give FWeek (Week).

I thought the best way of doing this was to write a crosstab query using Access’s query builder, but of course I never did this before. Below is what I currently got to work: “SQL design view”:

TRANSFORM Nz(Sum([tblStaticAllForecast].[GWP]),"0") AS GWP
SELECT tblStaticAllForecast.LOB
FROM tblStaticAllForecast
GROUP BY tblStaticAllForecast.LOB
PIVOT tblStaticAllForecast.FWeek;
 

My problem is that it does not show all LOBs (Products), in this case it only showed 17 out of the 22 and it also does not show all of the FWeek (Week), in this case it only showed 3 out of the 5. I am using the “Nz” to propagate zeros for null values for GWP but I don’t know how to have, a column or a row heading to propagate if there is no data on them yet.

Any ideas would be great.

Thanks,

Keith.

[kcdoell]

Hello again:

I was doing some research and found out that the "In clause" assures that you always get the same consistent number of columns even if no data is present. I expanded off of my existing SQL and came up with the following:

TRANSFORM Nz(Sum(tblStaticAllForecast.GWP),"0") AS GWP
SELECT tblStaticAllForecast.LOB
FROM tblStaticAllForecast
GROUP BY tblStaticAllForecast.LOB
PIVOT "Week" & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");

The result is that all my Week columns are present but for some reason all the values are zero. The selections in my “FWeek” field are (1,2,3,4,& 5) so I am thinking that that is where I am going wrong. Since I am new with this, does anybody know where I am going wrong in the code? Can I apply this same logic to the row heading as well??

Any ideas would be great,

Thanks,

Keith.

[kcdoell]

The Logic for the column was:

PIVOT "Week" & Format([FWeek]) In ("Week1","Week2","Week3","Week4","Week5");

Still working on the row headings showing......, any ideas please let me know.

Thanks,

Keith.

[kcdoell]

Hello I am going to open another thread and close this one to better explain my problem.....

Keith.