Help with SQL please - adding column contents

[rogerhwatts]

Hi,
I have a membership database. Each year we have column for the Date paid (eg Bank2014) and the amount paid ( eg Amt2014).
We run a calendar year membership but members can pay up to 2 months early. However, for Gift Aid we need any subs banked in the given Calendar year, regardless of which subs year they are for.
So, I need a Base Query column which calculates (Bank2014>=1/1/14)* Amt2014 + (Bank2015< 1/1/15)* Amt2015

I'm sure this must be quite simple.

regards

[MTP]

I think you will need two (sub)queries: one to sum over the rows where Bank2014>=1/1/14 and one to sum over the rows where Bank2015<1/1/15:

Code: Select all   Expand viewCollapse view

SELECT "A"."2014_Subs" + "B"."2015_Subs" AS "2014_Banked_Subs"
   FROM (SELECT SUM("Amt2014") AS "2014_Subs" FROM "YourTableName" WHERE "Bank2014" >= '2014-01-01') "A",
   (SELECT SUM("Amt2015") AS "2015_Subs" FROM "YourTableName" WHERE "Bank2015" < '2015-01-01') "B"

Not really that simple. This would be much easier if you stored your membership dues in rows, in a separate table, instead of in columns in your main table. For example, you could have the following tables where TABLE NAME is in all caps and column names for each table are listed under the name:

MEMBERS
MemberID (primary key)
MemberName
MemberAddress
[Other member identifying information]

SUBSCRIPTIONS
SubID (primary key)
MemberID (foreign key - meaning, it matches a primary key from the MEMBERS table)
SubYear
PaymentDate
AmountPaid


Now, if your database was set up like that, your query would be:

Code: Select all   Expand viewCollapse view

SELECT SUM("AmountPaid")
   FROM "SUBSCRIPTIONS"
   WHERE "PaymentDate"  >= '2014-01-01' AND "PaymentDate" < '2015-01-01'

Much simpler, yes?

[rogerhwatts]

Thanks. I will try that out.
I realise that this would be much easier if I had a separate Table for subs ie a relational database. My problem is that our group (1000 strong) is generally elderly and few (admit to ) knowing anything about databases. So I keep it so that, if the worst came to the worst, the single main table could be copied (using fairly simple instructions) as a spreadsheet which maybe someone else would be able to comprehend.