[AccessD] A Query Question

This one may be too much for QBE.  If I understand it correctly, you need
to:

- Select contributions for a particular time period
- Sum them
- Select on a threshold amount
- Select contributions for contributors meeting this criterion

For starters, I would use a series of cascading queries, as follows:

1 Select contributions for the period
2 Sum the contributions in 1) by contributor
3 Select the contributors from 2) who meet the criteron
4 Use the result set of 3) in an IN clause to select the contribution rows

Once you get this set of queries working, you should be able to combine them
into a single SQL statement.  Personally, I probably would not bother.

Regards,

Steve Goodhall, PMP
248-505-5204 mobile



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris
Fields
Sent: Monday, September 29, 2008 9:41 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] A Query Question


Hi All,

I have a table with names and other details of persons and organizations 
and a table with records of contributions.  The requirement is to craft 
a report showing all the contributors who gave at least some particular 
amount over some particular time period.  The time period and amount are 
selected by the user from a form.

I have a query that selects the pertinent fields from the tblNames and 
the tblContributions.  So, I need to craft another query that selects 
only those contributions made between the starting and ending dates 
(found on the form) and that total the particular minimum amount.  Using 
the QBE grid I can easily select all the contribution records from my 
first query that match the date criteria provided from my form.  But, I 
also want to get totals that match the amount criterion from the form.  
I tried using the totals row and ran into trouble right away - it 
doesn't like asterisks at all, insists on my putting each field I want 
from the first query into the grid - but, then what?  The only fields I 
really want totals of any kind for are the MEMID (Group by), the 
contribution amount CAMT (Sum), and the date field (Where the criteria 
are met).  All other fields I just want to have appear, but I don't know 
what selection to make from the totals drop list.  I tried Var, but that 
generated a data type mismatch.  So, I am feeling stymied.  Any help?

Probably as soon as I send this plea for help off to the list, my brain 
will kick in and I will see what I am not seeing now. 

Thanks,
TNF
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com