 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Primary Keys, Indexes, Relations (inability to update/edit queries
Created an employee database with the following tables: Employee Info Haz Mat Training Records Safety Training Records (where tables for training records are for 2 separate training programs) I have created identical queries and forms using name and SS# from the employee info table for both haz mat and a separate set for safety training. Everything on the safety training queries and forms operate without issue. Having issues with an error message with Haz Mat Queries and forms (not able to update any of the information) - getting index, primary key or relationship error. I've match all indexes, primary keys and relationships (what is set up for safety training matches how I've set up Haz Mat).... any suggestions for what may be causing error message?? Laura I don't know what you mean by "match[ed] all indexes, primary keys and relationships". It all starts with the data. Please provide a description of the tables you are using ... for example: tblPerson PersonID (primary key) FirstName LastName DOB tblClass ClassID (primary key) ClassTitle ClassDescription trelEnrollment EnrollmentID (primary key) PersonID (foreign key) ClassID (foreign key) EnrollmentDate ... Your description didn't mention foreign keys ... how are your tables "related"? You mention two separate (but very similar sounding) tables for [haz mat] and [safety training]. Are these two tables essentially identical, except that one is for [haz mat] and the other is for [safety training]? If so, you've designed your table structure to mimic what you would have probably done if you'd been limited to using a spreadsheet! If the two tables are essentially identical in field structure, you only need one table, with one additional field to indicate [?haz mat] or [?safety training]! More specific description may lead to more specific suggestions. Regards Jeff Boyce Microsoft Office/Access MVP > Created an employee database with the following tables: > Employee Info [quoted text clipped - 14 lines] > any > suggestions for what may be causing error message?? Thank you Jeff. . . here's more info tblEmployeeInfo: SS# (PrimaryKey) Per# = personnel number Last Name First Name . . . and more related to dept, status, address, city, st, zip, etc. tblTRN_HazMat - - - all fields relate to just haz mat training ID (primary key – assigned by access) SS# . . . multiple fields related to training record Date fields (1 for each of the three different tests) Test A, B (1 for each of the three different tests) 100% = Y/N (1 for each of the three different tests) ManagerReview (1 for each of the three different tests) – indicates if test is with manager to be reviewed HazPerformance completed HazShip Compliance turned in 1 record includes all three HazTest, as well as the performance and ship compliance info - all related to a single year's worth of HazMat training per employee. tblTRN_Safety ID (primary key – assigned by access) SS# . . . multiple fields related to training record (only 1 test in this category) Date field Test A, B 100% = Y/N ManagerReview – indicates if test is with manager to be reviewed 1 record includes the training information for safety for a given year per employee. Maybe I should have combined these into one table, but I have created multiple queries and reports to provide information either just on hazmat or just on safety. tblEmployeeInfo (one) has a one-to-many relationship set up with each tblTRN_HazMat (many) and tblTRN_Safety (many). There are no relationships between tblTRN_HazMat and tblTRN_Safety (don't anticipate I'll need them, they are completely separate). You mention – foreign keys (I have not established, don’t believe I need them, I could be wrong....???). I said that I matched what I did with tblTRN_Safety and with tblTRN_HazMat . . which simply means that I mirrored what I did with both tables (as well as the creation of the queries and forms), I have also investigated the properties of each and have made sure the properties are identical (i.e. indexes, etc.) Does this provide enough information or do you need more? I am grateful for any help you can provide. > Laura > [quoted text clipped - 58 lines] > > any > > suggestions for what may be causing error message?? I'm not sure I have a clear image of your table structure yet... We aren't there. We can't see what you're looking at. It sounds like you have repeating fields in the HazMat table ("1 for each of the three different tests"). This design is standard ... in spreadsheets! If you want to get the best use of Access' relationally-oriented features/functions, don't feed it 'sheet data! Foreign keys are just the way to point back to another table's primary key ... if you have SS# as a primary key in tblEmployeeInfo, then using SS# in the HazMat table is using it as a foreign key there. It tells a HazMat record which tblEmployeeInfo record it "belongs" to. I don't understand what test A/B means in the Safety table, nor 100% Yes/No. I'm reluctant to suggest ways to continue processing the data as you now have it structured (as best I understand), as it will only get harder and harder for both you and Access until you take the time to more fully normalize your data. If the terms "relational" and "normalize" are unfamiliar, consider spending the time to come up the learning curve on these ... Access will "thank you" and you will thank yourself. Regards Jeff Boyce Microsoft Office/Access MVP > Thank you Jeff. . . here's more info > [quoted text clipped - 130 lines] >> > any >> > suggestions for what may be causing error message?? I think that may be part of the issue - - - - I did feed it 'sheet data'...(took it right from my excel sheets (I'm embarassed to admit, but speaks to how much I have yet to learn in access)....the terms Relational and normalize are familiar. . . I am not an expert with Access (YET) . . . knowing what I wanted to get out of the reports is what drove my decision to do this... If you could help me understand better how I can more normalize my tables that would be great. I think the employeinfo table is ok... it's the training tables. by what you are saying it sounds like I can create one table for ALL training... Maybe I need to consider creating fields for: Training Type: options = haz mat or safety Course: options = Haz I, haz II, Haz III, Safety Then create separate records for each of the 4 training types.... It's when I get to the queries that this starts to break down for me... in order to create the queries that'll provide me the info I need in my reports. Would it be helpful to email you copies of the reports (I have several, but would send the 2 reports (haz and safety) that show all years of training)?? I know that I can set parameters...etc. I just seemed so simple to keep all the haz test info for one year all on one record.... One more thing: foreign key - do I need to go into the training tables and identify SS# as the foreign key - or did setting up the relationship 1-many already do that?? Thanks again for your help...I am appreciative! > I'm not sure I have a clear image of your table structure yet... We aren't > there. We can't see what you're looking at. [quoted text clipped - 159 lines] > >> > any > >> > suggestions for what may be causing error message?? Laura If I were setting up a "training db" in my environment (no guarantees it matches yours), I'd probably use: tblEmployee EmployeeID FName LName DOB <<no SS#, since there are some strict rules about asking for/using this, and not everyone has one)>> tblTraining TrainingID TrainingTypeID (a foreign key from a TrainingType table, so you could always add more training/types) TrainingTitle TrainingDescription tblDepartment DepartmentID ... (you did describe some info about an employee as "department", right -- what happens when s/he moves?!) trelAssignment AssignmentID EmployeeID DepartmentID AssignmentFrom AssignmentTo SupervisorsEmployeeID trelTrainingCompleted TrainingCompletedID EmployeeID TrainingID TrainingDateFrom TrainingDateTo tlkpTrainingType TrainingTypeID TrainingType (e.g., HazMat, Safety, DefensiveDriving, ...) The testing issue opens an entire new can of worms. You might want to take a look at Duane's db for creating tests/surveys to see if you can adapt it to your situation. Take a look at Duane Hookom's AtYourSurvey as a model for data structure. http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane Good luck! Regards Jeff Boyce Microsoft Office/Access MVP >I think that may be part of the issue - - - - I did feed it 'sheet > data'...(took it right from my excel sheets (I'm embarassed to admit, but [quoted text clipped - 222 lines] >> >> > any >> >> > suggestions for what may be causing error message?? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.