Union Query
Find Duplicates
Prevent Duplicates

Judging from the field names, do these involve different tables? It looks like it could just be one query potentially.

If not, you're probably looking at a Union Query

It's two different tables, unfortunately.

Union isn't working.  I can get the select query to work doing a "0 AS" for the missing fields in both queries but they won't union.

What do you mean they won't union? Are you getting an error or anything? You will need to fill in missing fields with 0 As SumSpent kind of thing

If nothing else you can just DLookup the SumSpent from the other query into it

Works:
SELECT CJI3SumByWOQ.WBSelement, CJI3SumByWOQ.Order, CJI3SumByWOQ.Description, 0 AS Plan, 0 AS Remaining, CJI3SumByWOQ.Spent
FROM CJI3SumByWOQ
ORDER BY CJI3SumByWOQ.Order;
Works:
SELECT CJI5SumByWOQ.WBSelement, CJI5SumByWOQ.Order, CJI5SumByWOQ.Description, CJI5SumByWOQ.Plan, CJI5SumByWOQ.Remaining, 0 AS Spent
FROM CJI5SumByWOQ
ORDER BY CJI5SumByWOQ.Order;

I get "Syntax error in ORDER BY clause." error if I union them.  I get that error with or without the "order by" SQL above.

Make 2 queries that have exactly the same field names and incorporate your 0 values. Do not include any ordering.

Make your UNION query. No ordering.

Make another query based on your UNION query that handles the ordering.

Could be a classic example of just needing to break it out into multiple steps. Also make sure all of your field data types are the same.

Remove the Query names.

SELECT WBSelement, Order, Description, 0 AS Plan, 0 AS Remaining, Spent
FROM CJI3SumByWOQ
ORDER BY Order

UNION

SELECT WBSelement, Order, Description, Plan, Remaining, 0 AS Spent
FROM CJI5SumByWOQ
ORDER BY Order

The field Order may also be causing a problem. If so, you may need to alias it.

It isn't working.  Could the field name "order" be throwing it off?  

The initial CJI3 table is a linked excel table.  Could that be the issue?

Yes, and yes. I'd import the data into a local table. And definitely alias Order.

I changed "order" to "Workorder" and that fixed the error I was getting but then I had duplicate records.  It was showing two lines for everything that was in both tables.  So I created another aggregate query to sum them.  Works great!

Importing the table didn't help with the duplicates so I left it linked.

Thanks so much for your help!!!