query members in tables and other organization

[iznubadd]

Please help. what i would like to make is a report regarding name of students who is a member of a centain organization and at the same time list the other organization where he/she affiliate

here are my tables
tbl.members :
id
givenname
middleinitial
familyname
orgID
remarks

tbl.organization :
orgID
name
incharge
phone

what i would like to make is a report regarding name of students who is a member of a centain organization and at the same time list the other organization where he/she affiliate

sample:
memberID = 1 ; orgID = 1
memberID = 1 ; orgID = 2

memberID = 2 ; orgID = 1
memberID = 2 ; orgID = 3

memberID = 3 ; orgID = 2
memberID = 3 ; orgID = 3
memberID = 3 ; orgID = 4

memberID = 4 ; orgID = 1

memberID = 5 ; orgID = 6
memberID = 5 ; orgID = 3
memberID = 5 ; orgID = 4

memberID = 7 ; orgID = 1
memberID = 7 ; orgID = 3
memberID = 7 ; orgID = 4


when running the query with orgID = '1' this will give a result:

memberID : 1 ; orgID : 1
memberID : 1 ; orgID : 2 << because he also belong to orgID=1

memberID : 2 ; orgID : 1
memberID : 2 ; orgID : 3 << because he also belong to orgID=1

memberID : 4 ; orgID : 1

memberID : 7 ; orgID : 1 << because he also belong to orgID=1
memberID : 7 ; orgID : 3 << because he also belong to orgID=1
memberID : 7 ; orgID : 4 << because he also belong to orgID=1

 

[Uncle Gizmo]

This sample is also your answer:

Sample:
memberID = 1 ; orgID = 1
memberID = 1 ; orgID = 2

memberID = 2 ; orgID = 1
memberID = 2 ; orgID = 3

memberID = 3 ; orgID = 2
memberID = 3 ; orgID = 3
memberID = 3 ; orgID = 4

memberID = 4 ; orgID = 1

memberID = 5 ; orgID = 6
memberID = 5 ; orgID = 3
memberID = 5 ; orgID = 4

memberID = 7 ; orgID = 1
memberID = 7 ; orgID = 3
memberID = 7 ; orgID = 4

You need a new intermediary table between your members and your organizations usually referred to as a “many to many table", and its structure would be exactly the same as the sample you provide:

Field memberID; Field orgID

And then your member ID Field would be filled with the members ID, and the corresponding organisation field in exactly the same manner as shown in your sample.

 

[iznubadd]

i run the query:
SELECT *
(SELECT id
FROM members m
WHERE orgid = '1') m1
INNER JOIN members m2
ON m1.id = m2.id
INNER JOIN organization o
ON m2.orgid = o.orgid
ORDER BY m2.id, m2.orgid;


---Here is the error message---
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT id
FROM members m
WHERE orgid = '1') m1
INNER JOIN members m2
ON m1.id =' at line 2

 

[wiklendt]

what Uncle Gizmo is saying, is that you need a separate, third table called, say, tblMemberships. this is called a junction table, as it creates a junction between two other tables - in effect, this allows a 'many-to-many' relationship (i.e., one member can have a membership with many organisations and one organisation can also have many members)

this table would have minimum three fields:

tblMemberships
----------------
MembershipID (PK)
OrganisationID (FK)
MemberID (FK)

in your Member form, then, (where you input data for members) you would have a SUBform called "sfrmMemberships". this would ideally be presented in datasheet view. you then link child and parent ID's via the properties of the subform control. (edit: this linking of parent and child allows access to AUTOMATICALLY filter all the memberships for the given member currently displayed in the form.)

make the OrganisationID field on the subform a dropdown box whose source draws from the organisation table (make sure the bound field of the dropdown box is the primary key of the organisation table).

when you then go to form view, you can then add and delete organisations to a member's membership (or visa versa - organisations with all their members - with another form/subform setup).

- once you have the data entered in this manner, you can have a peek at the table itself - the data will look remarkably similar to the combinations of ID's you posted in your first post.

a report would then be setup in a similar manner to the form - with a main report (data from the member table) and a subreport (data from the 'junction' table).

HTH