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

[kcdoell]

I have been trying for the last several days to create a query that will give me all of the values I need to create a report.

Background:

The report is different than anything I have done but I am hoping that for someone out there has. Visually it looks like the following:

Product Name__Week 1__Week 2__Week 3__Week 4__Week 5__BudgetGWP__P riorGWP
Product 1________45____ __56_______0___ ____0_______12_ ______300______ ___250__
Product 2________60____ __66_______0___ ____0_______11_ ______250______ ___225__
Product 3________45____ __56_______0___ ____0_______12_ ______300______ ___225__
Product 4________0_____ __0________0___ ____0_______0__ ______50_______ ___40___
Product 5________45____ __56_______0___ ____0_______12_ ______412______ ___335__
Product 6________0_____ __0________0___ ____0_______0__ ______41_______ ___32___
Etc……

All of the values are NOT coming from the same table:

The values for column headings Weeks 1-5 are coming from one table called “tblStaticAllFo recast”. This table includes my Product Name field and Weeks 1 -5 is a field called “FWeek” in which the user can select a drop down list of numbers 1-5.

The values for column heading BudgetGWP are coming from one table called “tblbudget”. This table includes my Product Name field.

The values for column heading PriorGWP are coming from one table called “tblPrior”. This table includes my Product Name field.

All of the tables have a filed called “GWP” of which the values in this report are based upon. As you can see on the report I displayed I have scenarios where there are products of which nothing was sold across the weeks or in certain weeks only.

I need to display all products of which in my case there are 22.
I also need to display a column heading called Week 1, 2, 3, ,, when I only have a field called FWeek.

Current State:

Like I said I have been struggling with this type of report. I have been going down the crosstab query road to build a part of this report. I figured out how to use the “In Clause” to create the column headings I needed (Week 1 , 2, 3, ,,), populate zeros where no values were found (Using “Nz” in my sum expression of GWP) but how to get ALL products to list has eluded me. Below is what I have so far on the crosstab query:

[code=vb]
TRANSFORM Nz(Sum(tblStati cAllForecast.GW P),"0") AS GWP
SELECT tblStaticAllFor ecast.LOB
FROM tblStaticAllFor ecast
WHERE (((tblStaticAll Forecast.Divisi onIDFK)=[forms].[Rpt_Summary].[cboDivision]) AND ((tblStaticAllF orecast.YearID) =[forms].[Rpt_Summary].[cboYear]) AND ((tblStaticAllF orecast.MonthID )=[forms].[Rpt_Summary].[cboMonth]) AND ((Val([Binding_Percent age]))>="75"))
GROUP BY tblStaticAllFor ecast.LOB
ORDER BY tblStaticAllFor ecast.LOB
PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
[/code]
On top of that, to tie in the BudgetGWP & PriorGWP values that are located on different tables has left me in a desperate state….. I have also read that in a crosstab query, I can specify only one value field and one column heading field.

Can anyone help me on this. I have run out of ideas due to my lack of experience. How would you go about trying to get the dataset in the format needed to create the report.

I hope someone is out there to shed some light on the subject.

Thanks,

Keith.

[FishVal]

Hi, Keith.

As soon as all three datasets contain unique values of [ProductName] you may just join them altogether.

Regards,
Fish

[kcdoell]

Hi, Keith.

As soon as all three datasets contain unique values of [ProductName] you may just join them altogether.

Regards,
Fish

Thanks Fish:

In my ignorance, what is meant by unique values of [ProductName]? Would I still go down the Crosstab query road??

Keith.

[FishVal]

Thanks Fish:

In my ignorance, what is meant by unique values of [ProductName]? Would I still go down the Crosstab query road??

Keith.

Well. Crosstab query is just ok. I guess [LOB] field contains what is called "Product Name" in layout sample you've provided. Am I right? Does it match [ProductName] field in the rest two tables?
If so, and if each of the rest two tables contain unique values in [ProductName] field , then you may just join them with the crosstab query (I guess via outer join).

Regards,
Fish

[kcdoell]

Well. Crosstab query is just ok. I guess [LOB] field contains what is called "Product Name" in layout sample you've provided. Am I right? Does it match [ProductName] field in the rest two tables?
If so, and if each of the rest two tables contain unique values in [ProductName] field , then you may just join them with the crosstab query (I guess via outer join).

Regards,
Fish

Sorry about that confusion, I cleaned up my use of [LOB] or [ProductName] in different tables. All fields on the different tables are now called [ProductName]. It does match on field but they are not the primary key. I actually have [ProductID] on my tblProduct as the primary key. I tried to create a one to many relationship between my "tblProduct " and “tblStaticAllFo recast” on [ProductName] but it would not let me?? In any case I did not think that was too important.

So I thought I would create a new query, join the tables and then base a new crosstab query on that.

So I added my table called “tblProduct” and then joined it with “tblStaticAllFo recast” on [ProductName].

In that new query I pulled in:

[ProductName] = tblproducts
[GWP] = tblStaticAllFor ecas
[FWeek] = tblStaticAllFor ecas

On the following join properties selections this is what happen:

Join Option 1: Only include rows where the joined fields from both tables are equal.

-Query runs and comes up null.

Join Option 2: Include ALL records from “tblStaticAllFo recast” and only those records from “tblProduct” where the joined fields are equal.

- Query runs, No products are listed (they are blank) but I have values in my [GWP] & [FWeek]

Join Option 3: Include ALL records from “tblProduct” and only those records from “tblStaticAllFo recast” where the joined fields are equal.

- Query runs, All products are listed (All 22, with same amount of records) but I have blank values in my [GWP] & [FWeek] (they are blank)

I am thinking that I would need to select Option 3 but that produces no values. I saw this happening before but don’t understand it fully. Should have I not received values?

Thanks for all your help,

Keith

[FishVal]

Are you sure the field you join tables on is the right one?
What you are getting is a result of no match by chosen field between the tables.

[kcdoell]

Fish:

Thanks for getting back to me. I stumbled across, something along the lines that you are talking about (Type Mismatch). I am going to correct it first and then get back to you.

Thanks again,

Keith.

[kcdoell]

Hello Fish:

I have been working on this still… I created a new query,"QryAllPr oducts" and then built a cross tab off of that one. I managed to get all the products populated with the following code and linking the tables beforehand.

[code=sql]
TRANSFORM Nz(Sum(tblStati cAllForecast.GW P),"0") AS GWP
SELECT QryAllProducts. ProductID, QryAllProducts. ProductName
FROM QryAllProducts
GROUP BY QryAllProducts. ProductID, QryAllProducts. ProductName
PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
[/code]

I get All 22 Products!

But when I enter the binding percentage where expression:

[code=sql]
TRANSFORM Nz(Sum(tblStati cAllForecast.GW P),"0") AS GWP
SELECT QryAllProducts. ProductID, QryAllProducts. ProductName
FROM QryAllProducts
WHERE (((Val([Binding_Percent age]))>="75"))
GROUP BY QryAllProducts. ProductID, QryAllProducts. ProductName
PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
[/code]

I only get 12 Products??

Any ideas??

Keith.

[kcdoell]

Fish:

In fact I put some other criteria in other fields of my query:
[code=sql]
'Division criteria:
[forms].[Rpt_Sum_Product].[cbodivision]

'Year criteria:
[forms].[Rpt_Sum_Product].[cboyear]

'Month Criteria:
[forms].[Rpt_Sum_Product].[cbomonth]
[/code]

and it also narrows down the list as well. Should that have happened?? I don't want it to. I want it to populate zeros for those products that had no values.

Keith.

[FishVal]

Fish:

In fact I put some other criteria in other fields of my query:
[code=sql]
'Division criteria:
[forms].[Rpt_Sum_Product].[cbodivision]

'Year criteria:
[forms].[Rpt_Sum_Product].[cboyear]

'Month Criteria:
[forms].[Rpt_Sum_Product].[cbomonth]
[/code]

and it also narrows down the list as well. Should that have happened?? I don't want it to. I want it to populate zeros for those products that had no values.

Keith.

Hi, Keith.

You may use outer join to enforce the query to return records for all products.
Example
tblProducts - table containing list of products
qryQuery - your crosstab query with all necessary criteria
[code=sql]
SELECT tblProducts.Pro ductID, tblProducts.Pro ductName, ctqQuery.<the rest fields> FROM tblProducts LEFT JOIN ctqQuery ON tblProducts.Pro ductID=ctqQuery .ProductID;
[/code]

Regards,
Fish

[kcdoell]

Hi Fish:

Is that a new query or do I place that in the SQL of my crosstab query?

Thanks,

keith.

[kcdoell]

Hi Fish:

Okay I created a crosstab query with all the criteria. Of course I did not get all 22 products when I ran it. So I added the tblProducts table to my crosstab query and did a left join to the table . Below is the Sql:

[code=sql]TRANSFORM Nz(Sum(tblStati cAllForecast.GW P),"0") AS GWP_Total
SELECT tblStaticAllFor ecast.ProductID FK
FROM tblProduct LEFT JOIN tblStaticAllFor ecast ON tblProduct.Prod uctID = tblStaticAllFor ecast.ProductID FK
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]

It looks like I did this correctly as you indicated but I still get only 10 products after I joined it. When I did add the tblProduct table, immediately i saw the one to many relationship. All I did was simply change the join properties of that relationship to the left.

Any ideas?


Thanks for all your help.

Keith

[FishVal]

Hi Fish:

Is that a new query or do I place that in the SQL of my crosstab query?

Thanks,

keith.

Hello, Keith.

I guess [tblBudget] and [tblPrior] may have records with [ProductID] not matching any record in the crossrab query but expected to appear in the final query.
If so, then the reliable query sequence will be the following:

• [ctqQuery]: your glorious crosstab query returning records with no duplicated ProductIDs but only those matching query criteria
• [qryAllProductsC tq]: [tblProducts] LEFT JOIN [crosstab query] to ensure all ProductIDs are present in resulting recordset
• [qryFinal]: ([qryAllProductsC tq] INNER JOIN [tblBudget]) INNER JOIN [tblPrior], [tblBudget] and [tblPrior] are expected to contain unique [ProductID] values satisfying join criteria (if not, then their records should be appropriately grouped by [ProductID]). INNER JOIN may be replaced with LEFT JOIN to get all products in the final output

Regards,
Fish

[kcdoell]

Hello, Keith.

I guess [tblBudget] and [tblPrior] may have records with [ProductID] not matching any record in the crossrab query but expected to appear in the final query.......... ..............[/list]

Regards,
Fish

Fish:

I was digging deeper before your response and noticed that once I put the criteria in, the reason the product list reduced from 22 to 17 was because given the parameters certain products were not present on the table (not my tblProduct but tbltblStaticAll Forecast).

Will Null results impact the logic??

Keith.

[kcdoell]

Hello Fish:

I am getting very close. I got step two down. Here is what "qryAllProducts Ctq" is looking like:

[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]

Now I have all 22 products for my 5 weeks!! Wow, I am so close I could taste it, I think?? Anyway, I went to create step 3, opened up a new query, imported my "qryAllProducts Ctq" and for now just the tblbudget table (without any criteria). For some reason I needed to create a Right join to get all 22 products to populate:

[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(Sum(tblbudge t.GWP),"0") AS GWP_Bud
FROM tblbudget RIGHT JOIN qryAllProductsC tq ON tblbudget.Produ ctIDFK = qryAllProductsC tq.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;[/code]

I was okay with that but I noticed that the criteria for my "ctqQuery" did not carry over for my Sum calculation I have above "Nz(Sum(tblbudg et.GWP...." Does that mean that I need to create a crosstab query for tblbudget much like the one I created for "ctqQuery" and then import that one in for my final query instead on the raw table (tblBudget)??

Thanks for all your help....

Keith. :-)