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:

[code=text]
Table Name = tblStaticAllFor ecast

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_Percent age; Number; Binding Percentage
[/code]

[code=text]
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
[/code]

[code=text]
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
[/code]

[code=text]
Table Name = tblCreditRegion

Field Type IndexInfo
CreditRegID; AutoNumber; The Credit ID
CreditRegionNam e; Text; Product Name
[/code]

[code=text]
Table Name = tblproduct

Field Type IndexInfo
ProductID; AutoNumber; The Product ID
ProductName; Text; Product Name
ProductType; Text; Product Type
[/code]

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 “tblCreditRegio n” table:

Query Name = qryGWP_Prod_Cre dit:

[code=sql] SELECT tblCreditRegion .CreditRegionNa me, 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 ((tblCreditRegi on LEFT JOIN ctqQueryC_G ON tblCreditRegion .CreditRegID = ctqQueryC_G.Cre ditRegIDFK) LEFT JOIN QryProd_Credit_ Bud ON tblCreditRegion .CreditRegID = QryProd_Credit_ Bud.CreditRegID FK) LEFT JOIN QryProd_Credit_ Prior ON tblCreditRegion .CreditRegID = QryProd_Credit_ Prior.CreditReg IDFK
GROUP BY tblCreditRegion .CreditRegionNa me, 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));
[/code]

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 tblStaticAllFor ecast.
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 tblStaticAllFor ecast.
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:

[Code=sql]
select * from ctqQueryC_G
UNION
select * from QryProd_Credit_ Bud
UNION
select * from ctqQueryC_G;
[/Code]

Afterwards as you warned, I got the following error:

[code=text]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.[/code]

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___C reditRegIDFK___ Week 1___Week 2___Week 3___Week 4___Week 5
Casualty_______ ____2__________ _3517__________ ____3471

QryProd_Credit_ Bud:

ProductType____ _CreditRegIDFK_ ____SumOfNWP___ ___SumOfGWP
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:
[code=text]
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)
[/code]

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

[code=txt]
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[/code]

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

Keith.