Solved Fixing issues with UNION SQL

[dawsonrhodes]

Hey all,

I'm having an issue where records are combining into one row if the PatronID matches, as it is normalized to do so. There are two source tables, tblReceiptofFunds and tblMultipleTransactionLogs combine in the rows, which makes some other functions I have like combining the ID's for a consolidated TransactionID not work, as it will take an id, [RoFID] 1 and [MTLID] 4 in the same row, which makes it total 5, where it should be MTLID - Null and RoFID 1, which totals 1, as each row would only either contain the RoF table information, or the MTL table information.

Anyway to achieve this?

 

[Gasman]

A union SQL will only create extra records, so not sure how you are combining the IDs unless you are using some sort of concatenate function?

 

[The_Doc_Man]

Your description is hard to follow. Can you create some exhibits so we can SEE the problem? Provide a few rows of each source table, a few rows of what you see, and a few rows of what you wanted to see. Plus the SQL you are using that gives you the "wrong" result.

 

[dawsonrhodes]

A union SQL will only create extra records, so not sure how you are combining the IDs unless you are using some sort of concatenate function?

Hey gasman, that is exactly how it's done now.

I think I've found the issue but unsure how to solve it.

The Union Query is pulling from 2 JOIN queries, I don't know why I did this, it was the only way I knew how, so basically, the records are combined, MTL and ROF tables, and a total query for the dollar amounts, each query uses a patron ID to link up.

I'm sure what I'm explaining doesn't make a ton of sense, I'll post all SQL code below for the tables mentioned (it is messy as they reference other queries to help filter some of the data, like some date critiera.

' qryAllTansactionsGamingDayExtended '  
SELECT qryTransactionTotalCalculated.PatronID, qryAllTransactionsGamingDay.RoFID, qryAllTransactionsGamingDay.MTLID, qryAllTransactionsGamingDay.MTLTransactionLocation, qryAllTransactionsGamingDay.RoFTransactionLocation, qryTransactionTotalCalculated.AllTotalFormatted, [RoFID] & "" & [MTLID] AS TransactionID, [RoFDate] & "" & [MTLDate] AS TransactionDate, [RoFType] & "" & [MTLType] AS TransactionType, [RoFTransactionLocation] & "" & [MTLTransactionLocation] AS TransactionLocation
FROM qryTransactionTotalCalculated RIGHT JOIN qryAllTransactionsGamingDay ON qryTransactionTotalCalculated.PatronID = qryAllTransactionsGamingDay.qryMTLTransactionsGamingDay.PatronID
GROUP BY qryTransactionTotalCalculated.PatronID, qryAllTransactionsGamingDay.RoFID, qryAllTransactionsGamingDay.MTLID, qryAllTransactionsGamingDay.MTLTransactionLocation, qryAllTransactionsGamingDay.RoFTransactionLocation, qryTransactionTotalCalculated.AllTotalFormatted, [RoFID] & "" & [MTLID], [RoFDate] & "" & [MTLDate], [RoFType] & "" & [MTLType], [RoFTransactionLocation] & "" & [MTLTransactionLocation]
HAVING (((qryTransactionTotalCalculated.PatronID)>0));

 

[dawsonrhodes]

Your description is hard to follow. Can you create some exhibits so we can SEE the problem? Provide a few rows of each source table, a few rows of what you see, and a few rows of what you wanted to see. Plus the SQL you are using that gives you the "wrong" result.

The issue is the way I've made the join queries, I'm going to upload them as that is probably more logical

 

[dawsonrhodes]

Your description is hard to follow. Can you create some exhibits so we can SEE the problem? Provide a few rows of each source table, a few rows of what you see, and a few rows of what you wanted to see. Plus the SQL you are using that gives you the "wrong" result.

Dawsonrhodes.zip

drive.google.com

The website said there was an error, here you are!

 

[Gasman]

Version is to late for me to access, so I am out, sorry.