This one is giving me problems

[scottk]

What I am ultimately attempting to do is create a list of chapter members and officers without duplicating.

Let me explain how the tables are organized. There are 5 tables pertinent to this process. Please note that I am not able to change the structure of the tables without destroying bunches of other functions. The obvious fields relate to each other.

Personal Data - contains ID, Name, Address.
Category Link - Contains ID and CatID
Categories - contains CatID, CatName
Office Link - contains ID, and OfficeID
Office - Contains Office, OfficeID

If the person is a Chapter Member, they are associated with the Category "Chapter Member" through the Category Link table. If the person is an officer in his/her chapter, they are associated with their office through the Office Link table. Of course all chapter officers are chapter members.

What I have attempted so far is to make two queries and then join them with the UNION statement in SQL.

The first query contains all 5 tables an the following fields:

Categories
----------
Category
Criteria = "Chapter Member"

Personal Data
-------------
*All

Office
------
Office


The second query contains 3 tables and 1 defined field so that the same number of columns appear

Categories
----------
Category
Criteria = "Chapter Member"

Personal Data
-------------
*All

Office:Null
-----------

Of course the first query finds only chapter officers and the second query finds all chapter members. When joined together the result is that if a person is an officer their name is duplicated. How can I eliminate this duplication? I know I can eliminate it by not showing the Office field but I need to know what office the person holds. Any suggestions?

Thanks in Advance for your help.

 

[Pat Hartman]

I know you are reluctant to change this design however do you really have many-to-many relationships between people and categories and between people and offices? Or, can one person belong only to one chapter at a time and one person hold only one office at a time? If rour business rules are the latter, then you need to change your table design. You would still have Personal data, categories, and offices but you would not have the link tables. Instead, the CatId and OfficeId would be added to the Personal data table.

You can't do what you are attempting in a single query because you have two separate many-to-many relationships. You'll need to do it in a report with nested subreports.

Main report - Category
Sub Report - Personal Data
Sub Report on Sub Report - Office

 

[scottk]

There definately must be a many to many relationship between categories and personal Information. "Chapter Member" is just one of several categories a person can belong to and they can belong to several at once. Really for the Officer section I could convert it to a one to many realtionship (Although I didn't mention it before but the Office Link table also has a Term field so that history is preserved and someone could of course be Vice President or their Chapter in 2002, President in 2001, and President again in 2002. If it would help anything, I could do away with the Office table and change Office ID to Office in the Office Link table.

I have been playing aroun and I have come up with the following sql:

SELECT DISTINCT [Personal Data].*, [Category Types].[Category Description], [Office & Chair Link].[Office/Chair], [Office & Chair Link].Term
FROM ([Personal Data] INNER JOIN ([Category Types] INNER JOIN [Category Link] ON ([Category Types].[Category ID] = [Category Link].[Category ID]) AND ([Category Types].[Category ID] = [Category Link].[Category ID])) ON [Personal Data].ID = [Category Link].[Member ID]) INNER JOIN ([Office and Chair Lookup] INNER JOIN [Office & Chair Link] ON [Office and Chair Lookup].[Office/Chair] = [Office & Chair Link].[Office/Chair]) ON [Personal Data].ID = [Office & Chair Link].ID
WHERE ((([Category Types].[Category Description])="Chapter member"));
UNION SELECT [Personal Data].*, [Category Types].[Category Description], Null AS [Office/Chair], Null AS [Office Chair]
FROM [Personal Data] INNER JOIN ([Category Types] INNER JOIN [Category Link] ON ([Category Types].[Category ID] = [Category Link].[Category ID]) AND ([Category Types].[Category ID] = [Category Link].[Category ID])) ON [Personal Data].ID = [Category Link].[Member ID]
WHERE ((([Category Types].[Category Description])="Chapter member") AND (([Personal Data].ID) Not In (Select ID From [Office & Chair Link])));

Like I was doing before, this divides officers and regular members. To find the office it, of course, looks for the ID in the Office and Chair Link Table. To find the regular members it checks to see if the ID is not in the Office and Chair Link Table. While I am not getting as many duplicats as before I am still getting some and its because of the preservation of office history. If the person has been in an office for 2 or more terms they will of course appear twice. My first instinct was to just specify the term, but I then encounter the problem of the person being eliminated if the person was an officer but is no longer. Example Bob was president last year. He is no longer a president but is still a chapter member. He would not be listed as an officer b/c he is not in the current term. He would not be in the non officers part of the query b/c his ID # is still in the office and chair link table from his history.

Any thoughts?

As always I appreciate any help that you can provide.

 

[KKilfoil]

Just a thought.

In your OfficeLink table, you could add fields for DateInstalled and DateRelieved (date format).

In your query, only select the records for Office link where DateRelieved is null (i.e. the person presently holds that office).

Just a personal preference, but you may want to use multiple smaller queries in series to make it easier to 'troubleshoot' when your output is not what you want.

{edited for typos}

[This message has been edited by KKilfoil (edited 01-28-2002).]

 

[Pat Hartman]

I told you what the problem is -

"You can't do what you are attempting in a single query because you have two separate many-to-many relationships."

Although the category table and the office table are both linked to personal data, they have no relationship to each other. If you attempt a query including all of these tables, you end up with something akin to a cartesian product. ie Rows of tableA * rows of tableB * rows of tableC. For every instance of a row in tableC, the rows of tableB are duplicated, for every row of that set, the rows of tableA are duplicated.

I'm sure that there is some complicated set theory mathmatical proof that will explain this. I'm sorry I can't do better. It works the way it works for a reason.

Use a report to solve the problem.

 

[scottk]

Thanks for the help Pat, the report w/ nested subreports worked perfectly. I was so insistent on queries earlier b/c I was hoping to use the same query for several different reports but oh well, what is, is.

Thanks again.