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

Union Query Question missing records......

kcdoell
100+
P: 230
Hello:

I am trying to create a union query but do not have a lot of experience. Basically I have the below tables:

The Tables:

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.  
tblbudget has 7,021 records in it and tblPrioir has 3,064. I am trying to create a union query that will give me a total of 10,085. So far I wrote the following union query:

Query Name = UqryProductTypeStatic:

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], [YearID], 0 AS [GWP_STAT], 0 AS [NWP_STAT], 0 AS [FWeek]
  3. FROM tblProduct 
  4. INNER JOIN (tblCreditRegion INNER JOIN tblbudget 
  5. ON tblCreditRegion.CreditRegID = tblbudget.CreditRegIDFK) 
  6. ON tblProduct.ProductID = tblbudget.ProductIDFK
  7. UNION SELECT
  8. [DivisionIDFK], [WrkRegIDFK], [CreditRegionName], [ProductType], [GWP] AS [GWP_PRI], [NWP] AS [NWP_PRI], 0 AS [GWP_BUD], 0 AS [NWP_BUD], [MonthID], [YearID], 0 AS [GWP_STAT], 0 AS [NWP_STAT], 0 AS [FWeek]
  9. FROM tblProduct 
  10. INNER JOIN (tblCreditRegion INNER JOIN tblPrior 
  11. ON tblCreditRegion.CreditRegID = tblPrior.CreditRegIDFK) 
  12. ON tblProduct.ProductID = tblPrior.ProductIDFK;
  13.  
but my result when I run it is only 7,276. I am not hitting any error when I run it so I am thinking that I must be missing something.

Any ideas or advice would be helpful? P.S. I did run them separately and I get 7,021 and 3,064 so I thought all that would be needed was the UNION SELECT expression....

Thanks,

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


puppydogbuddy
Expert 100+
P: 1,923
Hello:

I am trying to create a union query but do not have a lot of experience. Basically I have the below tables:

The Tables:

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.  
tblbudget has 7,021 records in it and tblPrioir has 3,064. I am trying to create a union query that will give me a total of 10,085. So far I wrote the following union query:

Query Name = UqryProductTypeStatic:

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], [YearID], 0 AS [GWP_STAT], 0 AS [NWP_STAT], 0 AS [FWeek]
  3. FROM tblProduct 
  4. INNER JOIN (tblCreditRegion INNER JOIN tblbudget 
  5. ON tblCreditRegion.CreditRegID = tblbudget.CreditRegIDFK) 
  6. ON tblProduct.ProductID = tblbudget.ProductIDFK
  7. UNION SELECT
  8. [DivisionIDFK], [WrkRegIDFK], [CreditRegionName], [ProductType], [GWP] AS [GWP_PRI], [NWP] AS [NWP_PRI], 0 AS [GWP_BUD], 0 AS [NWP_BUD], [MonthID], [YearID], 0 AS [GWP_STAT], 0 AS [NWP_STAT], 0 AS [FWeek]
  9. FROM tblProduct 
  10. INNER JOIN (tblCreditRegion INNER JOIN tblPrior 
  11. ON tblCreditRegion.CreditRegID = tblPrior.CreditRegIDFK) 
  12. ON tblProduct.ProductID = tblPrior.ProductIDFK;
  13.  
but my result when I run it is only 7,276. I am not hitting any error when I run it so I am thinking that I must be missing something.

Any ideas or advice would be helpful? P.S. I did run them separately and I get 7,021 and 3,064 so I thought all that would be needed was the UNION SELECT expression....

Thanks,

Keith.
Keith,
Try changing line 7 To>>>>>> Union ALL Select
May 15 '08 #2

kcdoell
100+
P: 230
Keith,
Try changing line 7 To>>>>>> Union ALL Select
That was it! Thanks a million

Keith.
May 15 '08 #3

Post your reply

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