I need to create a Recordset of date from two tables.

The first table is called tblCheques and has the following fields:
ChequesID(PrimaryKey), LoanID (Foreign Key), Date, Cheque Amount, and Cheque
No.

The second table is called tblPayments and has the following fields
PaymentsID (Primary Key), LoanID (Foreign Key), Date, Payment Amount and
Receipt Number.

LoanID is the primary key of a table called tblLoanDetails which is linked
to each of the other two tables by a one to many relationship. (One loan,
many payments or many Cheques)

I want to join the two tables together so that in the resulting query I have
four columns.  LoanID, Date, Payment Amount, Cheque Amount.

The query cannot have duplicate records. If Payments contains 15 records,
and Cheques contains 20 records, the resulting query should have 35 records,
no more no less.

I have succeeded in producing this dynaset by doing two separate queries
with outer joins (1 query with a left join, the other with a right join) on
the date fields of the two tables tblPayments and tblCheques.  I then do a
UNION query and with some tweaking, get the result I want.

This is too complicated.  What I really need is a neat SQL statement that I
can use in a RECORDSET statement.

Can anyone help please?

Hi Robin,

I don't known if I fully understodd your problem, I was here making
some samples Tables, to make the correct SQL, but I've found something
that is disturbing me, is there any relation between Payments and
cheques?

Is that a one to one relation? how are they related by the date field?

But if this somehow near the true, how can you get 35 records ?

I'm on a lost here, please give me some more detail maybe we can help
you out better...

On Mon, 9 Apr 2001 23:15:14 -0400, "Robin Young"

---------
Pedro Gil
http://www.geocities.com/pmpg98_pt

DDPI
http://www.users.bigpond.com/papwalker/DDPI.HTML
https://sourceforge.net/projects/ddpi/

"SELECT LoanID, [Date], expr AS [Payment Amount], [Cheque Amount] FROM
tblCheques UNION ALL SELECT LoanID, [Date], [Payment Amount], expr AS
[Cheque Amount] FROM tblPayments"

You never specified how you want to show Payment Amount for cheque
payments or vice versa, so expr should be replaced with whatever
expression you require

I have so far come up with a complex SQL statement that does more or less
what I want, but it seems too complicated for my tastes.  I want a simpler
SQL statement if possible, Paul, yours looks promising, but I don't
understand what the expr expression would be.

I basically want to show Cheques in one column and Payments in another
column.  These amounts ideally should have a separate row or register.  For
example, for each row that shows a cheque amount, the payment amount should
be Null or zero and vice versa.  If by coincidence (highly unlikely in the
real world though not impossible) that a payment and a cheque share the same
LoanID and Date values, then they could appear in the same row without
causing breaking business rules.  But what I tend to get is the repeated
Cheque values for each Payment value o vice versa. This clearly breaks
business  rules.  For example, if only one cheque payment was made for
$50,000 for LoanID = 1999-174, I can't have the value repeated in the Cheque
Amount column for every Payment made for the same LoanID.  It must appear
once and only once.

Basically I am trying to create a RECORDSET and need a neat SQL statement to
get the right date:  all the cheques paid out and all money received for all
the loans and with a date field.  I then want to filter each Record in the
Recordset by LoanID, order by Date, and do some calculations for each LoanID
such as total paid in, total paid out, interest accumulated etc.

I have some work ahead of me manipulating RECORDSETS, date calculations,
interest cal;culations, making dynamic tables to feed subforms and reports.
So unless I get a robust SQL statement in my rst.open statement that
guarantees the correct information everytime, I am stuck.

The problem must be my confusion on basic Database theory.  I know the
simplest solution would be to do away with two tables and just have one
tblTransactions with all the Cheque and Payment data.  It goes against
normalization rules I suppose with lots of NULL values in the secondary
fields used for Cheque numbers, Receipt Numbers, Paid in By etc.

So far I have the following ( which doesn't let me put in a WHERE loanID =
"lngCurrentLoanID" clause to filter it.

The complicated SQL I have so far  goes something like:

SELECT tblCheques_1.LoanID, tblCheques.ChequeAmount, tblPayments.PaymentDate
AS Fecha, tblPayments.PaymentAmount
FROM (tblCheques RIGHT JOIN tblPayments ON tblCheques.ChequeDate =
tblPayments.PaymentDate) INNER JOIN tblCheques AS tblCheques_1 ON
tblPayments.LoanID = tblCheques_1.LoanID

UNION SELECT tblCheques.LoanID, tblCheques.ChequeAmount,
tblCheques.ChequeDate, tblPayments.PaymentAmount
FROM tblCheques LEFT JOIN tblPayments ON tblCheques.ChequeDate =
tblPayments.PaymentDate;

Thanks in advance

Robin.

Thanks friends, I have got it working  now.  Well almost

The code (original in Spanish) is:
rst.Open _
Source:="SELECT PresID, DesemFecha AS Fecha,0 AS Pago, DesemMonto " & _
" FROM tblPresDesem" & _
" WHERE PresID = me.ctrlPresID " & _
" UNION ALL" & _
" SELECT PresID, PagoFecha AS Fecha, PagoMonto, 0 AS Desembolso" & _
" FROM tblPresPagos" & _
" WHERE PresID = me.ctrlPresID" , _  etc.

However, the Where clause isn't working, and I get a runtime error "3001".

Hi Robin,

You will have to put variable outside the string construction, in
order to get the values they have...

" WHERE PresID = " & me.ctrlPresID & " , _  etc."

beware of the fields type numeric no need for separators, text
enclosed in commas, date enclosed in # and american format mm-dd-yyyy

hth
On Tue, 10 Apr 2001 12:51:04 -0400, "Robin Young"

---------
Pedro Gil
http://www.geocities.com/pmpg98_pt

DDPI
http://www.users.bigpond.com/papwalker/DDPI.HTML
https://sourceforge.net/projects/ddpi/

Brilliant!
Thank you a million for sharing that with me. I have been trying to figure
it out all day, but was not even nearly close.  Now thanks to your help, I
can pass the current LoanID on my Main Loans form to filter the record set,
the rest just begins to fall into place.  I can get on with writting the
algorithms and get my calculations done.

Regards Robin