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

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

kcdoell
100+
P: 230
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__PriorGWP
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 “tblStaticAllForecast”. 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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Nz(Sum(tblStaticAllForecast.GWP),"0") AS GWP
  2. SELECT tblStaticAllForecast.LOB
  3. FROM tblStaticAllForecast
  4. WHERE (((tblStaticAllForecast.DivisionIDFK)=[forms].[Rpt_Summary].[cboDivision]) AND ((tblStaticAllForecast.YearID)=[forms].[Rpt_Summary].[cboYear]) AND ((tblStaticAllForecast.MonthID)=[forms].[Rpt_Summary].[cboMonth]) AND ((Val([Binding_Percentage]))>="75"))
  5. GROUP BY tblStaticAllForecast.LOB
  6. ORDER BY tblStaticAllForecast.LOB
  7. PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
  8.  
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.
May 1 '08 #1
Share this Question
Share on Google+
22 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, Keith.

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

Regards,
Fish
May 1 '08 #2

kcdoell
100+
P: 230
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.
May 1 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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
May 1 '08 #4

kcdoell
100+
P: 230
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 “tblStaticAllForecast” 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 “tblStaticAllForecast” on [ProductName].

In that new query I pulled in:

[ProductName] = tblproducts
[GWP] = tblStaticAllForecas
[FWeek] = tblStaticAllForecas

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 “tblStaticAllForecast” 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 “tblStaticAllForecast” 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
May 1 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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.
May 2 '08 #6

kcdoell
100+
P: 230
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.
May 2 '08 #7

kcdoell
100+
P: 230
Hello Fish:

I have been working on this still… I created a new query,"QryAllProducts" 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.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Nz(Sum(tblStaticAllForecast.GWP),"0") AS GWP
  2. SELECT QryAllProducts.ProductID, QryAllProducts.ProductName
  3. FROM QryAllProducts
  4. GROUP BY QryAllProducts.ProductID, QryAllProducts.ProductName
  5. PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
  6.  
I get All 22 Products!

But when I enter the binding percentage where expression:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Nz(Sum(tblStaticAllForecast.GWP),"0") AS GWP
  2. SELECT QryAllProducts.ProductID, QryAllProducts.ProductName
  3. FROM QryAllProducts
  4. WHERE (((Val([Binding_Percentage]))>="75"))
  5. GROUP BY QryAllProducts.ProductID, QryAllProducts.ProductName
  6. PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
  7.  
I only get 12 Products??

Any ideas??

Keith.
May 2 '08 #8

kcdoell
100+
P: 230
Fish:

In fact I put some other criteria in other fields of my query:
Expand|Select|Wrap|Line Numbers
  1. 'Division criteria:
  2. [forms].[Rpt_Sum_Product].[cbodivision]
  3.  
  4. 'Year criteria:
  5. [forms].[Rpt_Sum_Product].[cboyear]
  6.  
  7. 'Month Criteria:
  8. [forms].[Rpt_Sum_Product].[cbomonth]
  9.  
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.
May 2 '08 #9

FishVal
Expert 2.5K+
P: 2,653
Fish:

In fact I put some other criteria in other fields of my query:
Expand|Select|Wrap|Line Numbers
  1. 'Division criteria:
  2. [forms].[Rpt_Sum_Product].[cbodivision]
  3.  
  4. 'Year criteria:
  5. [forms].[Rpt_Sum_Product].[cboyear]
  6.  
  7. 'Month Criteria:
  8. [forms].[Rpt_Sum_Product].[cbomonth]
  9.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProducts.ProductID, tblProducts.ProductName, ctqQuery.<the rest fields> FROM tblProducts LEFT JOIN ctqQuery ON tblProducts.ProductID=ctqQuery.ProductID;
  2.  
Regards,
Fish
May 3 '08 #10

kcdoell
100+
P: 230
Hi Fish:

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

Thanks,

keith.
May 5 '08 #11

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

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Nz(Sum(tblStaticAllForecast.GWP),"0") AS GWP_Total
  2. SELECT tblStaticAllForecast.ProductIDFK
  3. FROM tblProduct LEFT JOIN tblStaticAllForecast ON tblProduct.ProductID = tblStaticAllForecast.ProductIDFK
  4. WHERE (((tblStaticAllForecast.DivisionIDFK)=[forms].[Rpt_Sum_Product].[cbodivision]) AND ((tblStaticAllForecast.YearID)=[forms].[Rpt_Sum_Product].[cboyear]) AND ((tblStaticAllForecast.MonthID)=[forms].[Rpt_Sum_Product].[cbomonth]) AND ((tblStaticAllForecast.Binding_Percentage)>=75))
  5. GROUP BY tblStaticAllForecast.ProductIDFK
  6. PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
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
May 5 '08 #12

FishVal
Expert 2.5K+
P: 2,653
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
  • [qryAllProductsCtq]: [tblProducts] LEFT JOIN [crosstab query] to ensure all ProductIDs are present in resulting recordset
  • [qryFinal]: ([qryAllProductsCtq] 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
May 5 '08 #13

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

Will Null results impact the logic??

Keith.
May 5 '08 #14

kcdoell
100+
P: 230
Hello Fish:

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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProduct.ProductID, tblProduct.ProductName, 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
  2. FROM ctqQueryP RIGHT JOIN tblProduct ON ctqQueryP.ProductIDFK = tblProduct.ProductID;
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 "qryAllProductsCtq" 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT qryAllProductsCtq.ProductID, qryAllProductsCtq.ProductName, qryAllProductsCtq.Week_1, qryAllProductsCtq.Week_2, qryAllProductsCtq.Week_3, qryAllProductsCtq.Week_4, qryAllProductsCtq.Week_5, Nz(Sum(tblbudget.GWP),"0") AS GWP_Bud
  2. FROM tblbudget RIGHT JOIN qryAllProductsCtq ON tblbudget.ProductIDFK = qryAllProductsCtq.ProductID
  3. GROUP BY qryAllProductsCtq.ProductID, qryAllProductsCtq.ProductName, qryAllProductsCtq.Week_1, qryAllProductsCtq.Week_2, qryAllProductsCtq.Week_3, qryAllProductsCtq.Week_4, qryAllProductsCtq.Week_5;
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(tblbudget.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. :-)
May 5 '08 #15

kcdoell
100+
P: 230
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.
May 6 '08 #16

kcdoell
100+
P: 230
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
May 6 '08 #17

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
I will look what may be done to optimize them.

Regards,
Fish
May 6 '08 #18

kcdoell
100+
P: 230
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.
May 6 '08 #19

kcdoell
100+
P: 230
Fish:

The Tables:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Table Name =  tblStaticAllForecast        
  3. Field    Type    IndexInfo
  4. DivisionIDFK;    Number;    Foreign Key for the Division ID
  5. YearID;    Number;    Year
  6. MonthID;    Number;    Foreign Key for the Month ID
  7. ProductIDFK;    Number;    Foreign Key for the Product ID
  8. GWP;    Number;    Gross Premium
  9. NWP;    Number;    Gross Premium
  10. Fweek;    Number;    Week number of a month (1-5)
  11. Binding_Percentage;    Number;    Binding Percentage
  12.  
  13. Table Name =  tblPrior        
  14. Field    Type    IndexInfo
  15. DivisionIDFK;    Number;    Foreign Key for the Division ID
  16. YearID;    Number;    Year
  17. MonthID;    Number;    Foreign Key for the Month ID
  18. GWP;    Number;    Gross Premium
  19. NWP;    Number;    Gross Premium
  20. ProductIDFK;    Number;    Foreign Key for the Product ID
  21. ProductName;    Text;    Product Name
  22.  
  23.  
  24. Table Name =  tblbudget        
  25. Field    Type    IndexInfo
  26. DivisionIDFK;    Number;    Foreign Key for the Division ID
  27. YearID;    Number;    Year
  28. MonthID;    Number;    Foreign Key for the Month ID
  29. GWP;    Number;    Gross Premium
  30. NWP;    Number;    Gross Premium
  31. ProductIDFK;    Number;    Foreign Key for the Product ID
  32. ProductName;    Text;    Product Name
  33.  
  34.  
  35. Table Name =  tblproduct        
  36. Field    Type    IndexInfo
  37. ProductID;    AutoNumber;    The Product ID
  38. ProductName;    Text;    Product Name
  39.  
The Queries:

ctqQueryP:
Expand|Select|Wrap|Line Numbers
  1.  TRANSFORM Nz(Sum(tblStaticAllForecast.GWP),"0") AS GWP_Total
  2. SELECT tblStaticAllForecast.ProductIDFK
  3. FROM tblStaticAllForecast
  4. WHERE (((tblStaticAllForecast.DivisionIDFK)=[forms].[Rpt_Sum_Product].[cbodivision]) AND ((tblStaticAllForecast.YearID)=[forms].[Rpt_Sum_Product].[cboyear]) AND ((tblStaticAllForecast.MonthID)=[forms].[Rpt_Sum_Product].[cbomonth]) AND ((tblStaticAllForecast.Binding_Percentage)>=75))
  5. GROUP BY tblStaticAllForecast.ProductIDFK
  6. PIVOT "Week " & Format([FWeek]) In ("Week 1","Week 2","Week 3","Week 4","Week 5");
ctqQueryP_B:
Expand|Select|Wrap|Line Numbers
  1.  PARAMETERS [forms].[Rpt_Sum_Product].[cboyear] Short, [forms].[Rpt_Sum_Product].[cbodivision] Short, [forms].[Rpt_Sum_Product].[cbomonth] Short;
  2. TRANSFORM Nz(Sum(tblbudget.GWP),"0") AS GWP_BUD
  3. SELECT tblbudget.ProductIDFK, tblbudget.ProductName
  4. FROM tblbudget
  5. WHERE (((tblbudget.YearID)=[forms].[Rpt_Sum_Product].[cboyear]) AND ((tblbudget.MonthID)=[forms].[Rpt_Sum_Product].[cbomonth]) AND ((tblbudget.DivisionIDFK)=[forms].[Rpt_Sum_Product].[cbodivision]))
  6. GROUP BY tblbudget.ProductIDFK, tblbudget.ProductName
  7. PIVOT tblbudget.YearID;
ctqQueryP_P:
Expand|Select|Wrap|Line Numbers
  1.  PARAMETERS [forms].[Rpt_Sum_Product].[cboyear] Short, [forms].[Rpt_Sum_Product].[cbodivision] Short, [forms].[Rpt_Sum_Product].[cbomonth] Short;
  2. TRANSFORM Nz(Sum(tblPrior.GWP),"0") AS GWP_PRI
  3. SELECT tblPrior.ProductIDFK, tblPrior.ProductName
  4. FROM tblPrior
  5. WHERE (((tblPrior.YearID)=[forms].[Rpt_Sum_Product].[cboyear]-1) AND ((tblPrior.MonthID)=[forms].[Rpt_Sum_Product].[cbomonth]) AND ((tblPrior.DivisionIDFK)=[forms].[Rpt_Sum_Product].[cbodivision]))
  6. GROUP BY tblPrior.ProductIDFK, tblPrior.ProductName
  7. PIVOT tblPrior.YearID;
qryAllProductsCtq:
Expand|Select|Wrap|Line Numbers
  1.  SELECT tblProduct.ProductID, tblProduct.ProductName, 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
  2. FROM ctqQueryP RIGHT JOIN tblProduct ON ctqQueryP.ProductIDFK = tblProduct.ProductID;
qryAllProductsCtq_B:
Expand|Select|Wrap|Line Numbers
  1.  SELECT tblProduct.ProductID, tblProduct.ProductName, Nz([2008],0) AS GWP_BUD
  2. FROM tblProduct LEFT JOIN ctqQueryP_B ON tblProduct.ProductID = ctqQueryP_B.ProductIDFK
  3. ORDER BY tblProduct.ProductID;
qryAllProductsCtq_P:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProduct.ProductID, tblProduct.ProductName, Nz([2007],0) AS GWP_PRI
  2. FROM tblProduct LEFT JOIN ctqQueryP_P ON tblProduct.ProductID = ctqQueryP_P.ProductIDFK
  3. ORDER BY tblProduct.ProductID;
qryF_byProd_PB (FINAL Query that drives my report):
Expand|Select|Wrap|Line Numbers
  1. SELECT qryAllProductsCtq.ProductID, qryAllProductsCtq.ProductName, qryAllProductsCtq.Week_1, qryAllProductsCtq.Week_2, qryAllProductsCtq.Week_3, qryAllProductsCtq.Week_4, qryAllProductsCtq.Week_5, Nz([GWP_BUD],0) AS GWPBUD, Nz([GWP_PRI],0) AS GWPPRI
  2. FROM (qryAllProductsCtq LEFT JOIN qryAllProductsCtq_B ON qryAllProductsCtq.ProductID = qryAllProductsCtq_B.ProductID) INNER JOIN qryAllProductsCtq_P ON qryAllProductsCtq.ProductID = qryAllProductsCtq_P.ProductID
  3. GROUP BY qryAllProductsCtq.ProductID, qryAllProductsCtq.ProductName, qryAllProductsCtq.Week_1, qryAllProductsCtq.Week_2, qryAllProductsCtq.Week_3, qryAllProductsCtq.Week_4, qryAllProductsCtq.Week_5, Nz([GWP_BUD],0), Nz([GWP_PRI],0);
  4.  
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.
May 6 '08 #20

FishVal
Expert 2.5K+
P: 2,653
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.
  • qryAllProductsCtq and qryAllProductsCtq_P and qryAllProductsCtq_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
May 6 '08 #21

kcdoell
100+
P: 230
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:
Expand|Select|Wrap|Line Numbers
  1.  SELECT tblProduct.ProductName, 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.GWP_PRI
  2. FROM ((tblProduct LEFT JOIN ctqQueryP ON tblProduct.ProductID = ctqQueryP.ProductIDFK) LEFT JOIN QryProd_Bud ON tblProduct.ProductID = QryProd_Bud.ProductIDFK) LEFT JOIN QryProd_Prior ON tblProduct.ProductID = QryProd_Prior.ProductIDFK;
May 7 '08 #22

FishVal
Expert 2.5K+
P: 2,653
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
May 7 '08 #23

Post your reply

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