TexasCHLforum.com

I have imported the SQL database for the TexasCHLforum Day registrations into an Access database. I need to know if it is possible for a rookie to structure a query that will return the people who have paid and who have not.

I know that sounds simple, but here is the problem. Since people registered for each seminar, clinic and mini-clinic as separate events, each person registered has several records. When people sent in their registration fee, I just entered the payment on one of their event registrations. (I didn't want to take the time to enter it in each one, plus this would not let me get a good number for total registration fees.) If I do a simple query to return all entries in which the "paid" field is "0" it will return everyone since everybody has at least one registration with a "0" in the paid field.

Is there an easy way to structure a query that will return only people who do not have any registration with "15" in the paid field? If so, how do I get it into an Access query? I don't see a way to do that within Access.

The response may be long and it won't be of interest to everyone, so feel free to send me a PM if you prefer.

Thanks,
Chas.

You can use a SQL query to ask for each name where payment >0.

SELECT name FROM table WHERE payment >0.

If your table has a name field and a type registration field (along with a field for payment amount), you can set up multiple queries, 1 for each type registration and create separate tables with the output of these queries. Then, write a query that links the name field in each of the "registration" tables and then checks the payment amount in each table. If the "amount" field has the same name regardless of the registration type, you may need to create unique amount field names by concatenating the name of the registration type with the amount field. The you would check for merged (linked) records with "seminar amount" equal to "0" or "clinic amount" equal to "0" or "mini-clinic 001 amount" equal to "0" and so on.

TLE2 wrote:You can use a SQL query to ask for each name where payment >0.

SELECT name FROM table WHERE payment >0.

and to get the names in sorted order with no payment

SELECT DISTINCT name
FROM table
WHERE name NOT IN (SELECT DISTINCT name FROM table WHERE payment > 0)
ORDER BY name