Help with a transform

[russgreen]

I have a database that I'm trying to perform a transform query on.

I have the following query that is working OK:

TRANSFORM Sum(tblInvoices.InvoiceAmount) AS SumOfInvoiceAmount
SELECT Sum(tblInvoices.InvoiceAmount) AS TotalInvoicing
FROM tblProjects INNER JOIN tblInvoices ON tblProjects.ProjectID = tblInvoices.ProjectID
WHERE (((DatePart("yyyy",[DueDate]))=[@Year]))
GROUP BY tblInvoices.CompanyID
PIVOT Format([DueDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

This query simply sums monthly totals for invoices from the table tblInvoices.

Within my database I also have a table that stores project (tblProjects). Invoices in tblInvoices relate top tblProject with the field ProjectID. At times projects go on hold (tblProjects.StatusID=3).

What I want to achieve is to have a transform query thats sums the monthly invoicing totals where the tblProjects.StatusID is NOT 3. Except if an invoice has been issued (tblInvoices.Issued=True) I want to include it in the total, even if the project that it relates to is tblProjects.StatusID=3

Any help here would be greatly appreciated.

Russ

 

[PHV]

Something lik this ?
...
WHERE DatePart("yyyy",[DueDate])=[@Year]
AND (tblProjects.StatusID<>3 OR tblInvoices.Issued=True)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[russgreen]

Ah. So simple. Now I feel dumb. It was very late!