I need help cleaning up my relationships for queries and reporting. When I try to run a query with information on more than two tables it won't show anything. I know this is because my relationships are messy.
This is because all tables share information. The database is used to run a script writing peer review system. Writers read other writers scripts and give feedback. We log a request for a script and the script feedback. The problem I have is that the writers are also readers.
Please take a look at my relationships and let me know how I could make access know what I need. Any help would be very useful.
Second, I don't know what your experience level is, but I'd make sure you have a firm grasp of the relational database design before laying out your table structure. Again, will save a lot of headaches down the road for you.
Thanks for that. I will read that article. I need the database to be able to merge from all tables at once which is difficult as there are many relationships. I can't work out when to do a lookup or a relationship.
How many requests does a feedback have? None
How many scripts does a feedback have? One
How many writers does a feedback have? One
How many feedbacks does a request have? None
How many scripts does a request have? One
How many writers does a request have? One
How many feedbacks does a script have? many?
How many requests does a script have? many?
How many writers does a script have? One
How many feedbacks does a writer have? many
How many requests does a writer have? many
How many scrpts does a writer have? many
Following this method gives the relationship structure that I have posted here. I think the problem is that writers are also readers (as this is a peer review system). It's the one to many writer - script relationship that causes the problem as they have a many to many relationship through requests and feedbacks.
I can't work out how to get around this.
*Edit* My lookups are only for reference. I hide the foreign key by giving it the display width of 0cm and add other name fields so I can see what I am selecting when creating a new record. The foreign key is always the bound field.