Avoid duplicated item when combining two queries

[Jill]

I am determining invoice totals for my firm. I developed a qry for current yr
invoice totals, a query for prior year invoice totals ... and then I combine
them in a query and it works beautifully.

Now, I want to have a few adjustments to the totals. The adjustments work
fine in the current year and prior year queries, but it is showing duplicates
in the combined query when I have a transaction for the same client in the
same engagement. I have tried different joins to no avail. Any suggestions?

See below:
Cltnum CltEng Comparison CurrentYrInvoiceAmount
PriorYrInvoiceAmount CurrentYrAdjustments PriorYrAdjustments
004426 00006 -32525 27380 59905
004426 00006 -59905 0 59905 16395

Help.

 

[Jeff Boyce]

You've shown us the output, but not the SQL of the query you are using.

Please post the SQL.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[pietlinden]

I am determining invoice totals for my firm. I developed a qry for current yr
invoice totals, a query for prior year invoice totals ... and then I combine
them in a query and it works beautifully.

Now, I want to have a few adjustments to the totals. The adjustments work
fine in the current year and prior year queries, but it is showing duplicates
in the combined query when I have a transaction for the same client in the
same engagement. I have tried different joins to no avail. Any suggestions?

See below:
Cltnum  CltEng  Comparison CurrentYrInvoiceAmount
PriorYrInvoiceAmount    CurrentYrAdjustments    PriorYrAdjustments
004426  00006   -32525  27380   59905  
004426  00006   -59905  0       59905   16395

Help.  

SELECT DISTINCT [FieldList]
FROM MyTable1 [JOIN] Table2....
WHERE...

 

[KARL DEWEY]

Use a single query like this --
SELECT Sum(IIF(Year(SalesDate) = 2008, Sales, 0)) AS 2008_Sales,
Sum(IIF(Year(SalesDate) = 2009, Sales, 0)) AS 2008_Sales,
Sum(IIF(Year(SalesDate) = 2008, Sales, 0)) - Sum(IIF(Year(SalesDate) = 2009,
Sales, 0)) AS 2008_Minus_2009_Sales
FROM YourTable;