Access Query for information not in a table - hard to come up with description

[LCUDK]

Hello,

Not sure if this is the right forum but here goes. I am writing a membership database. I have a main table(member)that contains relevant information about the member. It has an autonumber field called entrynumber that is linked to another table called financial. The financial table also has the entrynumber field in a many to one relationship with table (member). The financial table lists any monies paid into the club. Most entries will be dues, but there could be donations, upkeep, etc. In the "member" table I also have a field that lists the members status, good or not good. I need to write a report that lists any member who is "good" and does not have entry in the financial table listed "dues" between certain dates. BAsically I need a way to create a list of members who are in good standing that have not paid their dues. The database isn't in production so I can change just about any element. Having a hell of a time with this one. Any examples or help would be greatly appreciated. IThanks,

Doug

 

[ClulessChris]

Sounds like an unmatched query. I suggest a good starting point is the Access Query wizard.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Andrzejek]

How about this:

2 tables, as you described:[pre]
member financial

entrynumber entrynumber
member_name monies_paid
good/not good X (dues, donations, upkeep)
date_of_entry
[/pre]

First, to get all members who did pay their dues:
[tt][blue]
Select entrynumber From financial
Where X = 'dues'
And date_of_entry between #one_date# and #other_date#
[/blue][/tt]

And then, get those who did not pay:
[tt]
Select * from member
Where entrynumber [red]NOT IN [/red]([blue]
Select entrynumber From financial
Where X = 'dues'
And date_of_entry between #one_date# and #other_date#[/blue]
) AND Good_NoGood = 'Good'
[/tt]

Have fun.

---- Andy

 

[LCUDK]

Andy,

Definitly a SQL noob here but not sure that will work. Consider two years down the line members that are "good" will have multiple table entries for "dues" with the only thing differntiating them being the date.

Doug

 

[Andrzejek]

So if I am a member since Jan of 2011, and I did pay Jan 11 and Feb 11 dues, then did not pay for a few months, and then I did pay Oct 11 dues. Does that make me 'Good' or 'No Good' member?

And wouldn't you apply my Oct 11 dues to Mar 11 (I did not pay Mar dues)?
So I would owe you dues for Apr 11 and on...

Now I am behind for several months, that makes me a 'No Good' member, right?

Have fun.

---- Andy

 

[LCUDK]

The good or not good field is because the club refuses to delete people that have either violated rules or failed to meet some requirement. Their are probably 800 inactive accounts that if it was up to me would be deleted. Dues are paid yearly. So for instance in the financial table I would have in the entryid "1", amount "60.00",daterecieved "1/1/2011", and reasonrecieved "dues". I would also have an entry entryid "1", amount "60.00",daterecieved "1/1/2012", and reasonrecieved "dues".