Duplicate records in Query Result

  • Thread starter Thread starter david.moore
  • Start date Start date
D

david.moore

This is the SQL script of an Access query I run. Each record is being
duplicated in the result.
Can anyone see from this script where I am going wrong?
My thanks for any suggestions.

SELECT InvBatchDetail.INV_BATCH_HEAD, InvBatchDetail.INVOICE_NO, Now()
AS Expr1, InvBatchDetail.Q_NUMBER, QNo.EXTERNALID1,
CustomerName.CUSTNAME, InvBatchDetail.TOTAL_VALUE,
InvBatchDetail.TOTAL_VAT, InvBatchDetail.INVOICE_VALUE, QNo.SALESPERSON

FROM (QNo INNER JOIN InvBatchDetail ON QNo.MAIN_QNO =
InvBatchDetail.Q_NUMBER) INNER JOIN CustomerName ON QNo.EXTERNALID1 =
CustomerName.CUSTNMBR

WHERE (((InvBatchDetail.INV_BATCH_HEAD)=1999))

ORDER BY InvBatchDetail.INV_BATCH_HEAD, InvBatchDetail.INVOICE_NO;
 
This is the SQL script of an Access query I run. Each record is being
duplicated in the result.
Can anyone see from this script where I am going wrong?
My thanks for any suggestions.

SELECT InvBatchDetail.INV_BATCH_HEAD, InvBatchDetail.INVOICE_NO, Now()
AS Expr1, InvBatchDetail.Q_NUMBER, QNo.EXTERNALID1,
CustomerName.CUSTNAME, InvBatchDetail.TOTAL_VALUE,
InvBatchDetail.TOTAL_VAT, InvBatchDetail.INVOICE_VALUE, QNo.SALESPERSON

FROM (QNo INNER JOIN InvBatchDetail ON QNo.MAIN_QNO =
InvBatchDetail.Q_NUMBER) INNER JOIN CustomerName ON QNo.EXTERNALID1 =
CustomerName.CUSTNMBR

WHERE (((InvBatchDetail.INV_BATCH_HEAD)=1999))

ORDER BY InvBatchDetail.INV_BATCH_HEAD, InvBatchDetail.INVOICE_NO;
Click to expand...

Either there are two records for each value of MAIN_QNO in table QNO,
or there are two records for each CUSTNMBR in CustomerName, or you
have duplicate InvBatchDetail records. Try temporarily removing each
of these tables in turn from your query to see which, and then check
them for duplicate records.

John W. Vinson[MVP]
 
Thank you John.
There were indeed duplicated records for CUSTNMBR in CustomerName.
Data had been imported twice from a text file.
Thanks again for telling me where to look.
 
Thank you John.
There were indeed duplicated records for CUSTNMBR in CustomerName.
Data had been imported twice from a text file.
Click to expand...

Sounds like you need to put a unique Index on CUSTNMBR!

John W. Vinson[MVP]
 
Back
Top