DISTINCT selection in a One to many relation

[Flytkjaer]

Hi

I have members DB to keep track of members of a club.

Case: When someone becomes a member they are entered as members. Until they leave the club they can manage different types of functions. Like President, secretary or whatever.
I have the contact in one tabel and the functions in another tabel. in a one to many relation.

In my search form i would like to have all entries from contact tabel listed with the possibility to seach member functions as well. but they must only be listed once in my result, and the "Slutdato" = (end date) must not be a criteria.

SELECT Person_Kartotek.Medlemsnummer, Person_Kartotek.Fornavn, Person_Kartotek.Efternavn, Person_Kartotek.Kaldenavn, Medlemsdatoer.Medlemstype, Person_Kartotek.Mellemnavne, Person_Kartotek.Adresse, Person_Kartotek.Postnr, Person_Kartotek.By, Person_Kartotek.[Telefon Privat], Person_Kartotek.[Telefon Mobil], Person_Kartotek.[E-mail], Person_Kartotek.Fødselsdag, Person_Kartotek.Billede, Person_Kartotek.Arbejdsgiver, Person_Kartotek.Stilling, Person_Kartotek.[Telefon arbejde], Person_Kartotek.Note, Medlemsdatoer.Startdato, Medlemsdatoer.Slutdato, Medlemsdatoer.Note
FROM Person_Kartotek INNER JOIN Medlemsdatoer ON Person_Kartotek.Medlemsnummer = Medlemsdatoer.Medlemsnummer
WHERE (((Person_Kartotek.Medlemsnummer) Like "*" & [Forms]![Search]![Search2] & "*") AND ((Medlemsdatoer.Slutdato) Is Null)) OR (((Person_Kartotek.Fornavn) Like "*" & [Forms]![Search]![Search2] & "*") AND ((Medlemsdatoer.Slutdato) Is Null)) OR (((Person_Kartotek.Efternavn) Like "*" & [Forms]![Search]![Search2] & "*") AND ((Medlemsdatoer.Slutdato) Is Null)) OR (((Person_Kartotek.Kaldenavn) Like "*" & [Forms]![Search]![Search2] & "*") AND ((Medlemsdatoer.Slutdato) Is Null)) OR (((Medlemsdatoer.Medlemstype) Like "*" & [Forms]![Search]![Search2] & "*") AND ((Medlemsdatoer.Slutdato) Is Null))
ORDER BY Person_Kartotek.Fornavn;

Any good advice in here??
I attach my DB but it is in Danish, hope it doesn't scare anyone off :-)

[orange]

In your terms a Member is a Contact. You also say they can manage Functions, and I realize you are Danish and perhaps the terminology is a little different. Can a Member hold more than 1 position/function at a time? For example could the President also be the Communications Leader or whatever? Can a Position/Function be vacant?

What exactly is the one to many relationship?

Have you considered 3 tables

Members
-memberId PK
-memberName
-JoinDate
-LeaveDate
-other member specific info



Functions/Positions
-functionId PK
-functionName

MemberFunction

MFId PK
-memberId +
-functionId + these 2 fields make a unique index
-TermStartDate
-TermEndDate

Just some thoughts

[Flytkjaer]

Thanks for your reply.

It goes like this

Contact:
PK - Id - Int
PK - Member number - text
Name - text

One to many

Functions dates:
PK - Id - Int
Membertype - text
StartDate - Date
EndDate - Date

One to many

Functions
PK - Id - Int
Function

One member can have many functions, but only one at the time.
In my seach form i would like to search for all members even if they are not members anymore. As it is now it is not a problem since it is not a criteria that end date filled or not. but i would like to be able to search for the function as well, but my result list should onle list a member once in the list.
Let me try to explain.
I can in one textbox serach in the following fields: firstname, Lastname, Nickname, function.
In the result list i get some thing like this as an exsample:
Fname1, Lname1, Nname, Member (startdate and enddate)
Fname1, Lname1, Nname, President (startdate and enddate)
Fname1, Lname1, Nname, Member (startdate)
Fname2, Lname2, Nname, Member (startdate and enddate)
Fname2, Lname2, Nname, Secretary (startdate)
Fname3, Lname3, Nname, Member (startdate)
Fname4, Lname4, Nname, Member (startdate)
I would like to have only one listning and maintain the abillity to search the function. i know this is the one who makes the trouble.

Does this make sence?

[orange]

I find the post confusing.

What does this mean

Functions:
PK - Id - Int
Membertype - text
StartDate - Date
EndDate - Date

One to many

Functions
PK - Id - Int
Function

and

Contact:
PK - Id - Int
PK - Member number - text
Name - text

[Flytkjaer]

(Edit: i forgot to mention that it was my tabel layout in the previous post)

well i think it is more or less descriped as many would in here ;-)

Tabel
PK = Primary Key
Fields and type.

Relation type

Tabel
PK = Primary Key
Fields and type.

Otherwise i would put your attention to my first post, there are attached the database, there you might be able to see what i'm trying to descripe.
Open the Search form, and enter ex: "medlem" in the search field at the top of the form. or scroll down in the list field.

Thanks

[orange]

I have ac 2003 and can not open your accdb file.

[Flytkjaer]

Hi Orange

well we both could have done something here, i did not look and you didn't tell from the beginning.

I converted my DB to mdb 2003 for you.

[Flytkjaer]

Hi Orange

Please do not hecitae to write if there are any questions.

[Flytkjaer]

Hi Orange

Maybe i should clarify that. a member can be a member change to be a member of the board and leave the club.
Then after awhile re-enter the club as a member again.

If the manager of this DB would like to search for all members who have been presidet (Formand in Danish) they should be listed in the list. even if they are not members anymore.
And from time to time we invite old members to a party, therefore it is convinient to have them listed in the same list.

[orange]

I get errors when I open the db.

Can't find file. (I think it is an image .png)
Missing references.

[Flytkjaer]

Hi Orange

I have now removed any reference to all kind of pictures in the DB. I have made some translations, just to make it easier to navigate across the objects.

[Flytkjaer]

Sending a bump.

[Flytkjaer]

Does anyone have a soluotion to my problem?

[orange]

I looked at your database again. Some of the translations helped, but the forms and queries are confusing at best.

I found that
- you have compound primary keys on tables
-the tables are not normalized
===

I have renamed the Id autonumber PK fields

So now it looks like the attached.
In my copy I created 2 update queries
1) to add the memberId to Medlemsdatoer as FK
2) to add the membertypeId to Medlemsdatoer as FK

So you have People --->PositionOccupiedWithTimestartAndStop(Medlemsdatoe r)<----Functions

People table is fine
Medlemsdatoer does not need MemberNo or MembersType (they can be derived)
Medlemstype does not need MemberId


I hope this is helpful.