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

[kcdoell]

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:

Table Name =  tblStaticAllForecast        
 
Field    Type    IndexInfo
YearID;    Number;    Year
MonthID;    Number;    Foreign Key for the Month ID
CreditRegIDFK;    Number;    Foreign Key for the Credit Region ID
ProductIDFK;    Number;    Foreign Key for the Product ID
GWP;    Number;    Gross Premium
NWP;    Number;    Gross Premium
Fweek;    Number;    Week number of a month (1-5)
Binding_Percentage;    Number;    Binding Percentage
 

Table Name =  tblPrior        
 
CreditRegIDFK;    Number;    Foreign Key for the Credit Region ID
Field    Type    IndexInfo
YearID;    Number;    Year
MonthID;    Number;    Foreign Key for the Month ID
GWP;    Number;    Gross Premium
NWP;    Number;    Gross Premium
ProductIDFK;    Number;    Foreign Key for the Product ID
ProductName;    Text;    Product Name
 

Table Name =  tblbudget        
 
CreditRegIDFK;    Number;    Foreign Key for the Credit Region ID
Field    Type    IndexInfo
YearID;    Number;    Year
MonthID;    Number;    Foreign Key for the Month ID
GWP;    Number;    Gross Premium
NWP;    Number;    Gross Premium
ProductIDFK;    Number;    Foreign Key for the Product ID
ProductName;    Text;    Product Name
 

Table Name =  tblCreditRegion        
 
Field    Type    IndexInfo
CreditRegID;    AutoNumber;    The Credit ID
CreditRegionName;    Text;    Product Name
 

Table Name =  tblproduct        
 
Field    Type    IndexInfo
ProductID;    AutoNumber;    The Product ID
ProductName;    Text;    Product Name
ProductType; Text;    Product  Type
 

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:

 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
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
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));
 

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.

[nico5038]

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)

[kcdoell]

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……

[nico5038]

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)

[kcdoell]

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.

[nico5038]

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)

[kcdoell]

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.

[nico5038]

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)

[kcdoell]

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:

select * from ctqQueryC_G
UNION
select * from QryProd_Credit_Bud
UNION
select * from ctqQueryC_G;
 

Afterwards as you warned, I got the following error:

The number of columns in the two selected tables or queries of a union query do not match. (Error 3307)
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.

[kcdoell]

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.

[nico5038]

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)

[kcdoell]

Nico:

When I run the union query I now get this message:

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)
 

My individual queries work fine. Have you ever run into this message?

[kcdoell]

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..

ctqQueryC_G                                        
CreditRegIDFK    ProductType    GWP_BUD    NWP_BUD    GWP_PRI    NWP_PRI    Week 1    Week 2    Week 3    Week 4    Week 5
Atlanta    Other    0    0    0    0    1022        1474        
 
 
QryProd_Credit_Bud                                        
CreditRegIDFK    ProductType    GWP_BUD    NWP_BUD    GWP_PRI    NWP_PRI    Week 1    Week 2    Week 3    Week 4    Week 5
Atlanta    Casualty    3398    2885    0    0    0    0    0    0    0
 
 
QryProd_Credit_Prior                                        
CreditRegIDFK    ProductType    GWP_BUD    NWP_BUD    GWP_PRI    NWP_PRI    Week 1    Week 2    Week 3    Week 4    Week 5
Atlanta    Casualty    0    0    3316    2828    0    0    0    0    0

That made the error go away and the query ran... Getting closer.....

Keith.