Repeated records in file output

[bodmin]

Hello,

I am trying to use a loop to output a recordset to a text file, the data output is processed as it is output and formatted correctly. The output seems to include 8 lines of output for each record, though all I want is a single line for each of these records.

Can anyone shed any light on why I get the repeated records? The sql string used to generate the recordset is shown below.

"SELECT Cheque.Cheque_No, Cheque.ProducedDate, Cheque.Amount, Cheque.CreditAccount, Debits.DebitAccount, Debits.Amount, Attributes.Analysis_Code, Attributes.Analysis_Attribute FROM (Cheque LEFT JOIN Debits ON Cheque.Cheque_No=Debits.Cheque_No) LEFT JOIN Attributes ON (Debits.Debit_ID=Attributes.Debit_ID) WHERE (((((Cheque.Cheque_No)>=" & fromChequenum & " And (Cheque.Cheque_No)<=" & toChequenum & ") And (Cheque.Cancelled)= False) And (Cheque.Transferred)= False)) ORDER BY Cheque.Cheque_No, Debits.Debit_ID;"

cheers any help

 

[KenReay]

Hi

It is not possible to comment if the SQL string is tha cause without having more information about the nature of the data, however why not run the query in isolation and see if lines are repeated of not?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mordja]

bodmin,

If you are having duplicate records it is becuase there is more than one instance of the join field on one side of the join. You could just use SELECT DISTINCT blah blah
to "fix" the problem or you could look at your data.

Mordja

 

[bodmin]

Hey thanks for the help

I have tried running the query on its won and it does generate duplicate records, I am now thinking that it may require a sub-query to get the desired functionality. Perhaps you can help if I explain what I am after. I need to collect all details for every cheque but then only debit details that exist for that cheque and then only analysis details that exist for those debits. I thought that this could be achieved by using the relationship structure that I have implemented but like discussed it appears to be outputting multiple copies of the information.

Any ideas??
Cheers
Andi