By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,337 Members | 2,084 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,337 IT Pros & Developers. It's quick & easy.

Crosstab Query Report Problem Build

kcdoell
100+
P: 230
Hello Everyone:

I have a table called “tblStaticAllForecast”, 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”:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Nz(Sum([tblStaticAllForecast].[GWP]),"0") AS GWP
  2. SELECT tblStaticAllForecast.LOB
  3. FROM tblStaticAllForecast
  4. GROUP BY tblStaticAllForecast.LOB
  5. PIVOT tblStaticAllForecast.FWeek;
  6.  
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.
Apr 28 '08 #1
Share this Question
Share on Google+
3 Replies


kcdoell
100+
P: 230
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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Nz(Sum(tblStaticAllForecast.GWP),"0") AS GWP
  2. SELECT tblStaticAllForecast.LOB
  3. FROM tblStaticAllForecast
  4. GROUP BY tblStaticAllForecast.LOB
  5. 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.
Apr 29 '08 #2

kcdoell
100+
P: 230
The Logic for the column was:

Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 29 '08 #3

kcdoell
100+
P: 230
Hello I am going to open another thread and close this one to better explain my problem.....

Keith.
May 1 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.