473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

kcdoell
230 100+
Good Morning:

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

Expand|Select|Wrap|Line Numbers
  1. SELECT [CreditRegionName], [ProductType], Sum([GWP_PRI]) AS [T_GWP_PRI], Sum([NWP_PRI]) AS [T_NWP_PRI]
  2. FROM UqryProductTypeStatic
  3. WHERE ((([PRIYear])=[forms].[Rpt_Sum_Product].[cboYear]-1) AND (([MonthID])=[forms].[Rpt_Sum_Product].[cbomonth]))
  4. GROUP BY [CreditRegionName], [ProductType];
  5.  
When I run this query I want each [CreditRegionName] 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 [CreditRegionName] was

UTAH
TEXAS
FLORIDA

And [ProductType] was
APPLES
PEARS
ORANGES

I want the query to display:

[CreditRegionName]__[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.
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.  
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.
May 16 '08 #1
10 2151
NeoPa
32,556 Expert Mod 16PB
What are the records of [UqryProductTypeStatic]? 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.
May 16 '08 #2
kcdoell
230 100+
What are the records of [UqryProductTypeStatic]? Can you post them in here.
NeoPa:

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

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.  
Below is the SQL of [UqryProductTypeStatic] that feeds off those tables:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [DivisionIDFK], [WrkRegIDFK], [CreditRegionName], [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]
  3. FROM tblProduct 
  4. INNER JOIN (tblCreditRegion INNER JOIN tblbudget 
  5. ON tblCreditRegion.CreditRegID = tblbudget.CreditRegIDFK) 
  6. ON tblProduct.ProductID = tblbudget.ProductIDFK
  7.  
  8. UNION ALL SELECT
  9. [DivisionIDFK], [WrkRegIDFK], [CreditRegionName], [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]
  10. FROM tblProduct 
  11. INNER JOIN (tblCreditRegion INNER JOIN tblPrior 
  12. ON tblCreditRegion.CreditRegID = tblPrior.CreditRegIDFK) 
  13. ON tblProduct.ProductID = tblPrior.ProductIDFK
  14.  
  15. UNION ALL SELECT
  16. [DivisionIDFK], [WrkRegIDFK], [CreditRegionName], [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_Percentage] AS [BindPercent], 0 AS [BUDYear], 0 AS [PRIYear], [YearID] AS [SATCYear]
  17. FROM tblProduct 
  18. INNER JOIN (tblCreditRegion INNER JOIN tblStaticAllForecast
  19. ON tblCreditRegion.CreditRegID = tblStaticAllForecast.CreditRegIDFK) 
  20. ON tblProduct.ProductID = tblStaticAllForecast.ProductIDFK;
  21.  
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 [CreditRegionName] only has one [ProductType] listed and the other, [CreditRegionName] has two [ProductType] listed. Of course, when I do begin to link [UqryProductTypeStatic] to my tblCreditRegion (On CreditRegionName) 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 CreditRegionNames (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……attached is a visual of the report I am trying to create.....
Attached Files
File Type: zip ImageRPt.zip (35.7 KB, 92 views)
May 16 '08 #3
kcdoell
230 100+
……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 [tblStaticAllForecast] 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.
May 16 '08 #4
NeoPa
32,556 Expert Mod 16PB
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.
May 16 '08 #5
kcdoell
230 100+
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.
May 16 '08 #6
NeoPa
32,556 Expert Mod 16PB
Sounds like the type of member I like to deal with Keith :)

I'll let you know if I get anywhere.
May 16 '08 #7
NeoPa
32,556 Expert Mod 16PB
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.
May 19 '08 #8
NeoPa
32,556 Expert Mod 16PB
What are the records of [UqryProductTypeStatic]? 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)
May 19 '08 #9
kcdoell
230 100+
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.
May 19 '08 #10
NeoPa
32,556 Expert Mod 16PB
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.
May 19 '08 #11

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

Similar topics

29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
4
by: Martin Lacoste | last post by:
(Access 2000) Two issues: Within a query, I need to return a field name as data (see eg. below). I need to search within 80 fields (same criteria) - is there a way to avoid 80 separate...
2
by: Seth Delaney | last post by:
I have a form with multiple unbound text boxes which serves as a "search form". I can enter my search parameters in the various boxes as needed and click okay. My records are then filtered to...
3
by: dskillingstad | last post by:
I'd appreciate any help I can get. I'm not sure what I'm doing wrong, but.... I've searched these groups for some solutions but no luck. I have an unbound form (frmSearch), with several unbound...
6
by: Brian | last post by:
Hello, Basically, I'm running a query on a form's activation, and I'd like to have the results of the query be placed into other fields on the same form automatically. Does anybody know how...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
3
by: austin1539 | last post by:
I am trying to run an Access query from Excel. The query, called "ProdActs1" works in Access and is run from using information from a table called "Queries". When a button is clicked in Excel, data...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.