Sub-Total by Project within a Client Query

[andy_dyer]

I'm pretty sure this isn't possible...

I'm trying to replicate the manual excel process we have through the power of Access...

I need to be able to sub-total at a project level

So that when I run a query for a client say ABC I want to have all the invoices for Project 1 sub-totalled, then Project 2 etc etc

Query:

SELECT tblProject.[Project Number], tblProject.Client, tblProject.[Project Title], tblProject.[Total Value], tblProject.[Purchase Order No], tblInvoice.[Actual Invoice Date], tblInvoice.[Invoice Number], tblInvoice.[Invoice Amount (NET)], tblInvoice.[Payment Received Date]
FROM tblProject INNER JOIN tblInvoice ON tblProject.ProjectID = tblInvoice.ProjectID
WHERE (((tblProject.Client)=[forms]![frmHighlight]![cboClient]) AND ((tblProject.[Project Status])="Open"))
GROUP BY tblProject.[Project Number], tblProject.Client, tblProject.[Project Title], tblProject.[Total Value], tblProject.[Purchase Order No], tblInvoice.[Actual Invoice Date], tblInvoice.[Invoice Number], tblInvoice.[Invoice Amount (NET)], tblInvoice.[Payment Received Date]
ORDER BY tblInvoice.[Actual Invoice Date] DESC;

The best I've managed is to create a new query which totals the projects:

SELECT qryHighlight.[Project Number], qryHighlight.Client, qryHighlight.[Project Title], qryHighlight.[Total Value], qryHighlight.[Purchase Order No], qryHighlight.[Actual Invoice Date], qryHighlight.[Invoice Number], Sum(qryHighlight.[Invoice Amount (NET)]) AS [Invoiced Amount], qryHighlight.[Payment Received Date]
FROM qryHighlight
GROUP BY qryHighlight.[Project Number], qryHighlight.Client, qryHighlight.[Project Title], qryHighlight.[Total Value], qryHighlight.[Purchase Order No], qryHighlight.[Actual Invoice Date], qryHighlight.[Invoice Number], qryHighlight.[Payment Received Date]
HAVING (((qryHighlight.[Invoice Number])>0));

Then insert the new field "Invoiced Amount" into another query based on the first...

Does that make any sense??

I'm pretty sure this is a complicated method and it still gives it to me as a column on my query...

I need...

ABC
Project 1 - Invoice 1 = £5
Project 1 - Invoice 2 = £10
Sub Total Project 1 = £15
Project 2 - Invoice 1 = £30
Sub Total Project 2 = £30

Not...

ABC
Project 1 - Invoice 1 = £5 - Sub Total £15
Project 1 - Invoice 2 = £10 - Sub Total £15
Project 2 - Invoice 1 = £30 - Sub Total £30

Sorry about the formatting - hope you catch my drift...