Advice on Union SQL and Join

[PNGBill]

I am replacing a "Bundle" of queries, make tables etc with what may well be one sql.

Here is my basic Union sql

SELECT T.LDPK, T.TRNACTDTE, T.TRNTYP, T.TRNDR, T.TRNPR, 0 AS PaymentAmt
FROM TBLTRANS AS T
UNION ALL SELECT P.LoanID AS LDPK, "01/01/2010" AS TRNACTDTE, P.PayMethod AS TRNTYP, 0 AS TRNDR, 0 AS TRNPR, P.PaymentAmt
FROM tblMemberRepayments AS P;

This "Works" except that I have hard coded "01/01/2010" where it should be the actual date from another table. Require ADPK from TBLLOAN.LDPK and BanklID & StatementDate from tblBankStatements.

Here is the sql that gathers this info.

SELECT TBLLOAN.ADPK, tblBankStatements.StatementDate, tblBankStatements.BankID, tblMemberRepayments.LoanID, tblMemberRepayments.PayMethod, tblMemberRepayments.PaymentAmt
FROM TBLLOAN INNER JOIN (tblBankStatements INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID) ON TBLLOAN.LDPK = tblMemberRepayments.LoanID;

And here is the other side of the Union Query with all data

SELECT TBLTRANS.TRNACTDTE, TBLTRANS.LDPK, TBLLOAN.ADPK, TBLTRANS.TRNTYP, TBLTRANS.TRNDR, TBLTRANS.TRNPR
FROM TBLLOAN INNER JOIN TBLTRANS ON TBLLOAN.LDPK = TBLTRANS.LDPK;

Can I combine the two sqls into the union query to give me all the records with the info I need? or
Do I need to do 3 sql's two to collect the relevant data and the 3rd, the union query?

or should I use some temp tables and macros

 

[PNGBill]

Here is the finished Union Query using the two sqls to collect the data.

SELECT T.ADPK, T.LDPK, T.TRNACTDTE, T.TRNTYP, T.TRNDR, T.TRNPR, T.TRNCR
FROM QueryTest2 AS T
UNION ALL SELECT P.ADPK, P.LDPK, P.TRNACTDTE, P.TRNTYP, P.TRNDR, P.TRNPR, P.TRNCR
FROM QueryTest3 AS P;

SELECT tblBankStatements.StatementDate AS TRNACTDTE, TBLLOAN.ADPK, tblMemberRepayments.LoanID AS LDPK, [BankID] & ", " & [PayMethod] AS TRNTYP, 0 AS TRNDR, 0 AS TRNPR, tblMemberRepayments.PaymentAmt AS TRNCR
FROM TBLLOAN INNER JOIN (tblBankStatements INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID) ON TBLLOAN.LDPK = tblMemberRepayments.LoanID;

SELECT TBLTRANS.TRNACTDTE, TBLLOAN.ADPK, TBLTRANS.LDPK, TBLTRANS.TRNTYP, TBLTRANS.TRNDR, TBLTRANS.TRNPR, 0 AS TRNCR
FROM TBLLOAN INNER JOIN TBLTRANS ON TBLLOAN.LDPK = TBLTRANS.LDPK;

How would I go about incorporating QueryTes2 and QueryTest3into the union query so only one sql is used??

Appreciate any guidance.

 

[namliam]

I have not double checked the order but there is nothing wrong with a syntax like:

SELECT tblBankStatements.StatementDate AS TRNACTDTE, TBLLOAN.ADPK, tblMemberRepayments.LoanID AS LDPK, [BankID] & ", " & [PayMethod] AS TRNTYP, 0 AS TRNDR, 0 AS TRNPR, tblMemberRepayments.PaymentAmt AS TRNCR
FROM TBLLOAN 
INNER JOIN (tblBankStatements 
INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID) 
                               ON TBLLOAN.LDPK = tblMemberRepayments.LoanID
UNION ALL
SELECT TBLTRANS.TRNACTDTE, TBLLOAN.ADPK, TBLTRANS.LDPK, TBLTRANS.TRNTYP, TBLTRANS.TRNDR, TBLTRANS.TRNPR, 0 AS TRNCR
FROM TBLLOAN 
INNER JOIN TBLTRANS ON TBLLOAN.LDPK = TBLTRANS.LDPK;

 

[PNGBill]

Thanks Mailman, I will have a play with this soon.

Have proceeded on with my 3 sql version - same data result - and setting up a report.

When I get the sql down to one it should just slot in as the record source of the report. - easy said