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??? 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
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
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
| |