Union Query Dropping Data Issue

[jaymoore1756]

I have a report that is using a union within the report. The queries use data from the same two files - The equipment master and the GL transaction file. Q1 gets standard cost data --- standard rate, actual hours
and standard cost (rate x hours) charged to each equipment. Q2 gets the actual cost data charged to the each piece of equipment. The joins in framework are different for each query and setup in framework manager.
Q1(standard) and Q2 (Actual) both balance to the GL for all months and YTD

The Q3(union report) calculates an actual cost and gets a cost and a rate variance. These variances would be used to adjust the standard journal entry each month.

My issue is the union query Q3 is dropping actual data that is being reported in Q2.  When I run the report for any given time period (day, week month(s)). Some equipment types balance some do not.

Any thoughts or comments would be appreciated.


Modify message

[wyconian]

Hi

Why are you using a union query?  A depending on how you've set it up the union would give you a distinct list of records based on every column which may be why you're loosing data.

Have you tried just a join query?  It sounds like you should be able to joi the queries on equipement and date.  Depending on your data you may need to add an outer join if for example you have equipment with actuals but not standard values etc.

Hope that helps :-)

[blom0344]

There is a setting that allows for keeping duplicates (aka UNION ALL) and removing them (UNION) Check this..

[jaymoore1756]

"There is a setting that allows for keeping duplicates (aka UNION ALL) and removing them (UNION) Check this.."

It is set to preserve ---

[jaymoore1756]

This is resolved.

[MFGF]

What was the fix? It may help others later on if you can describe what you did to make it work?

Cheers!

MF.

[jaymoore1756]

Here is the fix -- I changed the setting that allows for keeping duplicates (aka UNION ALL) to preserve, That did not solve the issue. I was not using all the fields in the query, so I added all them back in. When I did this the missing data returned. Management did want to report this data on the report do I hid non - required fields. Thanks for your help.

[MFGF]

Thanks! Much appreciated! :)

MF.