query which returns only non-empty fields

[neil27]

Hi,
I am writing a database in Access 2003 for my school about the membership of school clubs. My main table has fields:
admission number - primary key
first name
surname
Fencing
Judo
Table tennis
etc
The club fields are either blank -if the person isn't and has never been a member- or Y fro current member or X for past member

I want to write a query which will list only the clubs for which the person is or has been a member - ie. not show the blank ones. Does anyone know how to do this?

Thanks

 

[Brianwarnock]

What you have is a spreadsheet not a database, it needs normalising, to avoid me reinventing the wheel see this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=237087

Brian

 

[plog]

You need to properly structure your database. This project is going to require 3 tables: Members, Clubs and Membership. These will be the fields of each:

Members
MemberID, autonumber primary key
FirstName, text
SurName, text

Clubs
ClubID, autonumber primary key
ClubName, text

Membership
MemberID, numeric link to Members table
ClubID, numeric link to Clubs table
Active, Yes/No field to denote if membership is current

Get your data into that structure and the query you want to run becomes trivial. FYI, what I did with your tables is called 'normalization' (http://en.wikipedia.org/wiki/Database_normalization), look it up and read a few tutorials to understand the process.

 

[neil27]

Thanks for the help. I will redesign my database accordingly.

 

[neil27]

Hi,

I set up my tables as described ie.
Members
MemberID, autonumber primary key
FirstName, text
SurName, text

Clubs
ClubID, autonumber primary key
ClubName, text

Membership
MemberID, numeric link to Members table
ClubID, numeric link to Clubs table
Active, Yes/No field to denote if membership is current

Having done this I still didn't find it straightforward to design a query which would find a persons clubs from an input of their surname. I have tries numerous queries but so far have only suceeded by using two sepate queries - the first inputs the surname and outputs the persons number and the second uses the number to output the clubnames for which he is a member. However, I would really like to just input a surname and have Aceess output the persons clubs. Could anyone tell me how to design a query which does this.
Thanks

 

[plog]

This SQL will give you every member's name of every club name:

SELECT Members.FirstName, Members.SurName, Clubs.ClubName, Membership.Active
FROM (Members INNER JOIN Membership ON Members.MemberID = Membership.MemberID) INNER JOIN Clubs ON Membership.ClubID = Clubs.ClubID;

 

[neil27]

Thanks but I'm not trying to find this. I want to be able to type in a surname and have that person's ( or persons') club listed

 

[neil27]

ok, I have adjusted your query to enter the surname and it work really well. Thanks.