How do I ignore duplicates?

G

Guest

I am running a query to balance our credits and invoices for the month.
Although a record could have several lines with different quantities and
amounts, I also have to track the amount of the original invoice. The
TotalOriginal field is entered just once with the main table for the record.

Unfortunately, my query has to look at all line items in the subform to
total those amounts and it is causing redundant data for the TotalOriginal
field. Is there an easy way (either in the query or report) to only sum that
field only one time for each record number (called TransactionID)?

Please see my pasted SQL from the query for more detail. Any advice would
be qreatly appreciated.

SELECT tblTransactionRequest.TransactionID, tblTransactionRequest.Division,
tblTransactionRequest.Product, tblTransactionRequest.Requestor,
tblRequestor.Function, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.RequestDate
FROM (tblRequestor RIGHT JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) RIGHT JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID,
tblTransactionRequest.Division, tblTransactionRequest.Product,
tblTransactionRequest.Requestor, tblRequestor.Function,
tblTransactionRequest.ReasonCode, tblTransactionRequest.TotalOriginal,
tblInvoiceLines.Quantity, tblInvoiceLines.[Unit Price],
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest, tblTransactionRequest.RequestDate
HAVING (((tblTransactionRequest.Division)=220 Or
(tblTransactionRequest.Division)=510) AND ((tblRequestor.Function)=[Enter
Function]) AND ((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And [End
date of report]))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product;

Thanks,
Jim
 
G

George Nicholson

Is there an easy way (either in the query or report) to only sum that
field only one time for each record number (called TransactionID)?
Click to expand...

Sum it on a report? Sure.

One approach:
- Use the query you have (minus tblInvoiceLines) as the recordsource for the
parent report. This query should give you one record per TransactionID.
- Create a subreport for tblInvoiceLines. Place it within the DetailSection.
Use TransactionID to link it to parent report.


HTH,



Jim Johnson said:
I am running a query to balance our credits and invoices for the month.
Although a record could have several lines with different quantities and
amounts, I also have to track the amount of the original invoice. The
TotalOriginal field is entered just once with the main table for the
record.

Unfortunately, my query has to look at all line items in the subform to
total those amounts and it is causing redundant data for the TotalOriginal
field. Is there an easy way (either in the query or report) to only sum
that
field only one time for each record number (called TransactionID)?

Please see my pasted SQL from the query for more detail. Any advice would
be qreatly appreciated.

SELECT tblTransactionRequest.TransactionID,
tblTransactionRequest.Division,
tblTransactionRequest.Product, tblTransactionRequest.Requestor,
tblRequestor.Function, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest,
tblTransactionRequest.RequestDate
FROM (tblRequestor RIGHT JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) RIGHT JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID,
tblTransactionRequest.Division, tblTransactionRequest.Product,
tblTransactionRequest.Requestor, tblRequestor.Function,
tblTransactionRequest.ReasonCode, tblTransactionRequest.TotalOriginal,
tblInvoiceLines.Quantity, tblInvoiceLines.[Unit Price],
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest,
tblTransactionRequest.RequestDate
HAVING (((tblTransactionRequest.Division)=220 Or
(tblTransactionRequest.Division)=510) AND ((tblRequestor.Function)=[Enter
Function]) AND ((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And
[End
date of report]))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product;

Thanks,
Jim
Click to expand...
 
G

Guest

Hi George,

Thank you for responding. Unfortunately, your solution didn't work for me.
Because of the design constraints by my department and the way they want the
data displayed, a subreport will not help.

The existing report works but counts the TotalOriginal too many times when
totalling the values. I tried selecting "hide duplicates" and I have tried
manipulating both the query and the report with no success.

If [TotalOriginal] is a single value on the main table but I need to
calculate values [Quantity] and [Unit Price] from the InvoiceLines table,
isn't there a way to only add the values of [TotalOriginal] once for eac
record?

Please advise,
Jim

George Nicholson said:
Is there an easy way (either in the query or report) to only sum that
field only one time for each record number (called TransactionID)?
Click to expand...

Sum it on a report? Sure.

One approach:
- Use the query you have (minus tblInvoiceLines) as the recordsource for the
parent report. This query should give you one record per TransactionID.
- Create a subreport for tblInvoiceLines. Place it within the DetailSection.
Use TransactionID to link it to parent report.


HTH,



Jim Johnson said:
I am running a query to balance our credits and invoices for the month.
Although a record could have several lines with different quantities and
amounts, I also have to track the amount of the original invoice. The
TotalOriginal field is entered just once with the main table for the
record.

Unfortunately, my query has to look at all line items in the subform to
total those amounts and it is causing redundant data for the TotalOriginal
field. Is there an easy way (either in the query or report) to only sum
that
field only one time for each record number (called TransactionID)?

Please see my pasted SQL from the query for more detail. Any advice would
be qreatly appreciated.

SELECT tblTransactionRequest.TransactionID,
tblTransactionRequest.Division,
tblTransactionRequest.Product, tblTransactionRequest.Requestor,
tblRequestor.Function, tblTransactionRequest.ReasonCode,
tblTransactionRequest.TotalOriginal, tblInvoiceLines.Quantity,
tblInvoiceLines.[Unit Price], tblTransactionRequest.InvoiceRequest,
tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest,
tblTransactionRequest.RequestDate
FROM (tblRequestor RIGHT JOIN tblTransactionRequest ON
tblRequestor.RequestorID = tblTransactionRequest.Requestor) RIGHT JOIN
tblInvoiceLines ON tblTransactionRequest.TransactionID =
tblInvoiceLines.TransactionID
GROUP BY tblTransactionRequest.TransactionID,
tblTransactionRequest.Division, tblTransactionRequest.Product,
tblTransactionRequest.Requestor, tblRequestor.Function,
tblTransactionRequest.ReasonCode, tblTransactionRequest.TotalOriginal,
tblInvoiceLines.Quantity, tblInvoiceLines.[Unit Price],
tblTransactionRequest.InvoiceRequest, tblTransactionRequest.CreditRequest,
tblTransactionRequest.ForcedCreditRequest,
tblTransactionRequest.RequestDate
HAVING (((tblTransactionRequest.Division)=220 Or
(tblTransactionRequest.Division)=510) AND ((tblRequestor.Function)=[Enter
Function]) AND ((tblTransactionRequest.ReasonCode)<>"Manual Invoice") AND
((tblTransactionRequest.RequestDate) Between [Start date of report] And
[End
date of report]))
ORDER BY tblTransactionRequest.Division, tblTransactionRequest.Product;

Thanks,
Jim
Click to expand...
Click to expand...
 
Top