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

Building a query based on one field’s value but grouping it by another…..

kcdoell
100+
P: 230
Hello:

I just learned how to put crosstabs queries together but this one in particular is adding a new dimension in which I was hoping someone could give me some direction. I have the following tables

The Tables:

Expand|Select|Wrap|Line Numbers
  1. Table Name =  tblStaticAllForecast        
  2.  
  3. Field    Type    IndexInfo
  4. YearID;    Number;    Year
  5. MonthID;    Number;    Foreign Key for the Month ID
  6. CreditRegIDFK;    Number;    Foreign Key for the Credit Region 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.  
Expand|Select|Wrap|Line Numbers
  1. Table Name =  tblPrior        
  2.  
  3. CreditRegIDFK;    Number;    Foreign Key for the Credit Region ID
  4. Field    Type    IndexInfo
  5. YearID;    Number;    Year
  6. MonthID;    Number;    Foreign Key for the Month ID
  7. GWP;    Number;    Gross Premium
  8. NWP;    Number;    Gross Premium
  9. ProductIDFK;    Number;    Foreign Key for the Product ID
  10. ProductName;    Text;    Product Name
  11.  
Expand|Select|Wrap|Line Numbers
  1. Table Name =  tblbudget        
  2.  
  3. CreditRegIDFK;    Number;    Foreign Key for the Credit Region ID
  4. Field    Type    IndexInfo
  5. YearID;    Number;    Year
  6. MonthID;    Number;    Foreign Key for the Month ID
  7. GWP;    Number;    Gross Premium
  8. NWP;    Number;    Gross Premium
  9. ProductIDFK;    Number;    Foreign Key for the Product ID
  10. ProductName;    Text;    Product Name
  11.  
Expand|Select|Wrap|Line Numbers
  1. Table Name =  tblCreditRegion        
  2.  
  3. Field    Type    IndexInfo
  4. CreditRegID;    AutoNumber;    The Credit ID
  5. CreditRegionName;    Text;    Product Name
  6.  
Expand|Select|Wrap|Line Numbers
  1. Table Name =  tblproduct        
  2.  
  3. Field    Type    IndexInfo
  4. ProductID;    AutoNumber;    The Product ID
  5. ProductName;    Text;    Product Name
  6. ProductType; Text;    Product  Type
  7.  
I need to run a report where I have to show all of the credit regions [GWP] values but also group them by [ProductType]. I got to the point where I have a query that displays all of the Credit Regions [GWP] values (12 in this case). This is comprised of three other queries (“ctqQueryC_G”, “QryProd_Credit_Bud”, & “QryProd_Credit_Prior”) that I joined together on [CreditRegID] via my “tblCreditRegion” table:

Query Name = qryGWP_Prod_Credit:

Expand|Select|Wrap|Line Numbers
  1.  SELECT tblCreditRegion.CreditRegionName, CLng(Nz([QryProd_Credit_Bud.SumOfGWP],0)) AS GWP_Bud, CLng(Nz([Week 1],0)) AS Week_1, CLng(Nz([Week 2],0)) AS Week_2, CLng(Nz([Week 3],0)) AS Week_3, CLng(Nz([Week 4],0)) AS Week_4, CLng(Nz([Week 5],0)) AS Week_5, CLng(Nz([QryProd_Credit_Prior.SumOfGWP],0)) AS GWP_PRI
  2. FROM ((tblCreditRegion LEFT JOIN ctqQueryC_G ON tblCreditRegion.CreditRegID = ctqQueryC_G.CreditRegIDFK) LEFT JOIN QryProd_Credit_Bud ON tblCreditRegion.CreditRegID = QryProd_Credit_Bud.CreditRegIDFK) LEFT JOIN QryProd_Credit_Prior ON tblCreditRegion.CreditRegID = QryProd_Credit_Prior.CreditRegIDFK
  3. GROUP BY tblCreditRegion.CreditRegionName, CLng(Nz([QryProd_Credit_Bud.SumOfGWP],0)), CLng(Nz([Week 1],0)), CLng(Nz([Week 2],0)), CLng(Nz([Week 3],0)), CLng(Nz([Week 4],0)), CLng(Nz([Week 5],0)), CLng(Nz([QryProd_Credit_Prior.SumOfGWP],0));
  4.  
Now I need to get the [ProductType] in the query so that I can group it by Product Type in my report. I tried to include my product table to the above query but I hit errors. I also tried to add [ProductType] to my individual queries (“ctqQueryC_G”, “QryProd_Credit_Bud”, & “QryProd_Credit_Prior”) and then somehow include it in the above mention final query but got lost.

Can anyone give me a sense of how to go about incorporating this additional grouping/breakout? I mean breakout because that is what I would be doing; breaking out the [GWP] values by Credit Region (Which I already did above) but then by Product Type........

Thanks,

Keith.
May 12 '08 #1
Share this Question
Share on Google+
12 Replies


nico5038
Expert 2.5K+
P: 3,072
Hi Keith

I'm a bit stunned about the tablefields, especially in tblPrior and tblBudget to find:
ProductName; Text; Product Name
They should be derived from the tblProduct when needed...

I also wonder why there's a table tblPrior, is it to store previous YearMonth's ?

OK, for the query I would add the tblProduct with a JOIN to the "master table" tblCreditRegion, when this always hold the product of the entire row. When the products differ in the queries the table is LEFT JOINing to, I would like to know why and how the structure should be.
Having JOINed the table will enable the addition of the ProductType.

Nic;o)
May 12 '08 #2

kcdoell
100+
P: 230
Hi Keith

I'm a bit stunned about the tablefields, especially in tblPrior and tblBudget to find:
ProductName; Text; Product Name
They should be derived from the tblProduct when needed...

I also wonder why there's a table tblPrior, is it to store previous YearMonth's ?....

Nic;o)

Nico:

Yes, it is to store previous years months. In fact, the Budget table and the Prior table are not directly tied to my DB in the sense that records are being written to them via my DB. Rather they are for reporting purposes. These are in fact tables that are loaded at the begin of the year and then left static. I originally requested that they give me “Product ID” so that I could tie them to my tables that were dynamic (Each record represents a [GWP] value for each [ProductID]). I imported these tables and then did a one to many relationship on ProductID but I am lost on how to tie Product Type to my queries (In a non-direct way) so I included both the” “Product Name” “Product Type” to both the budget and prior tables, so far it was the only way I knew how to do it…. If you have a better way of referencing the Product Type that would be greatly appreciated…. I figure there is a way via queries but I simply don’t know how……
May 12 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Just check the result of the query and check or all productID's (and thus the names) are identical on one row. When they are you can JOIN like I proposed, otherwise you have a problem, as different products can have different types and which one to take for reporting.....

Nic;o)
May 12 '08 #4

kcdoell
100+
P: 230
Hi Keith

OK, for the query I would add the tblProduct with a JOIN to the "master table" tblCreditRegion, when this always hold the product of the entire row. When the products differ in the queries the table is LEFT JOINing to, I would like to know why and how the structure should be.
Having JOINed the table will enable the addition of the ProductType.

Nic;o)

I am confused, you want me to join my tblProduct to my tblCreditRegion?? How can I join them if the tblCreditRegion does not have the field ProductID??

Thanks for the reply.

Keith.
May 12 '08 #5

nico5038
Expert 2.5K+
P: 3,072
Oops, mixed it with your first table tblStaticAllForecast.
I can't see what has been coded in the queries the tblCreditRegion is linked to.
Just add to those queries the tblProduct and include the ProductType to see or each individual row has the same product (and thus ProductType)

Nic;o)
May 12 '08 #6

kcdoell
100+
P: 230
Oops, mixed it with your first table tblStaticAllForecast.
I can't see what has been coded in the queries the tblCreditRegion is linked to.
Just add to those queries the tblProduct and include the ProductType to see or each individual row has the same product (and thus ProductType)

Nic;o)

Nico:

I am still stuck on this one... I added the Product table and thus the product type field to the individual queries. Now in my final query I have the three queries joined to my Credit Region so that I can get all the region names. I thought I would only get 36 records since there is only 12 credit regions and 3 product types. Instead, I am getting 85 records. Looks like it is duplicating some records in the query even though I did not change the join property above.

Do I need to now add the Product table to this final query?? and if so, should it be joined with something. Like I said, I am already joining my Credit Region table with the CreditRegionID so I don't know how to factor in the Product Type. Is this a double join move??

This report displays all Credit Regions but is broken up by Product Type.

Thanks,

Keith.
May 13 '08 #7

nico5038
Expert 2.5K+
P: 3,072
Hmm, hard for me to see your tables structure in the queries, but I get the impression you would need a UNION to handle this.

F.e.: create a query for tblPrior and JOIN with the Credit Regions and the Product, now do the same for the other two tables and make sure that they have all three the same number of columns and that the fields correspond.
Now create a new query like:
select * from qryUNION1
UNION
select * from qryUNION2
UNION
select * from qryUNION3;

Nic;o)
May 13 '08 #8

kcdoell
100+
P: 230
Hmm, hard for me to see your tables structure in the queries, but I get the impression you would need a UNION to handle this.

F.e.: create a query for tblPrior and JOIN with the Credit Regions and the Product, now do the same for the other two tables and make sure that they have all three the same number of columns and that the fields correspond.
Now create a new query like:
select * from qryUNION1
UNION
select * from qryUNION2
UNION
select * from qryUNION3;

Nic;o)

Okay but one of my queries has a field that is [FWeek] which my other two did not have. Below was my Union statement:

Expand|Select|Wrap|Line Numbers
  1. select * from ctqQueryC_G
  2. UNION
  3. select * from QryProd_Credit_Bud
  4. UNION
  5. select * from ctqQueryC_G;
  6.  
Afterwards as you warned, I got the following error:

Expand|Select|Wrap|Line Numbers
  1. The number of columns in the two selected tables or queries of a union query do not match. (Error 3307)
  2. The two tables or queries joined by the UNION operation must generate the same number of columns. Remove columns from the SELECT statement that has too many columns or include more columns in the SELECT statement that has too few.
How do I work around that?? That is to say I dont understand the work around they are describing "statement that has too many columns or include more columns in the SELECT statement that has too few."

Currently when I run the individual queries I get the following visual:

ctqQueryC_G:

ProductType___CreditRegIDFK___Week 1___Week 2___Week 3___Week 4___Week 5
Casualty___________2___________3517______________3 471

QryProd_Credit_Bud:

ProductType_____CreditRegIDFK_____SumOfNWP______Su mOfGWP
Casualty__________Atlanta___________2885__________ 3398

QryProd_Credit_Prior:

ProductType______CreditRegIDFK______SumOfNWP______ SumOfGWP
Casualty____________Atlanta___________2828________ __3316

Do I have to somehow create dummy fields so that every query has seven columns since my "ctqQueryC_G" has seven?

Thanks for getting back to me..

Keith.
May 13 '08 #9

kcdoell
100+
P: 230
Nico:

Before responding to my last reply let me try something, I have been reading up on Union queries, something I never did before.....

I will get back

Thanks and thanks for the idea.

Keith.
May 13 '08 #10

nico5038
Expert 2.5K+
P: 3,072
You already gave the solution: dummy columns :-)
Just make sure they are of the same "data type". For numbers use 0 and text use "" (Empty string)
The names of the columns of the first query are used for the final result, so use the AS predicate for the dummy columns !

Nic;o)
May 14 '08 #11

kcdoell
100+
P: 230
Nico:

When I run the union query I now get this message:
Expand|Select|Wrap|Line Numbers
  1. This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)
  2.  
My individual queries work fine. Have you ever run into this message?
May 14 '08 #12

kcdoell
100+
P: 230
Nico:

I set up my queries like the following (how I inputted the dummy fileds of which I called the same field name as the other query but did a ["qry field name": Null] in the query field. I needed to change the Null to 0 since it was numeric..

Expand|Select|Wrap|Line Numbers
  1. ctqQueryC_G                                        
  2. CreditRegIDFK    ProductType    GWP_BUD    NWP_BUD    GWP_PRI    NWP_PRI    Week 1    Week 2    Week 3    Week 4    Week 5
  3. Atlanta    Other    0    0    0    0    1022        1474        
  4.  
  5.  
  6. QryProd_Credit_Bud                                        
  7. CreditRegIDFK    ProductType    GWP_BUD    NWP_BUD    GWP_PRI    NWP_PRI    Week 1    Week 2    Week 3    Week 4    Week 5
  8. Atlanta    Casualty    3398    2885    0    0    0    0    0    0    0
  9.  
  10.  
  11. QryProd_Credit_Prior                                        
  12. CreditRegIDFK    ProductType    GWP_BUD    NWP_BUD    GWP_PRI    NWP_PRI    Week 1    Week 2    Week 3    Week 4    Week 5
  13. Atlanta    Casualty    0    0    3316    2828    0    0    0    0    0
That made the error go away and the query ran... Getting closer.....

Keith.
May 14 '08 #13

Post your reply

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