MS Access

Ok attached I have my link tables in MS Access. The more info I am receiving the more frustrating this DB creation has been.  It seems that adding all these primary keys with autonumbers just leaves useless information in my tables.  Anyways, this question is referring back to my previous question about a Police Department Field Interview Database.  I have attached my tables and links in hopes to have insight as to this being able to be a functional DB.  Please let me know what you see wrong and possibly how to fix it.   Will this allow me to search an officer to see all the subjects he has interviewed as well as being able to search a subject and find the interviewing officer?  Please note that I have 4 different primary keys labeled ID's and I feel like all these autonumbered fields are going to interfere with real data.  Thanks.
K KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

>autonumbers just leaves useless information in my tables
These are simply unique identifiers.  If you want to use another unique ID, you can but make sure that the field/fields you choose will never have any duplicates.
Ultimately, you need a unique way to access the data in a relational database and autonumber fields are a great way to go to avoid any potential future duplicates.

It definitely takes some getting used-to that there are extra "metadata" pieces laying out, but they actually help to link pieces together.
I'll check the DB later if someone else hasn't gotten to it.
EirmanChief Operations ManagerCommented:
I'm finished for the day KK so I can't give this more time ...
You are repeating fields unnecessarily.

The Interview table should only contain the date and time and 3 fields for linking
(most should be in the Subject field)

The case table should not have any name fields.

The subject field looks fine (but as mentioned, you need to more fields).

I'll check in tomorrow.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, you've got the basic idea, but you don't need a lot of those ID fields.

For example, in the Officer table, you don't need "Field Interview ID".  Like wise in Case # (BTW, bad idea to use a # sign as part of a name or have embedded spaces in a name).   You also don't need the subject names nor officer names in that table.   In the subject table, you don't need field interview ID nor office ID.  Also, many of the fields in the Field Interview table are not needed as they belong elsewhere.

 What you want in each table is the fields that pertain to the "thing" that the table describes.   For example, Officer first and last name is something about an officer, so they belong in that table and no where else.  Likewise, "Interview Date" is something about an interview, so it belongs in that table and no where else

 When you conduct an interview, you point to the specific officer record by using a copy of it's primary key, so Field Interview needs to contain OfficerID (this is called a foreign key).  But it should not have copies of things like Officer name, badge number etc.

 When you need the officers information for a report or query, you will use the foreign key to look up the record in the officers table and then have access to that information.

 In doing so, the officer's information is stored only once and not in multiple places.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

I suggested in an earlier thread that you should use best practice naming standards.  It is not too late to change them now.  Once you start building objects, it will be much more difficult and it isn't until then that you will understand your mistake.

As to the schema,
You are duplicating data.  With the exception of a table's primary key, NOTHING else is ever stored in another table because that will create update anomalies.  If you change OfficerLastName in tblOfficers, it won't be changed in the potentially thousands of related records in tblFieldInterview.  The only information from tblOfficers that belongs in tblFieldInterview is OfficerID.  When you need the details of the officer information, you use a query to join the two tables and you will see data from both tables at once.

You also have pathological relationships.  You have CaseID in tblFieldInterview and FieldInterviewID in tblCase.  You need to remove FieldInterviewID from tblCase.  So in tblFieldInterview, CaseID is the Foreign key which points to tblCase.  tblCase CANNOT point back to tblFieldInterview because there are many interviews for each case.  So the relationship is formed by placing the PK of the 1-side table (tblCase) into the many-side table where it is called a foreign key.

tblCase should be something like:
CaseID (autonumber, primary key)
CaseNum (user friendly unique identifier)

So start by removing all the case, subject, and officer columns from the interview table.

We talk a lot about normalizing the data so that you remove duplicates and other types of anomalies but we don't talk much about how once you have done that you make your data back into information.  Well the way to do that is to create queries.  All of your forms/reports will be based on queries so anytime you want to look at an interview, you will see all the pertinent officer, subject, and case fields as well.  If you change the OfficerLastName in the officer table, ALL references to OfficerLastName will as if by magic show the correct name.  That happens because the tables are linked by arbitrary autonumber IDs rather than by meaningful data.

It is difficult with your first database to take off your spreadsheet hat.  Most people intuitively understand simple lists in spreadsheets but it is not until they have tried to manage a large one with lots of relationships that have been flattened out that they understand the real pain of the flat table model.  Stick with us for a while.  It will all come together once you start making forms and reports.  Just remember, the user NEVER, EVER sees a table or a query.  He only sees the interface you created for him so if you want the control's label on the form to say "Case #", that's fine.  It is only a text description.  The field bound to the control will be named CaseNum and that will be far easier in the long run for you to work with.
K KAuthor Commented:
Ok, attached are the relationships.  When I try to open some inputted data in the tblofficer it gives a Parameter Value screen. I have to type in the correct autonumber to obtain the correct date.  A snipit of this is also attached.  Is this going to affect my forms down the line? I don't want users to have to enter in a value everytime they want to search data especially since its an autonumber.  When I tweak the name OfficerID (Officer ID) it will show me the correct data, however the next time I open the table the data doesn;t exist.  (I have tweaked the OfficerID in the tbl interview and tbl officer and cannot get it to consistantly show the appropriate data that was entered.    

Also, when I try to run a query I get the message SQL statement cannot be executed because it contains ambigous Joins.  When I take some of the tables out of the query it will run, but the data that displays isn't organized.  Any ideas what step I am missing?
> When I try to open some inputted data in the tblofficer it gives a Parameter Value screen
That usually means that one of the names is misspelled, somewhere, or the relationship is not found between the tables.

> when I try to run a query I get the message SQL statement cannot be executed because it contains ambigous Joins
That's a bit more challenging.  It typically means that the connection between tables is incorrect, or maybe the fields being used is not correct.
EirmanChief Operations ManagerCommented:
You could add some extra fields to the tblCase Table to store details such as ...
Date and Time of the Case, Type of Case (e.g. Theft, Vandalism, Larceny), Description Of the Case (in a memo field)
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
First, you design looks much better, but once again, watch your field naming.   I still see a # (which is a date delimiter) and you have a field named "Date" and one named "Time".   You want to avoid anything that is a reserved word, data type, or a delimiter.  

When you have this type of thing, Pat offered excellent advice, which is to compound the name, so "InterviewDate" instead of "Date"

Make sure you change these now as they can give you problems later.    Also, the parameter prompt your getting is because one place it's "OfficerID" (in the table), and you have the lookup defined as "Officer ID" (with a space).

Second thing; your making life complicated for yourself with the outer joins.  You don't need them at the moment, and certainly not defined as part of the relationships.  Everything you have currently is a one to many.

One officer can have many interviews.   Yes, some officers may not have interviews (which is what an outer join would give you), but you don't need that at this point.

And just to be clear, relationships (under database tools) are not the same thing as a join (which is what you do in a query).

 The ambiguous outer joins can happen and it just means that you need to define the order of them with multiple queries (create a query with one join, then do another query off that with the first query as a "table" and do the next outer join).

 Right now, you have more than one in a single query and Access is saying it simply doesn't know which one to apply first.

Last, you may need some additional tables and it depends on what the situation is.   For example, can an interview apply to many cases or just one?   Can a single interview apply to more than one subject?     Can an interview be given by more than one officer?

If the answer is yes to any of those, then you'll need to add what's called a linking table because what you have then is a Many to many relationship rather than a one to many.

 One interview can have many officers and one officer can have many interviews.    Right now, you only have the last part.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
See my response to your other question, advising creating a many-to-many relationship.  I agree with the other experts about field naming, and removing unnecessary fields in linked tables.
The parameter value prompt may be caused because Access is remembering the previous name.  Open the table in design view and examine the properties.  There may be a left over sort.

The join type between interview and officer is incorrect and that is what is causing the ambiguous join error.

I also agree with Jim that it is too early to specify join types.  Although Access allows you to specify a join type when you make a relationship, it is only a suggestion.  Access uses that information when you build queries using QBE to automatically join tables as they are added to the grid and it uses the join type defined for the relationship as the default.  I never specify join types in my relationship window because the type of join is dependent on the results I want.  For example, if I wanted a list of officers along with their open cases (you removed the open/close dates I suggested but you may want to put them back) you would use an inner join but if you wanted to include officers in the list even if they didn't have any open cases, then you would use a left join.

Relationships are used to enforce referential integrity.  This ensures that there are no orphan records (interviews without cases for example).  They also allow you to specify database operations like cascade delete - if you delete a Case, all interviews for that case will also be deleted.

Joins are what we use in queries to connect rows from multiple tables.  The vast majority of times, the joins in a query will match 100% the relationships defined in the relationship window but the database engine does not require that.  You can join any two tables on any columns of like data type.  So you could join interview to subject on InterviewDate to DOB.  It doesn't make any sense but it is still a valid join as far as the database engine is concerned.  However, you would not be able to make a relationship on these two fields (unless the tables were empty) because there would be date values in one table that didn't exist in the other.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.