Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

help with query?

I need a little help with sql query.

I have the tblAuthor(authorID,surname,givenname)
tblAssociation(associationID, publicationID,AuthorID)

I am trying to list coauthors here of given authorID.

Authors are tied to publication.

select authorID,surname,givenname from tblauthor a  inner join tblAssociation b on
a.authorid=b.authorid where b.publicationid in (
select publid from association where authorid=xxx)

Is that correct?
0
dkim18
Asked:
dkim18
  • 4
  • 3
2 Solutions
 
käµfm³d 👽Commented:
I don't think you need the inner query. Try:

SELECT auth.givenname, auth.surname
FROM tblAuthor auth
INNER JOIN tblAssociation asoc
ON auth.authorID = asoc.authorID
WHERE asoc.publicationID = xxx

Open in new window

0
 
dkim18Author Commented:
I am providing the authorid.
0
 
dkim18Author Commented:
With given authorid, I am trying to find any other authors who are tied to that publicationid.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
try this query

select a.authorID,a.surname,a.givenname, b.associationID
from tblauthor a  inner join tblAssociation b on
a.authorid=b.authorid where authorID=xxx
0
 
Rey Obrero (Capricorn1)Commented:
select a.authorID,a.surname,a.givenname, b.associationID,b.publicationid
from tblauthor a  inner join tblAssociation b on
a.authorid=b.authorid
where a.authorID=xxx
0
 
dkim18Author Commented:
Your sql finds all the publication for a search author.


I am trying to find all other authors who worked on that same publication with given an authorid.

ex) publication1, author1,author3,author4
      publciation2, author1,author2,author5
      publication3, author2,author1,author7
      publication4,author6,author7

 tblassociation(1,1,1)
 tblassociation(2,1,3)
 tblassociation(3,1,4)
 tblassociation(4,2,1)
 tblassociation(5,2,2)
 tblassociation(6,2,5)
 tblassociation(7,3,2)
 tblassociation(8,3,1)
 tblassociation(9,3,7)
 tblassociation(10,4,6)
 tblassociation(11,4,7)
0
 
dkim18Author Commented:
This brings back what I want.

How do I tie this up to the author main form?
If author main form's id is 3, how do I pass this to the subform whose data source is this query?





SELECT Affiliation.AuthorID, Author.Surname, Author.Given_Name
FROM Author INNER JOIN Affiliation ON Author.AuthorID = Affiliation.AuthorID
WHERE (((Affiliation.PublicationID) In (SELECT Affiliation.PublicationID FROM Affiliation WHERE Affiliation.AuthorID=[xxxx])));
0
 
Rey Obrero (Capricorn1)Commented:
you are posting different table names from your original post and up to your last post.

this is not a good way to present your problem..

anyway, see this link to help you get your main/sub form working


http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx?CTT=3
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now