Compare select results between 2 tables 2

[NerdTop72]

I record all entries of payment from members. We have a yearly fee that needs to be paid to continue being a member. How do I get a list of those who have not paid?
I have an Access database with 2 tables.

MembersTable
MemberID
Name
Address
LifeMember = Boolean

Ledger Table
Id
MemberID
LedgerNum
CheckNum
DateStamp
AMT
Description

The Description field is only allowed certain description. “Dues Paid” is one of them…

I want to make a query that shows what members have not paid their dues? I need the query to...
Select MemberID from TblLedger where Description = “Dues Paid”

Now how do I take those results and compare the MemberID’s to my MembersTable and get the remaining Members? Also if the Lifemember field in the MembersTable is true then they do not need to pay and as a result would not have a ledger indicating that payment was collected.

Please let me know if you think the table structure could have been created differently to suit my purpose of tracking down non payers!!

Thanks for the help in advance!

 

[Remou]

Perhaps:

[tt]SELECT Members.MembersID
FROM Members
LEFT JOIN (SELECT *
FROM Ledger
WHERE Year(DateStamp)=[Enter Year]) AS B
ON Members.MembersID = B.MembersID
WHERE B.MembersID Is Null
AND Members.LifeMember="No"[/tt]

 

[NerdTop72]

I am still stumped...

I am getting no results with the correct criteria.


SELECT TBLMembers.MemID
FROM TBLMembers
LEFT JOIN (SELECT *
FROM TBLLedger
WHERE (DateStamp = ?)) as B ON TBLMembers.MemID = B.MemID)
WHERE (TBLMembers.MemID IS NULL) and (TBLMembers.Lifemember = "no")

some of my records...

TblMembers
MemId LifeMember
JATO no
OIDS no
SAME yes
SEMT no

TblLedger
MemID DateStamp Description
JATO 1/1/2007 Dues
OIDS 1/1/2007 Dues
SAME 1/1/2007 RedCross


So in theory the results should be
SEMT
because they have not yet paid dues and are not lifemembers
I get nothing

Thanks

 

[PHV]

WHERE ([!]B[/!].MemID IS NULL) and (TBLMembers.Lifemember = "no")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NerdTop72]

with that change my results are

JATO
OIDS



Still not correct? the result should be SEMT? I do not understand why I would be getting these results? Then again, I do not understand the query...

If I want the MemID to be null doesnt ask for all results with no memid? I should have gotten no results. How are other databases setup to find out if someone is late on a a reacurring payment?

Thanks




Thanks for the help!

 

[PHV]

And what about something like this ?
SELECT M.MemID, M.Name, M.LifeMember
FROM TBLMembers AS M LEFT JOIN (
SELECT MemID FROM TBLLedger WHERE Year(DateStamp) = Year(Date()) AND Description Like 'Dues*'
) AS P ON M.MemID = P.MemID
WHERE P.MemID Is Null AND M.LifeMember = False

I do not understand the query
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NerdTop72]

Thanks everything did work!
I was battling with my query builders where access 2003 doesn’t like certain () [] and Visual Studios 2005 doesn’t like Joins, If anyone else has had problems with VS Query Builder and can help me out…

Here is the statement you created

SELECT M.MemID, M.lifemember
FROM TBLMembers AS M LEFT JOIN [SELECT MemID FROM TBLLedger WHERE Year(DateStamp) = Year(Date()) AND Description Like 'Dues*'
]. AS P ON M.MemID = P.MemID
WHERE M.LifeMember = [False] AND P.memid IS NULL

Error Source: Microsoft JET Database Engine
Error Message: No value given for one or more required parameters.

This error disappears when I remove M.lifemember from the select statement and final where clause. It also wont work if I use Tblmembers.lifemember or * in its place?

I will be posting this in the VB Forum soon if I have no responses!
Thanks again REMOU & PHV
Thanks PHV for the Helpful site, cleared up my confusion!

 

[PHV]

Is LifeMember really a Boolean field ?
I don't understand why the False keyword is bracketted ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NerdTop72]

VS 2005 Query Builder, it automaticly adds [] to [False] and [Off] after the query would error

It works in VS Query Builder now

SELECT m.memid, m.lifemember
FROM TBLMembers AS M LEFT JOIN
[SELECT MemID FROM TBLLedger WHERE year(datestamp)= '2007' AND Description = 'Dues'
]. AS P ON M.MemID = P.MemID
WHERE p.memid IS NULL AND m.lifemember = 0

Turns out False doesn't work even though my results say False under Lifemember ha ha??? I think I also had some misspellings in my query.

I think it is boolean even though in Access 2003 you have several options for formating
on/off true/false yes/no. I think this is for form design because there isnt a Boolean option in the designer?

Thanks Again PHV!