dups

[slimjen]

All I am getting duplicate totals in this query. I am joining two tables and the totals are repeating for the invoice numbers. Please help.


Thanks

SELECT DISTINCT qry_SP_ACCOUNTING_CHRGS.dteDATAENTDTE, qry_SP_ACCOUNTING_CHRGS.txtACCTCODE, qry_SP_ACCOUNTING_CHRGS.txtDIVISION, qry_SP_ACCOUNTING_CHRGS.txtCARRIERID, qry_SP_ACCOUNTING_CHRGS.txtCARRIERNAME, qry_SP_ACCOUNTING_CHRGS.txtINVOICENUM, tblSP_SHIPMENT.txtTRACKINGNUM, tblSP_SHIPMENT.txtSENDERNAME, tblSP_SHIPMENT.txtRECIPNAME, tblSP_SHIPMENT.txtRESPEXPTYPE, qry_SP_ACCOUNTING_CHRGS.numAMOUNT
FROM qry_SP_ACCOUNTING_CHRGS INNER JOIN tblSP_SHIPMENT ON qry_SP_ACCOUNTING_CHRGS.txtINVOICENUM = tblSP_SHIPMENT.txtINVOICENUM
WHERE (((qry_SP_ACCOUNTING_CHRGS.dteDATAENTDTE) Between [Enter Start Date] And [Enter End Date]));

[June7]

You are joining a table to a query and not showing sql for the query. There must be a many-to-one or many relationship here. I would have to work with data to understand more.

[slimjen]

What do you need? copy of the database??

[June7]

That would be one way to provide data. Don't need real data, dummy records will do, but enough to test with.

Make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post. The Attachment Manager is below the Advanced post editor.

[slimjen]

see attached. the query I'm having the problem with is the recharges reconciliation file. I'm getting dups which is inflating my total. Thanks you for the help.

[June7]

The issue is that one invoice can have many tracking numbers. When you join the many to the one and show numAmount from the first query it will be repeated for each of the tracking numbers associated with same invoice. The records are not duplicates because the tracking number changes.

What is it you want this query to do? At what level do you need the data summarized? I.e., why do you need the tracking number in this query? It is the only field from the shipment table. Why is numTRANSCHG not included in the query?

I think you need to build a report with Grouping & Sorting and summary calcs and not try to do the summary (aggregate) calcs in query. That's what reports are for. Allow you to present aggregate data as well as detail info. Might requre a report/subreport if you do need the tracking number detail.

[slimjen]

I need the amount from the accounting (numAmount)side plus the tracking number associated with the record. The query for SP accounting returns the total amount needed about 86000 and the qryShipment also has the amount of 86000 by itself. But when I combine them to get the tracking number; it seems to return dups. I need the tracking # from the shipment side that cooresponds with the accounting record. I hope this make sense. Would a union query give me the results I need and how. I've been playing around with it but can't get it to work.
ie: invoice number, acct code, division, carriername, amount from acct and tracking number from shipment. Thanks

[June7]

No, you cannot do this in a single, simple SELECT (without or without GROUP BY) query. The query is actually working correctly because of the nature of data, it is just not appropriate for aggregate calcs. Reread my edited previous post. If you don't do a grand total on numAmount, build report that has Group section for AcctCode (also groups for Division, InvoiceNum if desired) and TrackingNum would be in Detail section. Put numAmount field in the AcctCode Group header.

If you want a grand total, have two options:
1. DSum function in a textbox of report footer.
2. Exclude Shipment from the queries and use a subreport for the TrackingNum data.

UNION is not pertinent to this.

[slimjen]

I am so lost? Can you give me an example? I'll try creating a report base on the query without the shipment and adding the tracking num on the detail section of the report. Is this what you're talking about? Thanks

[June7]

That would be option 2 of my suggestion.

After looking at data again, don't think option one would work. Including the Shipment table in the query just is not working.