Listing Null entries in a second Table

[jcbhydro]

Good Morning from the UK,

I have a Membership List Table with a calculated field entitled Member Name.

The second table entitled Groups contains only 46 records each of which has up to 30 Fields also containing member names.
I Have written a query to establish which of the members from the single field of the first table do NOT featured in any of the fields and records of the second table. In the Query Design View I have listed 'Member name' from Table 1 followed by all the relevant member fields from Table 2, carrying the criteria 'Is Null'.

When I run the query, it merely lists all the members from Table 1.

Any suggestion to assist me in my greenhorn efforts would be much appreciated.

 

[Galaxiom]

You need to revisit the data structure. A table with names in thirty fields is likely to be wrong.

Typically all the names would be in one field and another field would be included to indicate the particular thing about them that originally separated them into the different fields.

Most of the problems querying come from inappropriate structure and disappear when that is fixed.

Tell us more about your tables.

 

[jcbhydro]

Many thanks for such a prompt response.

In response to your request for details of the 2 Tables;

Table 1 is a membership list with First & surnames, addresses, 'phone numbers, email address and several other fields such as Subscription data, newsletter requirements, etc.

Table 2 consists of 46 different Interest Groups, and the fields associated with each group are Leaders name plus up to 30 members of each Group. Some groups have only 5 or 6 members, others up to 30. Each Group member is a separate field.

My requirement is to write a query to produce a Report which will list out the 40 or so members of Table 1 who are not members of any of the Groups of Table 2.

The 'Member Name' field in Table 1 is a calculaated field being a concatenation of the First & Surname Fields. The name fields in table 2 are in the same format, but are not calculated - they are derived from an input Form.

The raw data for these 2 tables was imported from 2 Worksheets of an Excel Spreadsheet. In the Excel version I was able to achieve the task listed above, but I haven't mastered the technique to achieve the same result in Access.

Regards,

jcbhydro

 

[jcbhydro]

Many thanks for the response on this subject.
I had assumed that the structure for Table 2 (Groups)was suspect, but was unsure as to how to reconfigure it.
Are you suggesting that each group should have a separate field titled with the Group Name, with the members being records for each field. If so, how would one allocate an ID for each Group.

Maybe that is not the restructuring that you were visualizing, in which case an elaboration of your views would be much appreciated.

Regards,

jcbhydro

 

[jcbhydro]

Dear Pat H,

Many thanks for your useful suggestion. I can see the logic of the proposed re-structuring.
However, I have a few supplementary questions before I embark on the re-modelling;
a) I currently have a PK for the Table 1 (Members) on the member ID. This is an inherited database and the ID is not autonumbered. Attempts to change an existing series to Autonumber produces a 'no can do' message. I assume that number format is OK, but doesn't prevent duplicates.

b) Similarly, the Group ID is PK currently a text file, ie A1, A2, B1, B2, B3, etc. Again there is no protection against duplicates.

c) The existing Group Member Entry Form (to be abandoned) includes a drop down list of members names from tbl1, to aid unique selection, but does not preclude entering a non-member. I don't see how the proposed structure will cater with non-members. Would a 3rd field in tbl3 be a method of dealing with this. I would propose a drop down list of Group Names in a new Group Member Entry Form.

d) Am I correct in assuming that there is no merit in using the tbl3 PK as a FK in the other tables.

Your further thoughts on my ideas would be much appreciated.

Jcbhydro

 

[jcbhydro]

Many thanks for your very helpful suggestions which are duly noted.

You have greatly assisted me in putting together a more secure system.

Regards,

jcbhydro