Query results based on two control fields, I am at wits end…….

[kcdoell]

Good Morning:

I have 12 [CreditRegionNam e] and 3 [ProductType]. I created the below query:

[code=sql]
SELECT [CreditRegionNam e], [ProductType], Sum([GWP_PRI]) AS [T_GWP_PRI], Sum([NWP_PRI]) AS [T_NWP_PRI]
FROM UqryProductType Static
WHERE ((([PRIYear])=[forms].[Rpt_Sum_Product].[cboYear]-1) AND (([MonthID])=[forms].[Rpt_Sum_Product].[cbomonth]))
GROUP BY [CreditRegionNam e], [ProductType];
[/code]

When I run this query I want each [CreditRegionNam e] to be displayed as well as its [ProductType] with its corresponding [NWP_PRI] & [GWP_PRI] values.

So at the end of the day I want 36 records in total when I run the query.

Example:

If [CreditRegionNam e] was

UTAH
TEXAS
FLORIDA

And [ProductType] was
APPLES
PEARS
ORANGES

I want the query to display:

[CreditRegionNam e]__[ProductType]__[T_GWP_PRI]__[T_NWP_PRI]

UTAH___________ ____APPLES_____ ______0________ _____45
UTAH___________ ____PEARS______ ______20_______ _____56
UTAH___________ ____ORANGES____ _____0_________ ____0
TEXAS__________ ____APPLES_____ ______11_______ ______74
TEXAS__________ ____PEARS______ ______82_______ ______0
TEXAS__________ ____ORANGES____ _____15________ _____65
Etc.

I do have separate tables that are not part of the above query.
[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]

But when I include them in the above query, I can not get them to join (Left or Right) to give me the result I am looking for (36 records).

At this point I am at wits end, I believe it can be done but I am starting to lose faith.

If anybody could help, it would be greatly appreciated.

Thanks,

Keith.

[NeoPa]

What are the records of [UqryProductType Static]? Can you post them in here.

PS. Am I right in thinking that the first quoted set of SQL does actually work? It's only when you try to link in the reference tables that anything goes awry.

[kcdoell]

What are the records of [UqryProductType Static]? Can you post them in here.

NeoPa:

Thanks for replying back to me. [UqryProductType Static] is a union query that I built based on three tables. (tblStaticAllFo recast, tblPrior & tblbudget):

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]

Below is the SQL of [UqryProductType Static] that feeds off those tables:

[code=sql]
SELECT
[DivisionIDFK], [WrkRegIDFK], [CreditRegionNam e], [ProductType], 0 AS [GWP_PRI], 0 AS [NWP_PRI], [GWP] AS [GWP_BUD], [NWP] AS [NWP_BUD], [MonthID], 0 AS [GWP_STAT], 0 AS [NWP_STAT], 0 AS [FWeek], 0 AS [BindPercent], [YearID] AS [BUDYear], 0 AS [PRIYear], 0 AS [SATCYear]
FROM tblProduct
INNER JOIN (tblCreditRegio n INNER JOIN tblbudget
ON tblCreditRegion .CreditRegID = tblbudget.Credi tRegIDFK)
ON tblProduct.Prod uctID = tblbudget.Produ ctIDFK

UNION ALL SELECT
[DivisionIDFK], [WrkRegIDFK], [CreditRegionNam e], [ProductType], [GWP] AS [GWP_PRI], [NWP] AS [NWP_PRI], 0 AS [GWP_BUD], 0 AS [NWP_BUD], [MonthID], 0 AS [GWP_STAT], 0 AS [NWP_STAT], 0 AS [FWeek], 0 AS [BindPercent], 0 AS [BUDYear], [YearID] AS [PRIYear], 0 AS [SATCYear]
FROM tblProduct
INNER JOIN (tblCreditRegio n INNER JOIN tblPrior
ON tblCreditRegion .CreditRegID = tblPrior.Credit RegIDFK)
ON tblProduct.Prod uctID = tblPrior.Produc tIDFK

UNION ALL SELECT
[DivisionIDFK], [WrkRegIDFK], [CreditRegionNam e], [ProductType], 0 AS [GWP_PRI], 0 AS [NWP_PRI], 0 AS [GWP_BUD], 0 AS [NWP_BUD], [MonthID], [GWP] AS [GWP_STAT], [NWP] AS [NWP_STAT], [FWeek], [Binding_Percent age] AS [BindPercent], 0 AS [BUDYear], 0 AS [PRIYear], [YearID] AS [SATCYear]
FROM tblProduct
INNER JOIN (tblCreditRegio n INNER JOIN tblStaticAllFor ecast
ON tblCreditRegion .CreditRegID = tblStaticAllFor ecast.CreditReg IDFK)
ON tblProduct.Prod uctID = tblStaticAllFor ecast.ProductID FK;
[/code]

PS. Am I right in thinking that the first quoted set of SQL does actually work? It's only when you try to link in the reference tables that anything goes awry.

Actual when I run the first quoted SQL I get 33 records instead of 36. One of my [CreditRegionNam e] only has one [ProductType] listed and the other, [CreditRegionNam e] has two [ProductType] listed. Of course, when I do begin to link [UqryProductType Static] to my tblCreditRegion (On CreditRegionNam e) or tblproduct
(On ProductType) all bets are off and my count goes down further….

Over the last two weeks I have learned a lot about building Union queries and Left and Right joins. Everything has been trail and error. I have managed to do part of what I when I am looking to do only one Parameter (lets say in this case populating all the CreditRegionNam es (12)) (Creating a pivot table looking report……) but including two now has left me dumfounded.

I hope you can help..

Thanks,

Keith.

P.S. My apologies for such a late response……attac hed is a visual of the report I am trying to create.....

[kcdoell]

……attached is a visual of the report I am trying to create.....

With regards to the visual on the report I just posted:

Week 1, Week 2, Week 3, & Week 4 is coming from my control [FWeek] located on my [tblStaticAllFor ecast] I have a crosstab query that breaks out [FWeek] to the proper above format. The Budget column on the report; [GWP]'s data coming from my [tblBudget] and The Prior column on the report; [GWP]'s data coming from my [tblPrior]. I created a union query to get all of these tables on one recordset and then began to create the queries from there (see below).

When it was just one parameter, I would create the three queries and then create a final query that would LEFT join them on that parameter. I went down that road and saw that including the second parameter (inserting the tblProduct and linking) reduced the record count..... and that is where the struggle has been.

Keith.

[NeoPa]

That's a lot to go through Keith, and I will have a stab at it when I can.

In the mean-time, check out your WHERE clauses. These have an effect on the number of records produced too (in conjunction with the JOINs). Sometimes they are applied after instead of before (before ==> in a subquery or in one of the UNIONed datasets).

Normally, adding a LEFT JOIN (of itself) to a dataset will not reduce the number of records displayed.

[kcdoell]

That's a lot to go through Keith, ..........

NeoPa:

I was just trying to develop a complete visual on my challenge, it is okay if you dont get to it. I am plugging away at it as we speak... If you do find sometime and come up with something let me know. In the meantime, I play around with the where statements being in my final query...and most likely other things.

Best regards,

Keith.

[NeoPa]

Sounds like the type of member I like to deal with Keith :)

I'll let you know if I get anywhere.

[NeoPa]

I didn't manage to get on at all over the weekend so I will fit in a look at this as soon as I can.

[NeoPa]

What are the records of [UqryProductType Static]? Can you post them in here.
...

Any chance of this data being posted Keith? That would give me something more concrete to get my teeth into (I hate those mixed metaphors - they do nothing for my dentistry :D)

[kcdoell]

Any chance of this data being posted Keith? That would give me something more concrete to get my teeth into (I hate those mixed metaphors - they do nothing for my dentistry :D)

NeoPa:

My family keep me plenty busy, just getting back to concentrating again on my challenge... I sent you an e-mail regarding your above request.

Keith.

[NeoPa]

I was really just looking for the data being posted in here, but if it's all sensitive data then I'll communicate with you privately about how we can work this.