Query for an Invoice Report

  • Thread starter Thread starter Douglas
  • Start date Start date
D

Douglas

Im trying to write a query that calculates the total cost for each
invoice and displays the figure for all the customers who havn't paid
yet.

I have two tables: Job & Invoice Items

Job: Invoice No, Date, Description, Invoice Paid Date, Invoice Printed
Date

Invoice Items: Invoice No, Item Description, Item Count, Unit Cost,
VAT Rate


A Job can have many Invoice Items linked by Invoice No



So far my query looks like this

SELECT InvItems.Invoice_No, InvItems.Description, InvItems.Item_Count,
InvItems.Cost, InvItems.VAT_Rate,[InvItems].[Item_Count]*[InvItems].[Cost]*(1+[InvItems].[VAT_Rate])
AS GrandTotal
FROM Job INNER JOIN InvItems ON Job.Invoice_No = InvItems.Invoice_No
WHERE ((Job.PaidDate) Is Null);

This produced a line by line list of items and total item costs.


My question is how do i take it to the next level and get it to add up
all the items with the same Invoice number?
So eventually I want my query to return only two fields, Invoice
Number and Total Invoice amount?
 
Hi,



SELECT Invoice_No, SUM(GrandTotal)

FROM previousSavedQuery

GROUP BY Invoice_No




should do, no?


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top