Query Doesn't Return Data 2

[muriel]

I've searched the forum regarding why a query wouldn't return data. So far, I have not been able to solve my problem. I know there's probably an easy solution that I'm overlooking.

The problem is when I'm querying tables that have many-to-many relationships, where I've created join tables.

Here's an example:
Author_tbl
AuthorID autonumber/primary key
AuthorName text
OrganizationID number - foreign key to Organization_tbl

Organization_tbl
OrganizationID autonumber/primary key

AuthorOrganization_tbl (join table)
AuthorID number/primary key (foreign to Author_tbl)
OrganizationID number/primary key (foreign to Organization_tbl)

The query needs to produce:
Authors and all organizations associated with them (there will definitely be more than one - thus the need for the many-to-many relationship)
Organizations and all Authors associated with them (same reason as above for many-to-many relationship).

I've tried a few different queries, but getting no data. MAIN QUESTION: Should I be using the Author_tbl and AuthorOrganization_tbl OR the Author_tbl and Organization_tbl to design the query?

If anyone has any insight, I'd really appreciate it. I've been getting help from another forum (Tables and Relationships), but I can't seem to get this problem worked out.
Thank you.

 

[GoDawgs]

First off you should not have the Organization ID in the Author table...that's the point of the intermediate table that you created. Otherwise how do you decide which organization ID to put in there since it's many to many?

The query...do this in the design view in Access...bring in all 3 of the tables that you talked about. When you do this you should have a link from the Organization table to the AuthorOrganization table, and then another link from the AuthorOrganization table to the Author table. If that's not the case (meaning you haven't set up the relationships in the relationship window) then drag the links yourself. Now if you just bring down the fields you want to see in the results that should be all you need to do. Hope that helps.

Kevin

 

[willir]

muriel

I am back. Let's say that the recent infestation of viruses has made things a little more challenging.

I beleive you have decided that an author can only belong to one organization. If so, don't use the AuthorOrganization_tbl table. Instead, use the OrganizationID field in the Author_tbl to link authors to an organization.

Something like...
SELECT Author_tbl.*, Organization_tbl.*
FROM Organization_tbl INNER JOIN Author_tbl ON Organization_tbl.OrdID = Author_tbl.OrgID;

Should work.


If you have decided that an author can belong to many organizations and an organization can have many authors, then you do need the AuthorOrganization_tbl table, and you do not want to use the field OrganizationID in the Author_tbl.

Then the query created by the query wizard looks like...

SELECT Author_tbl.*, Organization_tbl.*
FROM Organization_tbl INNER JOIN (Author_tbl INNER JOIN AuthorOrg_tbl ON Author_tbl.AuthorID = AuthorOrg_tbl.AuthorID) ON Organization_tbl.OrdID = AuthorOrg_tbl.OrgID;

NOTE: You may have to go back to the relationships design view to correct the relationships.

Does this help?

Richard

 

[muriel]

First, I really appreciate all your help and patience, especially Richard, who has gone above and beyond the call of duty in helping me.

1) I've determined that the relationship between Authors and Organizations is many to many.
2) I took the OrganizationID out of the Author_tbl.
3) I ran the query listed in your response for the many to many relationship (the second query you listed). (I want to get Authors and all Organizations associated with them.) The SQL is exactly as you wrote above. Here's what I get:

AuthorID AuthorName OrganizationID Org Name
(autonumber) (autonumber)

However, there's no data in it, even though I have data in both the Author and the Organization tables.

Any thoughts? Once again, I really appreciate your help and I'm trying to make my questions clear, but I know I'm not always doing that. Thank you.

 

[GoDawgs]

Is there any data in the intermediate table? That table should hold the IDs of authors and organizations they are tied too...otherwise the query won't have any results.

 

[muriel]

There's nothing in the AuthorOrganization_tbl except 0s. How do I make sure the IDs are being stored in the AuthorOrganization_tbl? In other words, when data goes into the Author and Org tables, respectively, record by record, how do the IDs get stored in the intermediate table?

Thanks again - I really appreciate it.

 

[GoDawgs]

You actually have to get the data in there...that means you have an Author form that has a subform where you pick what organizations he/she are in (the subform would have the AuthorOrganization table as the recordsource and would have a combo box on it that would allow you to pick the organization but store the organization ID in the table)...or the other way around, and organization form that allows you to pick the authors associated with them. You can do this by hand too if you need to, just go to the intermediate table and put in the IDs for the authors and their organizations. Hope that helps.

Kevin

 

[muriel]

That helps a lot!! Thank you.

One followup - do you see any way I can have all of these fields on one form? I've been instructed to make the data entry process as fast and as simple as possible.

Thanks again.

 

[GoDawgs]

I think your best bet would be a form for Authors and a form for Organizations...it would be hard to get both on the same form. Then have the subforms we talked about above on each of them if you want...that way people can add authors to organizations or organizations to authors...that would make things easier.

Kevin

 

[muriel]

Thanks for all your help - I'll give it a go.

 

[willir]

muriel

Both these guys a right, and silly me about assuming you knew what I meant about entering some test data. You will have to create some forms to enter the data...

A hands-on Access book may be a good investment. Also, some of the Tek-Tips go over some of this...
thread700-632820 what I meant was as follows...

Author
1 Terry Goodkind
2 Terry Brooks
3 Ursala LeGuin
4 R. A. Salvatore

Publications
1 DelRay
2 TOR
3 Wizards of the Coast

Author x Publication
AuthorID PubID
1 1 - Terry Goodkind, DelRay
1 2 - Terry Goodkind, TOR
2 2 - Terry Brooks, TOR
3 2 - Ursala leGuin, TOR
4 3 - Wizards of the Coast

Kind of thing...

After you feel comfortable with this, you can go on to designing you forms.

By the way, I know you must be a bit discouraged. I understand. You will find as you work through some of this stuff, it will become easier and easier. But please don't get too frustrated if you find the design of a form or report is not working, and you have to go back to the beginning. It will work out in the end, and you will be impressed with yourself.

Richard

 

[muriel]

Thanks again. I know it'll eventually work out - I have to just keep at it.

Stars for Kevin & Richard.

 

[lespaul]

Sorry to interrupt this thread for a sidebar, but....

I knew I liked you for a reason Richard, those are some of my FAVORITE authors. Have you read Goodkind's newest (Naked Empire)?

Email me if you want to talk SF/F authors/books. I'd love a "reading partner"!



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!