Limit for Access query is 255 fields and 1GB of data. IIRC, 2GB limit does not apply to queries, and where that limit applies, it includes overhead (thus also bloat).
Sql examples 1,2 and 4 look like Cartesian queries which would most certainly drive you over the 1GB limit with 800K+ records and so many fields. In the last case maybe UNION ALL isn't helping either, as opposed to just UNION. Is this Access sql, because the JOIN statement looks wrong to me? Another issue you may have introduced by splitting is now having too many joins and/or indexes for the new queries.
I'm with anyone who suspects the db is poorly designed, and if you are experienced (the fact that you're contracted would suggest that) I wonder why you would even take this on, unless it is not as bad as we suspect.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.