Union Query for two queries from the same table

[WCStarks]

I am attempting to do a Union Query to combine two sibling queries into a single query. Querying online, I found there is such a thing as a Union Query. Union Queries seem rather simple, but it is not working for me.

I need to retrieve all Credits from the Mem_Trans table and a sub-set of Debits from the same table. I need to end up with columns for [Trans Date], Credits and Debits. When I run the Union Query, only the Credits come across. Can Union Queries not work with the same table? Here is what I have in my Union Query. Each sub-query has the same number of columns, even though the Credits query doesn't need the [Member ID]. Why does the second query not run?

SELECT Mem_Trans.Credit, Mem_Trans.[Trans Date], Mem_Trans.[Member ID]
FROM Mem_Trans
WHERE (((Mem_Trans.Credit)<>IsNull([Credit])))

Union

SELECT Mem_Trans.Debit, Mem_Trans.[Trans Date], Mem_Trans.[Member ID]
FROM Mem_Trans
WHERE (((Mem_Trans.Debit)<>IsNull([Debit])) AND ((Mem_Trans.[Member ID])=34));

[pbaldy]

Try

SELECT 0 As Debit, Mem_Trans.Credit, Mem_Trans.[Trans Date], Mem_Trans.[Member ID]
FROM Mem_Trans
WHERE Mem_Trans.Credit Is Not Null

Union

SELECT Mem_Trans.Debit, 0 As Credit, Mem_Trans.[Trans Date], Mem_Trans.[Member ID]
FROM Mem_Trans
WHERE Mem_Trans.Debit Is Not Null AND Mem_Trans.[Member ID]=34

[WCStarks]

That works! Thanks. I wonder why MS does not talk about this when they show us how to do Union Queries?

[pbaldy]

Happy to help! Not sure but I suppose they can't cover every use.

[WCStarks]

OK. To clarify: Place holders in each query are only needed when the two queries are drawing data from the same table. In the MS help example, the two queries were drawing data from different tables. Apparently, in this case, no place holders from the other query are needed. Does this correctly clarify the rules?

This being the case, it seems that this is an important detail MS should have included in their explanation of using Union Queries.

[pbaldy]

No, placeholders are needed when you want to return different fields from each query (in your case one had debits and one had credits, you wanted them returned as separate fields). Normally a union query is pulling from different tables, so that's probably why their example did. I'm not even sure you need it. Does this work?

SELECT Mem_Trans.Debit, Mem_Trans.Credit, Mem_Trans.[Trans Date], Mem_Trans.[Member ID]
FROM Mem_Trans
WHERE (Mem_Trans.Debit Is Not Null AND Mem_Trans.[Member ID]=34) OR Mem_Trans.Credit Is Not Null

[WCStarks]

I tried your suggested query, since I could recall if I had not already tried that syntax. It does not work correctly. It includes all debits for all members, not just for the SCATeam "member" record.

My criteria is to return only those debits linked to the SCATeam financial transactions, ignoring debits from the remainder of the member records. The debits for the other member records is used only as a "flag" showing what the team member owes the team. When the team member pays that amount, he gets a receipt and it gets recorded as a credit against the debit, essentially cancelling out the debit. So, all the credits need to be returned as income to the SCATeam, but only the debits belonging to the SCATeam should be returned. If not, the "flag" debits in the member records, would cancel out that portion of the credits as income to the team.

I had tried several different ways to get the result I needed, but could not get it. I toyed with the idea of creating a temp table and appending each query to it. That seemed overly involved, requiring a macro to run a sequence of queries, so I searched online for a better solution and found the Union Query at the top of the list.

I started the db a year ago with the single purpose of tracking financial transactions between the team and its members. It did not initially include tracking all of the team transactions. Since then, I have expanded the db to include tracking all financial transactions, membership, organization, events, assets, communication sites and EOCs for the various cities in the county. The SCATeam (a volunteer group of Amateur Radio Operators) is under the Emergency Services division of the County Sheriff's Office, like Search And Rescue, except our team manages emergency communications for the the SO.

FYI: As you know from recent postings you have helped with, the db has multiple tabs under the Members Tab, along with the other tabs at the Members level. When I expanded what the db included, I didn't know how to have sub-tabs under forms. I found the Access feature of Navigation Subforms, which seemed to be just what I needed. Unfortunately, I hadn't yet discovered the Tab Controls, which would also have provided the form structures I needed. So I blindly developed with the Navigation Subforms, which would have been better done with Tab Controls. But now, I do not know how to migrate away from the Navigation Subfoms.

[isladogs]

I agree with Paul.
The single query in post #6 should be functionally the same as what you were showing with a union query in post #1.
Perhaps you need to review the filter was applied correctly or review what you had originally

[WCStarks]

Well, I don't see how. I copied and pasted the above SQL into a query, adding the missing ";" and ran it, and it did not eliminate the member debits. I just now recreated the query, using the above SQL and it still includes the member debits. I guess I should indicate that the debit and credit fields are in the same transaction record. Please read my description of the use of Debit in this case.

[WCStarks]

Here is an image showing the first page of the result of the above single query. I needed a way to track what members owed and when, and when that amount owed was paid. As I do it now, I enter a debit to indicate an amount owned by the member, then when he pays, I update the same transaction record with the credit and date of the credit, which shows the debt has be paid. I admit this is an unusual way of using the debit field. If I were to put the debit and credit in their own transaction records, then the above single query method would likely work, but not as it is now. As I am doing it now, I can see at a glance when a particular amount owed has been paid. Perhaps I should revisit this use of debit in the future. An accountant would probably cringe.

The transactions for the SCATeam itself, is done in the more traditional way, with separate credit and debit transactions.


As it is done now, the Union query does fulfill the objective.

[isladogs]

I'm not an accountant but I have to admit that I'm indeed 'cringing' at the way you record your credit / debit pairs.
Also surely there are times where the entire debit isn't paid in a single payment.

[accesstos]

I thing that debits and credits should be keeped in separate tables, but,
if you like to keep them in the same record, i propose to keep in separate field the date of their transactions.

in addition...

the criteria in the second query must be changed to:

WHERE (Mem_Trans.Debit Is Not Null AND Mem_Trans.[Member ID]=34) 
OR (Mem_Trans.Credit Is Not Null AND Mem_Trans.[Member ID]=34)

while the first UNION query might have the following syntax:

SELECT  'Credit' AS [Trans Type], Mem_Trans.Credit AS [Amount], Mem_Trans.[Trans Date], Mem_Trans.[Member ID]
FROM Mem_Trans
WHERE Mem_Trans.Credit Is Not Null

Union

SELECT 'Debit' AS [Trans Type], Mem_Trans.Debit AS [Amount], Mem_Trans.[Trans Date], Mem_Trans.[Member ID]
FROM Mem_Trans
WHERE Mem_Trans.Debit Is Not Null AND Mem_Trans.[Member ID]=34