How to add values together from several different records under the same ID?

[eidaa]

Hello,

Since it is slightly hard to describe the problem I'm uploading the database itself to make it slightly easier to describe it.

Now the problem is that I am trying to make a membership form which should include fully added together subscription fees for each member however if you look in the Member Sub Owing query, then you can see that each member has a subscription for several years and some have paid and some have not, giving several same memberIDs with different years and the costs in different records.

So what I am asking is; How would I go about creating a simple query that would give me the full total subscription fee for all subscribed years for one member? e.g.
MemberID=2054, Total Fee=£9.50

instead of:
MemberID=2054, Year=2010, Fee=0
MemberID=2054, Year=2011, Fee=0
MemberID=2054, Year=2012, Fee=9.50

Relatively simple looking problem but I have been stuck on this for a good while now.

 

[mdlueck]

Quick thought... check into SQL GROUP BY, which will allow you to specify a column to look for matching values in, and then you may run a SUM against the currency column, and that would only SUM within the same values of the column you told SQL to GROUP BY.

 

[mdlueck]

Here is some example SQL that does what I was describing...

    SELECT [p].[id],
           SUM(CASE
                 WHEN ([ppl].[eau] IS NOT NULL) THEN [ppl].[eau] * [ppl].[qtyper]
                 WHEN ([prod].[eau] <> 0) THEN [prod].[eau] * [ppl].[qtyper]
                 ELSE 0
               END) AS [eau]
    FROM [dbo].[projects] AS [proj]
    INNER JOIN [dbo].[products] AS [prod] ON [proj].[id] = [prod].[projectid]
    INNER JOIN [dbo].[productpartlink] AS [ppl] ON [prod].[id] = [ppl].[productid]
    INNER JOIN [dbo].[parts] AS [p] ON [ppl].[partid] = [p].[id]
    WHERE [proj].[id] = @projid
    GROUP BY [p].[id]

A given part ( [p].[id] ) might be attached to multiple Products within the one Project.

The GROUP BY specifies to group all same parts together.

The SELECT part of the query is going to SUM up the total QtyPer. It will not SUM up all of the QtyPer's into one pot since the GROUP BY is included in the query.

In my example there are two possible places that the EAU (Estimated Anual Usage) could be defined. You do not need to deal with that CASE statement. For your case, just SUM upon the column that you need SUM'ed.

 

[eidaa]

Tried using the SQL and it seems to be working. Thank you.

 

[mdlueck]

You are very welcome, eidaa. Thank you for posting your success.

 

[gemma-the-husky]

eidaa - is this access?

if so, needing SQL direct seems strange. design a totals query and go from there. You can view the SQL that the query designs, if you ever need it - but most stuff you can do in query design windows.

 

[eidaa]

Yeah this is Access but I tend to work on spreadsheets a lot more and barely ever touch Access so I wasn't able to figure out how to do it on the design view.