Counting Words across two fields

[donmouth]

Sorry if this is very basic.

 

We have our church membership lists in a MySQL database with the following fields:

 

Surname

Person1

Person1_Membership

Person2

Person2_Membership

...

 

The membership fields will either be empty or contain "Resident" or "Non-Resident". The reason for two columns is that some couples may only have one partner as a member.

 

I am trying (without success) to find a way of getting a total (to display on a web page) of how many "Resident" and how many "Non-Resident" members we have. In other words, I'm trying to get a sum of the total number of times "Resident" and and total number of times "Non-Resident" appears in the two columns combined.

 

(hope that makes sense!)

 

I would appreciate any pointers from anyone who has managed to do this, or something similar, already.

 

Many thanks for the help

[Adam]

Why not create a separate row for each, but store their resident status in a field of it's own? You could link to the other partner with a "parent" record:

 

ID | Surname | Firstname | Resident | Parent

1  | Smith  | John      | 1        | null

2  | Smith  | Linda    | 0        | 1

 

This would make such calculations a lot easier.