Union Query (2013) - qry using union pulling SOME dups (1 Viewer)

I am attaching a document that shows the results correctly is an easier to read format. Kim


Afternoon,

I am working on my 2nd union query to pull revenue data from two different sources. In one table, I have the approved revenue budget. In another table, I have the adjusted revenue budget, the recognized revenue and the collected revenue.

I need to build a query that combines this data, so I built a union query to pull the Fund, Location, Department and Account number from each table to limit my duplicates. Here is my union query; which seems to pull fine...that I do not believe is my issue.

SELECT DWNLD_Essbase_Hyperion_Revenue.Department, DWNLD_Essbase_Hyperion_Revenue.Fund, DWNLD_Essbase_Hyperion_Revenue.Loc, DWNLD_Essbase_Hyperion_Revenue.Account
FROM DWNLD_Essbase_Hyperion_Revenue
UNION SELECT DWNLD_OV_Revenue.Dept, DWNLD_OV_Revenue.Fund, DWNLD_OV_Revenue.Location, DWNLD_OV_Revenue.Account
FROM DWNLD_OV_Revenue;

I use the union query as the basis of my results query; however, include the tables of DWNLD_Essbase_Hyperion_Revenue and the DWNLD_OV_Revenue tables.

When reviewing the results, I am receiving SOME Duplicates (I guess that is the right word). Let me see if I can explain by providing some of the data details and then showing my results.

DWNLD_Essbase_Hyperion_Revenue Data:
DepartmentFundLocAccountAppv_Budget00000000281000005999$0.0000000000281000005999$20,000.0000000000281008015999$0.0000000000572104025406$680,000.0000000000572104025901$0.0000000000572104025905$0.0000000000691608015180$2,110,000.0000000000691608015180$0.0000000000691600005180$0.0000000000691600005999$0.00


DWNLD_OV_Revenue Data:
FundLocationDeptAccountRevenue EstimateRecognized RevenueCollected Revenue10000000000000285999$20,000.00$0.00$0.0021040200000000575905$0.00$1,925.00$1,700.0021040200000000575406$680,000.00$0.00$0.0016000000000000695180$0.00$28,006.00$0.0016080100000000695180$2,110,000.00$201,449.91$115,426.86

Union Query Data:
DepartmentFundLocAccount0000000028100000599900000000281008015999000000005721040254060000000057210402590100000000572104025905000000006916000051800000000069160000599900000000691608015180

Results:
DepartmentFundLocAccountBudget PeriodAppvd BudgetAdj RevenueRecognized RevCollected Rev000000002810000059992016$0.00$20,000.00$0.00$0.00000000002810000059992016$20,000.00$20,000.00$0.00$0.00000000005721040254062016$680,000.00$680,000.00$0.00$0.00000000005721040259052016$0.00$0.00$1,925.00$1,700.00000000006916000051802016$0.00$0.00$28,006.00$0.00000000006916080151802016$2,110,000.00$2,110,000.00$201,449.91$115,426.86000000006916080151802016$0.00$2,110,000.00$201,449.91$115,426.86

In my results, Departments 28 and 69, both list data twice for the Adj Revenue, Rec Revenue and Coll Revenue. One row, it is combined with the approved budget, One row, it is not. Whereas, Dept 57, shows the combined results.

Out of 29 Departments in my results, 8 of the departments show the duplicate line of data and 21 departments are correct.

I also need to point out that I did this exact same union query with our expense approved budget vs. adjusted budget and the data has been confirmed...all are correct. At first, I copied what I did and made adjustments. Thinking that might have been my issue, then I went in a created the union query from select queries (basically started fresh) and both yielded the same issues.

I am completely stumped. I even left this for a day or so, came back and built it again from scratch...still issues.

I am hoping that someone can give me an idea to try or better yet, an answer. Thanks in advance.

Kim

Hi Kim, your details are completely unreadable (at least in Chrome). Maybe a picture or sample db would work better.

Looking at your sample data your duplicated Departments surprise me as well, but for the opposite reason as you. I think there should be more.

DWNLD_Essbase_Hyperion_Revenue Data has 3 Department=28 records and 4 Department=69 records. In DWNLD_OV_Revenue there are 1 and 2 more records, respectively.

I'd expect 4 Department=28 records and 6 Department=69 records in the UNION.

Something doesn't jive. I don't think you are showing us valid data or SQL.

I see my answer now. You used UNION instead of UNION ALL. That explains why you have what you have in the UNION.

Can you demonstrate what you expect to end with? Using the starting data you provided, can you illustrate the data you expect to end with?

Paul, I attached a PDF after I realized it posted weird; which is easier to read. Kim

Plog,
I am attaching my expected results in the attached, as I cannot figure out how to insert an excel table or pdf into the body of the message. Kim

First, let me admonish you for poor field names. You should only use alphanumeric characters and underscores in table and field names. Anything else (like spaces) and it makes coding that much more difficult.

Then I have a few notes:

1. Wheres Adj Revenue suppose to come from? You have no field named it in the data sources and the data you have could mean it could come from Appv_Budget or Revenue Estimate. I used Revenue Estimate in my code below.

2. You have 2016 for the Budget Period in expected results, but that data is not in the sources. I hard coded it, but you really should have it be discernable from the data.

Onto your code. First I would put the UNION in its own sub-query and I would do it correctly. You were hoping fields lined up with each other in your UNION. I ensured that. I did that by explicitly making each field appear in the same position in each SELECT clause and I reconciled the names so they exactly matched. Your data sources' fields don't have the exact same names for the same fields (e.g. Department/Dept). This is the sub-query code:


Save that query with the name 'RevenueReport_sub1'. Then build your final query on it:

Plog, Thank you for the information however, either I was not clear or there is a misinterpretation of data. I will review your information to see if it will be helpful. Please note the following however.

DWNLD_Essbase_Hyperion_Revenue ONLY holds the Approved Budget, Dept, Fund, Loc and Account.

DWNLD_OV_Revenue ONLY holds the Revenue Estimate (or Adjusted Budget), Dept, Fund, Loc and Account. This table also holds the Budget Period, Recognized Revenue and Collected Revenue.

The ONLY fields that are in BOTH tables are Dept, Fund, Loc and Account, thus the reason my union query is only for those fields. Once I determine all potential combinations with no duplicates (thus only using UNION, not UNION ALL), then I want to use that data to pull the Approved Budget from one table and the Adjusted Budget, Recognized Revenue and Collected Revenue from the other table.

My issue is that on the final data, there are some rows of data, where duplicates are pulling; whereas, duplicates are not pulling on other rows of data.

Kim