Criteria over multiple fields

[AJordan]

Currently my brain is fried and I can't think of a way to qrite a query that is based on multiple fields.

Example:
5 Fields: Donor,2002D,2004D,2006D, 2008D
- Donor is the names, other fields show if they donated that year (Y or N)

Question
How do I query to bring back records that show donors that have donated in at least 1 of those years? Basically excluding all N,N,N,N???

My current job is destroying braincells!

 

[Scooterbug]

Well, if table setup were correctly normalized, it wouldn't be a problem

What you should of had (have) is one table for the Donor and their information and another table where you record each donation (Including the DonorID and the Donation Date). But since you dont, you are going to have to tackle it a different way. Off the top of my head:

SELECT Table1.Donor, 
           Table1.[2002D], 
           Table1.[2004D], 
           Table1.[2006D], 
           Table1.[2008D]
FROM Table1
WHERE (((Table1.[2002D])=-1)) 
OR (((Table1.[2004D])=-1)) 
OR (((Table1.[2006D])=-1)) 
OR (((Table1.[2008D])=-1));

 

[AJordan]

Trust me I know the client pushed excel to the brink, with different people adding to the monster over 6 years. So they have provided me with all of this wonderful data in one table!

 

[Scooterbug]

Give them a few good slaps for that

 

[wiklendt]

Trust me I know the client pushed excel to the brink, with different people adding to the monster over 6 years. So they have provided me with all of this wonderful data in one table!

i feel your pain ;-)

 

[AJordan]

Scooter, thanks for this! my brain stopped working as this is a massive table with over 300K records and endless fields. It's really ridiculous, and had you not suggested your answer I was going to to do a query that searched for donors who did not meet the requirement. And then use a find unmatched query to resolve this.

Your solution much simpler

 

[gemma-the-husky]

if this is a regular thing, you would be better importing the excel file, and then normalising it in your database to get it into a "normal" form.

this can all be automated, and will give you a proper database, with much more flexibilty going forward.

even to the point where you decide to do away with the spreadsheet, and record future donations in your access dbs