Union Query Question missing records......

[kcdoell]

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:

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
 

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:

 SELECT 
[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]
FROM tblProduct 
INNER JOIN (tblCreditRegion INNER JOIN tblbudget 
ON tblCreditRegion.CreditRegID = tblbudget.CreditRegIDFK) 
ON tblProduct.ProductID = tblbudget.ProductIDFK
UNION SELECT
[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]
FROM tblProduct 
INNER JOIN (tblCreditRegion INNER JOIN tblPrior 
ON tblCreditRegion.CreditRegID = tblPrior.CreditRegIDFK) 
ON tblProduct.ProductID = tblPrior.ProductIDFK;
 

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.

[puppydogbuddy]

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:

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
 

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:

 SELECT 
[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]
FROM tblProduct 
INNER JOIN (tblCreditRegion INNER JOIN tblbudget 
ON tblCreditRegion.CreditRegID = tblbudget.CreditRegIDFK) 
ON tblProduct.ProductID = tblbudget.ProductIDFK
UNION SELECT
[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]
FROM tblProduct 
INNER JOIN (tblCreditRegion INNER JOIN tblPrior 
ON tblCreditRegion.CreditRegID = tblPrior.CreditRegIDFK) 
ON tblProduct.ProductID = tblPrior.ProductIDFK;
 

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

[kcdoell]

Keith,
Try changing line 7 To>>>>>> Union ALL Select

That was it! Thanks a million

Keith.