Vlookup in query..

[peterlee516]

Thanks in advance for your assistance.

I have an invoice table and a project table. A 1 to Many relationship from Project to Invoice respectively.

Invoice Table:
InvoiceID
ProjectID
InvoiceAmount
Status (Paid, Not Paid)


I am trying to create a report that lists Total Invoiced and Total Paid by Project.

I've been trying to do it in 2 separate queries (Total Sum of invoice and Total Sum of invoice where Status is Paid) and linking them but that causes multiple records.

Is there a way to do it in one query using a constraint on the second "Total Sum of Invoices" (i.e. where status is paid)?

Thanks.

 

[Jon K]

Try this query.

SELECT [ProjectID], Sum([InvoiceAmount]) AS TotalInvoiceAmount, Sum(IIf([Status]="Paid",[InvoiceAmount],0)) AS Paid
FROM [Invoice]
GROUP BY [ProjectID]

.

 

[peterlee516]

Learned something new! Thank you!!