Listing value only one time

[mokeehi]

I have this query where I want the amount in field tblMods.ModifiedLaborDollars to list only once.

SELECT tblDeloitteInvoiceDetail.WorkAuthorizationNumber, tblDeloitteInvoiceDetail.DeloitteInvoiceNum, tblDeloitteInvoiceDetail.AmountApproved, tblDeloitteInvoiceDetail.CLIN, tblMods.ModifiedLaborDollars
FROM tblMods INNER JOIN tblDeloitteInvoiceDetail ON tblMods.WorkAuthorizationNumber=tblDeloitteInvoiceDetail.WorkAuthorizationNumber
GROUP BY tblDeloitteInvoiceDetail.WorkAuthorizationNumber, tblDeloitteInvoiceDetail.DeloitteInvoiceNum, tblDeloitteInvoiceDetail.AmountApproved, tblDeloitteInvoiceDetail.CLIN, tblMods.ModifiedLaborDollars
HAVING (((tblDeloitteInvoiceDetail.WorkAuthorizationNumber)="wa4"))
ORDER BY tblDeloitteInvoiceDetail.CLIN;

What to do?

 

[dhookom]

Can we ask "why" you want to perform this un-orthodox query output?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mokeehi]

Managing a government contract. The bean counters have been using excel to track invoices and the associated work orders (WO). There are many invoices against one work order. And the invoices can also be billed against more than one WO. I want to be able to query the invoice information against the WO, total the invoices against the WO and compare to the WO original order amount.

I have a table for the work order information (amounts, beginning/end dates, etc.), one for the invoices submitted (total amount, date submitted etc.) and one for the details on the invoice (work order number, etc.) The invoice tables are related by invoice number. The primary key in the invoice table is the invoice number.

 

[dhookom]

Create a query that groups by the WO Original order amount and sums the invoices.

Are you attempting to use this query as the record source for a report? Is that what is causing you to ask this question?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mokeehi]

yes

 

[dhookom]

If you issue is in the report then format a question about your report. Your query is probably fine.

Tell us about your report and what isn't correct with it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]