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.
Access Query Help Needed
-
Topic author - Site Admin
- Posts in topic: 1
- Posts: 17787
- Joined: Wed Dec 22, 2004 9:31 pm
- Location: Friendswood, TX
- Contact:
-
- Senior Member
- Posts in topic: 1
- Posts: 755
- Joined: Wed Feb 04, 2009 10:45 pm
- Location: Houston Texas Area
Re: Access Query Help Needed
You can use a SQL query to ask for each name where payment >0.
SELECT name FROM table WHERE payment >0.
SELECT name FROM table WHERE payment >0.
Laws that forbid the carrying of arms...disarm only those who are neither inclined nor determined to commit crimes... (Jefferson quoting Beccaria)
... tyrants accomplish their purposes ...by disarming the people, and making it an offense to keep arms. - Supreme Court Justice Joseph Story, 1840
-
- Senior Member
- Posts in topic: 1
- Posts: 389
- Joined: Sun Mar 08, 2009 9:25 pm
- Location: Near East Texas
Re: Access Query Help Needed
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.
-
- Senior Member
- Posts in topic: 1
- Posts: 595
- Joined: Wed Aug 17, 2005 4:05 pm
- Location: Highlands,Tejas
Re: Access Query Help Needed
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
Reasonable gun control is hitting your target with the first shot.