Can't find good SQL query

[PL]

Hi,
I have 2 simple tables, 'Accesses' and 'Partners'.

Partners :
- Id, primary numeric key
- Name of the partner
- other informational fields

Accesses :
- Id, primary numeric key
- some informational fields
- 'CreatedBy', which is a partner Id
- 'SupportBy', which is also a partner Id
I want to display a table of all accesses, with for each access the name of
the creator partner and the name of the support partner.
How can I do that in a single SQL query ?

for example :
Partners :
1 Bob
2 Bruce

Accesses :
1 Access1 CreatedBy=1 SupportBy=2
1 Access2 CreatedBy=1 SupportBy=1

Expected display :

Access1, created by Bob, supported by Bruce
Access2, created by Bob, supported by Bob

thanks
P.

[Allen Browne]

Firstly, create the relations between the 3 tables if you have not already
done so.
1. Choose Relationships on the Tools menu.

2. Add the Accesses table and 2 copies of the Partners table.
Access will alias the 2nd one as Partners_1.

3. Drag Partners.Id onto Accesses.CreatedBy.
Check the box for Referential integrity, and create.

4. Drag Partners.Id onto Accesses.SupportBy.
Check the box for Referential integrity, and create.

5. Close the Relationships window, saving the view.

Now you can do the same thing in the query, using 2 copies of the Partners
table.

1. Create the query with Partners and Accesses.
Access will show 2 lines joining the tables.
Delete the line to Accesses.SupportBy.

2. Double-click the remaining join line.
Access offers a dialog with 3 options.
Choose the one that says:
"All records from Accesses, and any matches from Partners."
This is called an outer join.

3. Add a 2nd copy of Partners to the query.
This time, add a join from Partners_1.ID to Accesses.SupportBy.

4. Double-click this join line, and make it an outer join as before.

5. Now you can now add the fields from all tables to the query, to get the
names and output you desire.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PL" <no****@nomail.comwrote in message
news:44***********************@news.free.fr...

>
I have 2 simple tables, 'Accesses' and 'Partners'.

Partners :
- Id, primary numeric key
- Name of the partner
- other informational fields

Accesses :
- Id, primary numeric key
- some informational fields
- 'CreatedBy', which is a partner Id
- 'SupportBy', which is also a partner Id
I want to display a table of all accesses, with for each access the name
of
the creator partner and the name of the support partner.
How can I do that in a single SQL query ?

for example :
Partners :
1 Bob
2 Bruce

Accesses :
1 Access1 CreatedBy=1 SupportBy=2
1 Access2 CreatedBy=1 SupportBy=1

Expected display :

Access1, created by Bob, supported by Bruce
Access2, created by Bob, supported by Bob

[PL]

Great!
thanks a lot Allen.
P.

"Allen Browne" <Al*********@SeeSig.Invalida écrit dans le message de
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

Firstly, create the relations between the 3 tables if you have not already
done so.
1. Choose Relationships on the Tools menu.

2. Add the Accesses table and 2 copies of the Partners table.
Access will alias the 2nd one as Partners_1.

3. Drag Partners.Id onto Accesses.CreatedBy.
Check the box for Referential integrity, and create.

4. Drag Partners.Id onto Accesses.SupportBy.
Check the box for Referential integrity, and create.

5. Close the Relationships window, saving the view.

Now you can do the same thing in the query, using 2 copies of the Partners
table.

1. Create the query with Partners and Accesses.
Access will show 2 lines joining the tables.
Delete the line to Accesses.SupportBy.

2. Double-click the remaining join line.
Access offers a dialog with 3 options.
Choose the one that says:
"All records from Accesses, and any matches from Partners."
This is called an outer join.

3. Add a 2nd copy of Partners to the query.
This time, add a join from Partners_1.ID to Accesses.SupportBy.

4. Double-click this join line, and make it an outer join as before.

5. Now you can now add the fields from all tables to the query, to get the
names and output you desire.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PL" <no****@nomail.comwrote in message
news:44***********************@news.free.fr...

I have 2 simple tables, 'Accesses' and 'Partners'.

Partners :
- Id, primary numeric key
- Name of the partner
- other informational fields

Accesses :
- Id, primary numeric key
- some informational fields
- 'CreatedBy', which is a partner Id
- 'SupportBy', which is also a partner Id
I want to display a table of all accesses, with for each access the name
of
the creator partner and the name of the support partner.
How can I do that in a single SQL query ?

for example :
Partners :
1 Bob
2 Bruce

Accesses :
1 Access1 CreatedBy=1 SupportBy=2
1 Access2 CreatedBy=1 SupportBy=1

Expected display :

Access1, created by Bob, supported by Bruce
Access2, created by Bob, supported by Bob