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
Help with SQL please - adding column contents
-
- Posts: 44
- Joined: Mon Mar 21, 2011 5:22 pm
Re: Help with SQL please - adding column contents
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:
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:
Much simpler, yes?
Code: Select all
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"
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
SELECT SUM("AmountPaid")
FROM "SUBSCRIPTIONS"
WHERE "PaymentDate" >= '2014-01-01' AND "PaymentDate" < '2015-01-01'
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
-
- Posts: 44
- Joined: Mon Mar 21, 2011 5:22 pm
Re: Help with SQL please - adding column contents
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.
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.
OpenOffice 4.00
Windows 7
Windows 7