By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Can't find good SQL query

P: n/a
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.
Aug 14 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Aug 14 '06 #2

P: n/a
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


Aug 14 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.