distinct query

[slimjen]

all, I am trying not to get duplicates on the trackingnumber in this query and it's not working. When I run the query; it tells me it's too complicated. I guess it's trying to do all the fields which it cant I just don't want dups on the trackingnumber

SELECT DIST tblSP_SHIPMENT.idxTRACKINGID, tblSP_SHIPMENT.txtTRACKINGNUM, tblSP_INVOICE.txtINVOICENUM, tblSP_INVOICE.txtPAIDMONTH, tblSP_INVOICE.txtCARRIERID, tblSP_INVOICE.txtCARRIERACCTNUM, tblSP_INVOICE.txtCARRIERNAME, tblSP_SHIPMENT.dteSHIPDTE, tblSP_SHIPMENT.txtSHIPTYPE, IIf([txtSENDERCITY]="Rock Hill" Or [txtSENDERCITY]="CHARLOTTE","OUTBOUND","INBOUND") AS SHIPTYPE, tblSP_SHIPMENT.numTRANSCHG, tblSP_SHIPMENT.numFUELSURCHG, tblSP_SHIPMENT.numACCESCHG, tblSP_SHIPMENT.numOTHCHG, NZ([numTRANSCHG],0)+NZ([numFUELSURCHG],0)+NZ([numACCESCHG],0)+NZ([numOTHCHG],0) AS TOTALCHGS, tblSP_SHIPMENT.txtSALESORDNUM, tblSP_SHIPMENT.txtSENDERNAME, tblSP_SHIPMENT.txtSENDERCITY, tblSP_SHIPMENT.txtSENDERSTATE, tblSP_SHIPMENT.txtSENDERZIP, tblSP_SHIPMENT.txtRECIPNAME, tblSP_SHIPMENT.txtRECIPCITY, tblSP_SHIPMENT.txtRECIPSTATE, tblSP_SHIPMENT.txtRECIPZIP, tblSP_SHIPMENT.numWEIGHT, [TOTALCHGS]/[numWEIGHT] AS CostPerPound, tblSP_SHIPMENT.txtSERVTYPE, tblSP_SHIPMENT.txtEXPTYPE, tblSP_SHIPMENT.txtRESPEXPTYPE, tblSP_SHIPMENT.txtSENDERCTRYREG, tblSP_SHIPMENT.txtRECIPCTRYREG, qry_SP_ACCOUNTING.txtCOSTCENTER, qry_SP_ACCOUNTING.txtDIVISION, qry_SP_ACCOUNTING.txtACCTCODE, qry_SP_ACCOUNTING.numAMOUNT
FROM (tblSP_INVOICE INNER JOIN tblSP_SHIPMENT ON tblSP_INVOICE.txtINVOICENUM = tblSP_SHIPMENT.txtINVOICENUM) INNER JOIN qry_SP_ACCOUNTING ON tblSP_INVOICE.txtINVOICENUM = qry_SP_ACCOUNTING.txtINVOICENUM
WHERE (((tblSP_INVOICE.txtPAIDMONTH) Between [Forms]![frmMONTHPICK]![STARTMONTH] And [Forms]![frmMONTHPICK]![ENDMONTH]))
ORDER BY tblSP_SHIPMENT.idxTRACKINGID;

please help
Thanks

[June7]

It's not just because of the fields that makes it too complicated, it is the INNER JOINS on tables with one-to-many or many-to-many relationships.

The records are distinct, hence the duplication of the trackingnumber.

I doubt you can get what you want with a single query.

[slimjen]

ok. Any ideas will be grateful.
Thanks

[June7]

Can't suggest anything if I don't know what you want.

What output do you want? Why did you try to join these tables to begin with? Show sample of source data and sample of desired output. Provide database for analysis.

Options are a sequence of queries until you reach the arrangement you want and/or VBA code.