K K
asked on
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.
Relationship1.PNG
Relationship1.PNG
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
relationship2.PNG
Officer-ID-value.PNG
Sql-Statement.PNG
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?
relationship2.PNG
Officer-ID-value.PNG
Sql-Statement.PNG
> 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.
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.
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)
Date and Time of the Case, Type of Case (e.g. Theft, Vandalism, Larceny), Description Of the Case (in a memo field)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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.