Query Does Not Work-Selct Members with No Transaction for Selected Years

[dlhappy]

Table 1: tblMembershp

Table 2: tbl MembersTrans

Relationship:

I am a volunteer for a small non-profit organization. I maintain membership records and am also a member of the board of directors. Everyone is an unpaid volunteer. I want to generate a query (MS Access 2007) that will show which members have had no transactions (that is paid membership dues) for membership years 2009, 2010 and 2011. I have been unable to get the query to show any such information.

The database uses two tables, tblMembership (which contains data such as name, address, phone, email, etc.) and tblMembersTrans which records transactions as follows: TransDate, MembershipYear, TransType, and TransPaid (the amount of payment). TransID is an autonumber (Primary Key). MembershipID is the link from tblMemberhship and is an autonumber (Primary Key) in tblMembership. The link is MemberID in tblMembersTrans.

TransDate is the date of a payment, which can be for the current membership year, the past membership year if there was an arrearage, or the for a subsequent year or years. If a payment covers more than one membership year, a separate record is created to record the payments. TransType refers to Single, Family, etc. type of memberships. TransPaid is the amount of payment for a single membership year.

Everything has been working fine except for a new query that I tried to create. The query that I tried to create 1st from another query, as the source, that combines tblMembership fields and tblMembersTrans for years 1998 through 2013, and then I tried again, instead of using another query as the source, I tried creating the query from both tables. In neither case did the new query work properly. What I want is a query that will show all of the selected fields from the tblMembership (name, address, etc.) for those members who have NO transactions for MembershipYear 2009 and 2010 and 2011. The query also is set to only select those records where Status IS NULL and Archive = FALSE. (There are people who enjoy a certain status such that payment is not required. Those archived -- meaning Archive = Yes/true -- are excluded from qryMailingList and thus are not mailed publications. The purpose of the current query that I am trying to create should produce a list of those members who have not paid their membership contribution since the 2008 membership year, who are not archived and who do not enjoy a special status. That list would be used by the board of directors to determine if some should enjoy a special status, or should be archived and no longer be mailed publications.

In the query design view, I set MembershpYear to 2009 and 2010 and 2011. I also set all of the other transaction related fields to IS NULL. I think that perhaps the problem is that in reality MembershipYear will be null for all records where TransID is null. But I don't know how to get around that. I tried all sorts of various combinations but nothing worked, even including 2008 and setting the other years 2009-2011 to IS NULL, thinking that it would at least show those who last paid for the 2008 membership year but not thereafter. Even that did not work.

The query does not work in that it is empty, that is is shows no names, or any other data. It merely has one record line (new).

This is somewhat baffling because I have another query that shows data for those who have never paid anything for any year. For that query, I merely included one field from tblMembersTrans, that is, TransID and in design view, set it to: IS NULL, and it worked to return the selected fields from tblMemberhip for those members who never paid anything for any year. So why can't I get this to work just for selected years?

Thanks.

Allan

P.S. I was going to copy the database and delete the records and create some sample data; however, I was hoping that someone here could merely tell me how to set the query design window so that the query would work.

 

[Galaxiom]

Are you using a LEFT JOIN in the query? An INNER JOIN will show no records because only those with transaction records will be returned by that join.

 

[dlhappy]

Are you using a LEFT JOIN in the query? An INNER JOIN will show no records because only those with transaction records will be returned by that join.

Hi:

I'm sorry but I don't know what those terms mean. What is a left join and how do I do that?
Thanks

 

[Galaxiom]

Right click on the join line in the query designer and select Join Properties. (You have to be very precise with the pointer and it might take a couple of attempts to get the menu.)

Choose option 2 or 3 according to the descriptions provided.

 

[dlhappy]

Right click on the join line in the query designer and select Join Properties. (You have to be very precise with the pointer and it might take a couple of attempts to get the menu.)

Choose option 2 or 3 according to the descriptions provided.

Hi Glaxiom:

Thanks. I tried that but it still does not work when I am trying to show which members had no transactions, but only for a specified year or years.

I already have an old query in the database that shows members who have never paid anything, ever (all years). I imagine that the "join" option was automatically set back then when I used the query wizard to create that query. In that case no year is listed but rather the field TransID is set to IS NULL. Using the join properties, it works under method 2 but not method 3. So it works. But that is for a case where there are no TransID records at all for a member. If in the MembershipYear field I list a year, such as 2009 in the criteria box in design view, no results are returned. Cleary, though, that is an incorrect result, because those who never paid for any year obviously did not pay for 2009.

It will be a bit of work, and I don't have time now, but perhaps I should delete all of the records from the copy of the database (not the original), that I am using to test the query, create a few sample records, and attach the test database to a message/post, here in the forum.

Take care and have a happy New Year!

Allan

 

[boblarson]

You basically need to also create a query to have all years showing, regardless of when someone had a membership. Then you link that using the outer join. You may need to use it with the membership table first and then use that query in another query with the member list joined with an outer join.

 

[dlhappy]

You basically need to also create a query to have all years showing, regardless of when someone had a membership. Then you link that using the outer join. You may need to use it with the membership table first and then use that query in another query with the member list joined with an outer join.

Hi Bob:

Thanks. I tried doing the above several times and several ways; probably, I messed up, or possibly, if I select a year and there is no transaction record for that year, it won't show the data that I want.

Anyway, the point is now somewhat moot because I used a less elegant way to easily accomplish the result that I want. What I did was add a field to the main table (tblMembership) called LastMembershipYearPaid. I created a new form that, for each member, shows all data from the tblMembership and also tblMemberTrans. As there are a total of 286 members, it was not too difficult to manually post that one field for the last year a payment was made. Now I am able to create queries showing the data that I want.

Again, though, I want to thank you and Galaxiom for the support offered.

 

[Galaxiom]

Anyway, the point is now somewhat moot because I used a less elegant way to easily accomplish the result that I want. What I did was add a field to the main table (tblMembership) called LastMembershipYearPaid.

That is not a wise strategy as it breaches normalization. That piece of information is now recorded in two separate places in the database.

Now you have the potential for conflicting information and the need to manage the synchronisation.

You must still have a problem with the type of join you are using.

 

[dlhappy]

Hi and thanks for your replies.

I have tried all of the suggestions, as best I understood them, but was unable to get the desired result, except when I created a new field in the tblMembership and copied redundant data to it from the tblMembersTrans via the frmMembership which has all fields from both tables. The new field lists ONLY the last memberhip year where there was a payment, for each member.

That way, I can obtaina query that shows the correct results that I want, namely, those members who are not archived, do not have any special status, and who have a transaction posted for membership year 2008 but no transaction for any subsequent year.

qryQUERY2-WorksButFromNon-Normalization-RedundantData

I appreciate what was said, that is that such a method violates normalization, etc., but it is the only way that I was able to obtain the desired result.

As for the suggestion about first creating a query that already had all data for all years from both tables, I already had such a query, namely, qryMembershipAndMembersTransAllYears. Except that it only has years for a member where there was a transaction for that year. Before I had added the redundant data to a new field, LastMembershipYrPaid (a text field in case I wish to add a short text comment to the year) in the tblMembership, I also tried using qryMembershipAndMembersTransAllYears as the source for the new query that I wanted. This also did not produce the desired result.

I deleted all of the old query tries, and and recopied the database, and again tried using the two tables and all three of the various join methods.

None worked. The results show no data, and appear in the below queries:

qryQUERY1-from2tablesDoesNOTwork-Join1
qryQUERY1-from2tablesDoesNOTwork-Join2
qryQUERY1-from2tablesDoesNOTwork-Join3

I think that the problem is that where there is no record for a member for a given year, that year does not appear at all in the transaction record. The years are not fields, but rather parts of a record when there is a transaction. When there is a transaction (payment) the year appears in in the field, MemberhipYear. So, the following situation exists. If a member never paid anything (there are some members who enjoy a special status, such that they are not required to pay anything), then, if I create a query where transID is null, I get a proper result. However, if I try to prepare a query where for example, 2008 is not null and (2009 is null) and (2010 is null) and (2011 is null) no result is returned, because 2009, etc. is NOT a field and so it is either being treated as such and not found, or 2009 is not being found at all since there is no entry or record in MembersTrans for a member who had no transaction for 2009.

I think that the query would only work if I were to post a transaction for ALL members, including those who had no transaction, and merely input the Membership Year, 2009 (for example) and amount paid 0.00. However, I don't want to have to post a ghost type transaction where there is actually no transaction.

What I have done is sanitized the DB by eliminating last names, street addresses, email addresses, telephone numbers, most memo field notes, etc.

If someone would not mind, I would appreciate it if you could extract the test db from the uploaded zip file, create the query that I want and upload/attach it to a post/message reply. I would then copy it to the working database, and eliminate the redundant, non-normalization field from tblMembership and have a proper DB.

Btw, originally, about a year or so ago, I had only one table and a field for each year. However, I was told that I needed a relational DB and should not be adding a field for each year, but rather have a transaction type register with the year merely being posted as part of the transaction. That advice has worked very well; however, under the field for each year method, I would have been able to create the query easily, I think.

Anyway, thanks for the previous help, and any additional help in actually creating the query for me in the attached database. Just to recap: what I want is a query that shows the all information for tblMembership for those members for whom status is null, archive is false and from tblMembersTrans, 2008 is not null and there are NO transactions for any MembershipYear subsequent to 2008.

 

[dlhappy]

I was just wondering if there was no reply because no one tried to work with the database I attached in the immediately previous post, or because no one was able to get it to work the way suggested in prior messages in this thread.

Thanks.

 

[Jack Mack]

I am sure we can create a query that will work correctly if you will post the actual table structures. It would also be a good idea to post the queries that you already tried.

 

[dlhappy]

I am sure we can create a query that will work correctly if you will post the actual table structures. It would also be a good idea to post the queries that you already tried.

Hi. I'm not sure by table structures if you mean the properties, etc. of each field. However in message # 9, above, I have attached the database (sanitized to eliminate the members' last names, addresses, etc.) in a zip file. In that file I had tried the query again, using all 3 join methods (the original queries that failed had been deleted). If you look at the attached database (message #9, above) you will see the failed queries, with these names:

qryQUERY1-from2tablesDoesNOTwork-Join1
qryQUERY1-from2tablesDoesNOTwork-Join2
qryQUERY1-from2tablesDoesNOTwork-Join3

The only query that works is the one from the newly added field to the tblMembership. That field, though, I was told violates database normalization and is redundant because all I am really doing is copying information already appears in tblMembersTrans. Anyway, that query, in the attached database, is: qryQUERY2-WorksButFromNon-Normalization-RedundantData

Thanks.