Access Syntax

[shar4254]

Please forgive my ignorance, but I have been tossed into an Access database through no fault of my own. My husband is the president of a not-for-profit historical organization. I have been forced into the position of membership secretary. I can generally get around Access to get what I need, but I can't resolve this problem. The database is set up in two parts, members and transactions linked to those members. I need to generate a set of labels using specific member locations by state and within a time period (i.e. 2000 to 2002). The query works, in that I can retrieve the information by state and also by criteria being "2000, 2001, 2002" membership information from transactions, but in doing so my labels include John Doe 2000, 2001 and 2002 member and Steve Smith, 2000 and 2001 member, etc. How can I limit the query so as to only retrieve John Doe or Steve Smith without duplicate entries for multiple year subscriptions? Again, I apologe for my ignorance and any help you could give would be most appreciated.

 

[KenReay]

Hi

You do not describe the query you are using, but I assume it is a join between the members table and the transactions table, with criteria on the data field(s) on the transaction?

Try something like:

SELECT * FROM tblMembers WHERE memberId IN (SELECT MemeberId FROM tblTransaction WHERE Year IN ("2000", "2001", "2002"

you need to substitute your own table names and column names of course

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[JeremyNYC]

If you post the text from the sql view of your query it will be easier to help you. Have you tried using totals queries to do the job? I can't tell from what's in your first post, but my guess is that you can just group by member and get what you need.

But post that text, and I'll see what I can do.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.