473,323 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

A query to produce unpaid members

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
4 2231
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jimmy Tran | last post by:
Hi All, I have a table below and I want to design a query to pull all the members from the TABLE into a Query Result and into a single column with points assigned appropriately, but I am having...
1
by: bstout2 | last post by:
I'm going to simplify the tables and queries to eliminate all but the pertinent info. We have a table containing detail information on our invoices called ARDETAIL. Each line item on the...
5
by: Pablo | last post by:
Hello, there, I have two tables: tblPCP: it has 108 doctors name and affiliation number. tblMembers: it has 7800 members name and PCP affiliation number. Let's say tblPCP has two fields: PCPName,...
1
by: Eskil | last post by:
Hi I have a form that supplies my query with information on two different variables. The form uses a lookup to display a list of 5 different customer types and 5 different types of...
2
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for...
1
by: socasteel21 via AccessMonster.com | last post by:
I have a database that is used to process warranty claims. Each record in my main table (tblClaim) represents a claim. Each claim has a status of Paid, Unpaid, or Pending. I would like to add a...
3
by: SQL Learner | last post by:
Hi All, I a table with two columns: ID Note --- ------ 1 Andy was paid on 4/5/04. He was working on the xxx project. 2 Tom was paid on 4/3/05. He was working on...
1
by: bhushanbagul | last post by:
Hi, I have below table structures Id'd Status Id's Table A: table_a_id number(10) pk Data: 1 ...
3
by: Christoph | last post by:
I have an <S access databse and try to select the the customer ID to print a statemet report based on the customer ID but am unable to do so Can anybody help Thank you Christoph Access...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.