Counting Charities

[whitestone]

Here's a dilly of a pickle...

I have set up a database for the charity that I work at, it contains a form (individual bio information) with a sub-form in it linked up to a seperate table. This sub-form/table contains twenty fields of bedfellow charities (charities that the individual has also donated too). These fields are all free text with one charity in each and in no particular order.

I now want to find out which charities that the people who give to us most often also give to. I can't work out how....If I had just one field I would do a count using the ID number that links the two tables??

 

[Mile-O]

First suggestion would be to change your structure.

tblPeople
PersonID
PersonForename
PersonSurname
other fields relating to the person

tblCharities
CharityID
CharityName
other fields relating to the charity

tblDonations
DonationID
PersonID
CharityID
other fields relating to the donation

relate the PersonID in tblPeople to the PersonID in tblDonations (one to many)
relate the CharityID in tblCharities to the CharityID in tblDonations (one to many)


That way you'll be able to query more successfully with your data.

 

[whitestone]

As each person will only have one gift (it is a database for Legacy research) do I really need to separate out the gifts? it would be one-to-one.

 

[Mile-O]

This sub-form/table contains twenty fields of bedfellow charities (charities that the individual has also donated too). These fields are all free text with one charity in each and in no particular order.

It's just that what you have described sounds more like a spreadsheet than a database.

 

[hangglide]

Tech note

Sorry to be technical but a spreadsheet is a database, it is just not a relational database (by itself).

 

[Mile-O]

You know what I meant!

 

[hangglide]

Sure did Mile-O-Phile . I have a bad habit of being too technical. It drives my wife nuts! LOL

BTW, I like your sig.