Multiple query question 1

[muriel]

I have a database which contains bibliographic data, including authors, organizations they're affiliated with, title of works, publications, etc.

The data entry form is set up with 4 separate author fields with 4 separate corresponding organization fields (e.g. author1, organization1, etc.).

I need to create queries that will:
1) show all authors and title of works associated with those authors, and
2) show all authors and organizations they belong to.

I'm not sure how to design the queries since I have my authors data in 4 separate fields. One organization may have many authors associated with it.

Any ideas? Am I missing something obvious?
Thanks.

 

[lespaul]

Instead of looking at how the form collects the data, you need to look at the table structure and see how they are set up and relate to each other. If the database is normalized (which is iffy at best) you should have a table named (something like) Authors, another named Organizations and a third table named Authors_Organizations. This setup allows a many to many relationship between authors & organizations. Even if your tables aren't normalized, if you will detail how the tables are set up and what data you want from each table, someone should be able to help you design a query to return the information you need.



Leslie
landrews@metrocourt.state.nm.us

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

 

[muriel]

Thanks - I will try to clarify what tables I need and what information I need from them.

 

[willir]

I guess we answered this one
thread700-628486 it works out.

 

[lespaul]

You know, I'm BIG on normalization, and I usually try to get people to FIX their data (I have several links that I usually send people too), but you went above and beyond the call of duty with that one, have another star in this posting too! I'm going to add that thread to any posting having to do with normalization, gives a great "thought by thought" process to follow!

Leslie

 

[willir]

Thanks for your complement Leslie. I am truely humbled -- there a some really exceptional advisors that participate in these forums.

I will say that when I read Muriel's question, she presented her issue with great clarity and been very thoughtful on her outcomes. In addition, her project sounded very interesting. Couple this with me working late staging a couple of laptops - mindless work - it was nice to work the old gray matter.

Cheers all
Richard