I'm having an issue with my DB, and I believe it's a novice issue. I am building a database to help me register people for law enforcement classes. I would save their name, agency, class information, etc. in a record and then create a query for each class that will act as a roster for the class. For instance, I will create a query for Arresting 101 (not a real class!) and set the date criteria as the date for that class and my hope is that everyone I entered in my master form with that class and that date will pop up in the query.
Well...I created a query to test two people I entered for a perishable skills course. The query did NOT retrieve these 2 records. I have a feeling that my database is setup wrong...whatever horrible way I set it up, I am prepared for and know I may have to really fix some stuff to get this up and running.
I have attached a copy of the database here. I'm sorry, but it won't let me save it in an older version, I'm not sure how...
The April 30 form is where I entered information for each person, and saved it as a record. The problem with this form is, when I go back to the form to look at John Doe, for instance, his agency name and contact person is not populated, even though I chose them when I entered him the first time. That makes me think that part of the form isn't being saved as being connected to John Doe, but I'm not sure how to do that...
The "Perishable Skills 8/27/14" is the query I created to pull records from the April 30 form, which isn't working.
I'm not sure how clear I am being. Please don't hesitate to ask me questions. Thank you.
And, in summary, my problem is: The query is not pulling up the two records I saved.
When you create an INNER JOIN, the query can only return rows where the link condition (the ON Tbl1.PK = Tbl2.FK part) is met. If there are many INNER JOINs in the query there must be a match for each link condition.
In the query [Perishable Skills 8/27/14], you have an INNER JOIN between [AgencyInfo] and [AgencyContact]:
FROM ((AgencyInfo INNER JOIN AgencyContact ON AgencyInfo.[AgencyInfoID] = AgencyContact.[AgencyID])
While I open the table [AgencyContact], I can see that all rows have Null in the column AgencyID (the foreign key) --> No match in the join --> No row returned by the query.
If some link conditions are optional, you should use a LEFT JOIN or a RIGHT JOIN instead of an INNER JOIN. As it is, the database is incoherent.