Thanks for getting back to me Pieter, I have tried and failed with the sub-query. I don't understand what you are referencing in the example. Invoice = jrn.Invoice. There is...

I wasn't able to use the formatting directly from the forum but this is what I have achieved thus far.

Thanks for the prompt reply maybe I am doing something wrong as I said I am a novice. I tried to use the example you gave to no avail.

Declare...

Thank you guys

Hi Guys,

Cracked it, report is working fine.

Thanks

Hi Jeff,

Should I re-format my code with comments and upload? or upload how the sample report now appears?

Thanks

It hasn't actually resolved because now anything in base is showing as a base currency.

I put the G/L as a left join and anything I had in the where clause...

Thanks guys resolved.

Thank you, I'll put it into the format as per the rules and put some examples together.

Sorry a little more structured.

SELECT InvcHead.Company

,InvcHead.InvoiceNum AS [Invoice Number]

,InvcHead.CurrencyCode AS Currency

,CONVERT(VARCHAR, InvcHead.InvoiceDate, 101) AS [Invoice Date]

,CONVERT(VARCHAR, InvcHead.DueDate, 101) AS [Due Date]

,DATEDIFF(dd, InvcHead.DueDate, GETDATE()) AS [Days Over]

,InvcHead.UnpostedBal AS [Invoice Bal]

,Customer.NAME AS...

Hopefully this makes it more readable.

SELECT InvcHead.Company ...

The left join gave me example the same result as I had before, the data did not change.

I have 4 tables,

Invchead - Holds most the information I need to compile a report, had to join the following tables to get extended detail.

Customer - Had to join...

I've tried all combinations of joins but it doesn't seem to work, I guess due to the number of filters I have in the where Clause.

Sorry will not give the desired result of showing all data but excluding the base currency still