Creating a totals per row in query

[Ceebaby via AccessMonster.com]

Hi Folks

In a table I have 1 client that can have many funding applications and sub
funding applications.
I have created 2 tables for this. If a client has 1 main funding application
open and 3 subfunding applications open of differing amounts is there a way
to calculate the total subfunding applications against the 1 main funding on
the same query row EG

Table 1 Table 2
IDPK Funding Amt IDFK Subfunding
SubAmt

457 Energy Grant £10,000 457 Loft
250
457 Solar
Panel 5000
457
c/heating 2000
totals £10,000
7500

Creating the query i get

energy grant £10,000
£250
energy grant £10,000
£5000
energy grant £10,000
£2000
what i would like is
£10,000
7500

When I create the report and total the energy grant I get

energy grant £30,000 against
£7500

when it should be £10,0000 against
£7500

Is this possible and I hope I have given enough information.

Thanks in advance
Ceebaby
London

 

[KARL DEWEY]

Post the query you now have.

 

[Ceebaby via AccessMonster.com]

Post the query you now have.

Hi Karl

Many thanks for your response
Here is the SQL. I hope this makes sense.SELECT TBLGrant.FoaID, TBLGrant.PropertyID, TBLGrant.ApplicationState,
TBLGrant.FoaType, TBLGrant.ActualFOA, [ActualFOA]+nz([SuppTotal]) AS
Committed, TBLGrant.ApprovalDate, TBLGrant.NoOfUnits, TBLGrant.
CertificateDate, TBLGrant.RepayReceived, TBLSupplement.SupplementID,
TBLSupplement.SuppTotal, TBLSupplement.SuppDate, TBLSupplement.SuppType,
TBLGrant.LoanStatementDate, TBLGrant.LoanStatement, TBLGrant.ChgDeReg,
TBLGrant.DateDeReg, TBLGrant.ChgRegistered, TBLSupplement.SuppType
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
ORDER BY TBLGrant.FoaID;

I was wondering whether I should have another table to store the sub funding
totals for each grant?

I look forward to reading your comments on this.

Cheers
Ceebaby

 

[cesima via AccessMonster.com]

Karl

I noticed that my post was all over the place
hopefully this will come out a bit better


Creating the query I get
Funding amt Subfunding
energy grant £10,000 £250
energy grant £10,000 £5000
energy grant £10,000 £2000

what I would like is
Funding amt Subfunding
energy grant £10,000 £7500

When I create the report and create totals
I get
energy grant £30,000 against subfunding of £7500
when it should be
energy grant £10,0000 against subfunding of £7500

Cheers
Ceebaby

 

[KARL DEWEY]

Try this --
SELECT TBLGrant.FoaType, Sum(Nz([ActualFOA],0)+nz([SuppTotal],0)) AS
Committed, TBLSupplement.SuppType, Sum(Nz([TBLSupplement].[SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType;

Post the query you now have.

Hi Karl

Many thanks for your response
Here is the SQL. I hope this makes sense.SELECT TBLGrant.FoaID, TBLGrant.PropertyID, TBLGrant.ApplicationState,
TBLGrant.FoaType, TBLGrant.ActualFOA, [ActualFOA]+nz([SuppTotal]) AS
Committed, TBLGrant.ApprovalDate, TBLGrant.NoOfUnits, TBLGrant.
CertificateDate, TBLGrant.RepayReceived, TBLSupplement.SupplementID,
TBLSupplement.SuppTotal, TBLSupplement.SuppDate, TBLSupplement.SuppType,
TBLGrant.LoanStatementDate, TBLGrant.LoanStatement, TBLGrant.ChgDeReg,
TBLGrant.DateDeReg, TBLGrant.ChgRegistered, TBLSupplement.SuppType
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
ORDER BY TBLGrant.FoaID;

I was wondering whether I should have another table to store the sub funding
totals for each grant?

I look forward to reading your comments on this.

Cheers
Ceebaby

 

[Ceebaby via AccessMonster.com]

Try this --
SELECT TBLGrant.FoaType, Sum(Nz([ActualFOA],0)+nz([SuppTotal],0)) AS
Committed, TBLSupplement.SuppType, Sum(Nz([TBLSupplement].[SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType;
[quoted text clipped - 20 lines]

Cheers
Ceebaby

Thanks for your time on this Karl.

I have inserted the SQL (not my strength in Access) into the query but I get
a syntax error:

syntax error missing operator in query expression (Sum(Nz([SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType

Have I missed out something?

Cheers
Celia

 

[Ceebaby via AccessMonster.com]

Try this --
SELECT TBLGrant.FoaType, Sum(Nz([ActualFOA],0)+nz([SuppTotal],0)) AS
Committed, TBLSupplement.SuppType, Sum(Nz([TBLSupplement].[SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType;
[quoted text clipped - 20 lines]

Cheers
Ceebaby

Thanks for your time on this Karl.

I have inserted the SQL (not my strength in Access) into the query but I get
a syntax error:

syntax error missing operator in query expression (Sum(Nz([SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType

Have I missed out something?

Cheers
Ceebaby

 

[cesima via AccessMonster.com]

Try this --
SELECT TBLGrant.FoaType, Sum(Nz([ActualFOA],0)+nz([SuppTotal],0)) AS

[quoted text clipped - 8 lines]Thanks for your time on this Karl.

I have inserted the SQL (not my strength in Access) into the query but I get
a syntax error:

syntax error missing operator in query expression (Sum(Nz([SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType

Have I missed out something?

Cheers
Ceebaby

Hi Karl

I have seen the error I missed off a parenthesis. Thank you for your help on
this. It is working fine now. I however need to brush up on my SQL.

Have a good evening

Cheers
Ceebaby

 

[KARL DEWEY]

Have I missed out something?

Add parenthsis after ,0) and before AS

Sum(Nz([SuppTotal],0)) AS SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID =TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType;

Remove any hard returns the copying and pasting may have put in the SQL.

Try this --
SELECT TBLGrant.FoaType, Sum(Nz([ActualFOA],0)+nz([SuppTotal],0)) AS
Committed, TBLSupplement.SuppType, Sum(Nz([TBLSupplement].[SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType;

Post the query you now have.

[quoted text clipped - 20 lines]

Cheers
Ceebaby

Thanks for your time on this Karl.

I have inserted the SQL (not my strength in Access) into the query but I get
a syntax error:

syntax error missing operator in query expression (Sum(Nz([SuppTotal],0) AS
SupplmentTotal
FROM TBLGrant LEFT JOIN TBLSupplement ON TBLGrant.FoaID = TBLSupplement.FoaID
GROUP BY TBLGrant.FoaType, TBLSupplement.SuppType

Have I missed out something?

Cheers
Ceebaby