By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,972 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,972 IT Pros & Developers. It's quick & easy.

A query to produce unpaid members

P: n/a
I have a database containing info on subscribers to an organization. I
have one table with members info in it (name, address, etc.), another
table containing orders for membership, (AV materials, etc.), and other
ancillary related tables.

I need to query my database to find all of the members who paid their
dues last year, but did NOT pay this year. Sounds easy, but I can't
seem to create a query or queries that does the job. When I search
(create criteria) on the yearly membership item codes in the order
table, no matter how I try to design the criteria I get a dynaset that
has either one entry for a member's name (with all address info) (if
they were members last year), one entry if they are a new member this
year, or two entries if they paid last year and this year. I want the
query to contain only the first instance.

I eventually need to produce a mailmerge to Word to send a reminder
letter (that part is easy).

In other words, if FY04 and FY05 represent membership in these years, I
want to produce a query that selects "FY04 And Not FY05". This doesn't
work if naively applied to my DB for obvious reasons.

How do I produce a query that will contain only the information on
members who did not resubscribe this year???

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi,
You have not mentioned how you are storing the membership information.
If it is in a single field, which contains "FY04,FY05" for someone
who has paid both, then I think you can get what you want by using 2
queries. The first one can create a calculated field marking "FY04"
differently from "FY04,FY05". Then a second query can pick out
those of the first type alone.

HTH

Sunil Korah

Nov 13 '05 #2

P: n/a
Hi,
You have not mentioned how you are storing the membership information.
In a single field, or multiple fields of the same record or multiple
records. My feeling is that you would need 2 queries, the first one
will mark those who have FY04 and those who have FY05 differently in a
new calculated field. Then you can run another query on the result to
pick out whichwver set you want.

HTH

Sunil Korah

Nov 13 '05 #3

P: n/a
Sunil wrote:
Hi,
You have not mentioned how you are storing the membership information. If it is in a single field, which contains "FY04,FY05" for someone
who has paid both, then I think you can get what you want by using 2
queries. The first one can create a calculated field marking "FY04"
differently from "FY04,FY05". Then a second query can pick out
those of the first type alone.

HTH

Sunil Korah


Membership info is in one table, orders info in another, item
description in another, relationally linked. The "FY0X" info is stored
in the orders table and the description of the order ("FY" text
description) is located in the item description table. The orders table
contains the customer ID number.

Nov 13 '05 #4

P: n/a
wizard_chef wrote:
Membership info is in one table, orders info in another, item
description in another, relationally linked. The "FY0X" info is stored
in the orders table and the description of the order ("FY" text
description) is located in the item description table. The orders table
contains the customer ID number.


Note: All SQL to follow is air-code.

Create a query that returns all customers that are members for 2005, something along the lines of:

qry2005
select CustomerId from tblOrder where OrderCode = "FY05"

Now create a query that returns all customers that are members for 2004, with the additional requirement that they not
be a part of the first query, something like:

qry2004AndNot2005
select CustomerId, [, Other Fields as Required] from tblOrder
where OrderCode = "FY04" and
CustomerId not in (select * from qry2005)
--
To Email Me, ROT13 My Shown Email Address

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.