If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#91
|
|||
|
|||
Still Struggling...
I don't have much time to reply today, but I have noted a few things inline.
"Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. 2.Ever since this started taking off in earnest, I have often thought that this db seems to be complicated; maybe that's just beginner's woe. I don't know because I don't have a point of reference. Both you and Beetle stated in your posts to each other in the beginning that it wasn't simple for a first app. My question is how did you know? We hadn't even really gotten into it past the original 6 tables. How does one determine complexity? There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Hoping this is not too much for you in one post... We know: 1. This is a staff database for a school setting. 2. We encompass 2 campuses. 3.Db emphasis is on keys because they have been problematic. 4. We have already suffered through a re-key of an entire campus, including the stadium, gyms and gates. 5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms., etc.) 6. Master keys allow general access for a single campus. 7. Master keys are unique, employee specific and their allocation is severely restricted. 8. Storage, Stadium, Food Service and Gate Masters are location specific *and* follow rule #7. 9. Wing masters will only open all rooms for a specific section of the site. 10. Key assignments are based on job title, room assignment and extracurricular duties. Where we left off: 1. We had 4 tables relating to keys or location: tblRooms, tblKeysEmployees (junction), tblKeys and tblKeysRequests. What became of tblLocks? If one lock may have several keys, locks are the top level. 2. There was a lot of discussion involving home, personal cell, district cell and room phone #s. The last decision was that home/personal cell is part of tblEmployees; district cell and room phone #s are part of tblphones. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. If your eyes haven't glazed over... You don't have to answer today; I know it's a lot. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. 2. Master keys--This is a sub-type of key, correct? You can have one key and many masters or is it many sub-types (Gate, Stadium, etc.) But you can also have one specific master that has many keys assigned. While the master is employee specific, it *is* essentially the same key that is assigned to every employee who has clearnace. It's just coded so we know in advance who we gave it to. This is a 1:M relationship? You can't have a M:M relationship with sub-types can you? Either way it will have its own PK. This is where I'm confusing myself and going around in circles. I'll have to ponder this one later. I'll stop here for now. Thanks! -- Aria W. "BruceM" wrote: I think that Macros may be considered to be something other than code, but I'm not sure. If you use the wizard the code is added automatically. You can view it, but you may not know it was added. In any case, unless you use macros, which are rather limited, you pretty much need VBA code for anything other than a very simple database. To find the Customize option I mentioned, open your database. In the database window, click the Forms tab. Click View Code, or click the Code icon on the toolbar. What you see is the VBA editor. Right click a blank spot on the toolbar or menu bar. You should see Customize, probably as the last item on the list. There are of course other ways to open the VBA editor, so choose another method if you prefer. The way you open it is not important (although I wouldn't try to customize while debugging). I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. For instance, for tblEmployees: EmployeeID (PK - Number) LastName FirstName etc. may be enough. For tblSiteEmps, list the PK/FK field, the relationship type, and a few fields that will give the idea of how the table is used: tblSiteEmps (1:1 tblEmployees) EmpID (PK/FK) Home Phone-txt Address-txt etc. This is enough for our purposes. Give yourself a break from typing all of the details, unless for instance the fact there is a cell phone number is relevant to the problem at hand, and make it easier for me or another responder to read. Limit your description to a few typical fields, or to fields that are part of your code or that are involved in relationships. "Aria" wrote in message ... You know I had never been to this section of my database before. Originally, I had no intention of putting in any programming code. I tried doing as you suggested but when I go to Customize...Options...I don't see what you are describing. What I see in the Options tab is Personalized Toolbar and Menu but it's greyed. The only button you can push is Reset my Usage Data (?). Maybe I'm in the wrong place. I wanted to return to the 2nd half of the db. I still need to work on tables but I have questions (naturally!) Do you mind? -- Aria W. "BruceM" wrote: I should have said 'Still in the VBA editor, click Debug Compile." This is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click Debug on the left, and drag Compile from the right side to the toolbar. BTW, you could sort the row source for the Employee combo box by the concatenated (LastFirst) field. If you sort on LastName you should sort on FirstName next, in case two people have the same last name. You don't need to show the LastName and FirstName fields. "Aria" wrote in message ... Guess what? It works...it works! I'm trying real hard to hold it together, but I am so happy. sniff Thank you *so* much! OK, so let me tell you what happened. I'm going to combine parts of both your posts. Bruce M wrote: If you view the SQL in datasheet view you will see that the first column is EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup. This statement: rs.FindFirst "[EmpID] = " & Me.cboStaffLookup means "Find the first record in the RecordsetClone in which EmpID is the same as EmpID in the combo box." Beetle wrote: A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No Aria writes: It was the combination of both your posts that allowed me to follow along closely. You explained what was happening and then said this is what it will look like. Bruce M wrote: Private Sub cboStaffLookup_AfterUpdate() End Sub The cursor should be blinking between those lines. Add the code. After you enter: Dim rs As Object press the Enter key to go to a new line (or press it twice to create some space and make the code easier to read). Add: Set rs = Me.RecordsetClone Press the Enter key again, and add the next lines of code, pressing the Enter key after each one. Scroll to the top of the code window and be sure the words Option Explicit are under Option Compare Database. Add them if they are not. If they are not, in the VBA editor click Tools Options. Click the Editor tab, and check the box Require Variable Declaration. Still in the editor, click Debug Compile. This will highlight any typos and other such errors in the code. Aria writes: Step by step instructions...how could I ask for anything better than that. The Require Variable Declaration box wasn't checked. I didn't find the Debug Compile box but it did have Auto Syntax Check box. When I ran it I got a Compile error (Error 461). I used Help for this part. When I was finished, I noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in yellow; highlighted in blue was cbo StaffLookup. I didn't notice the underscore with cboStaff_Lookup before. That did it. Bruce M wrote: I don't know what is happening with the sort order, but let's not get too many things cooking on a Friday afternoon. lol Well, on my end it may be a little late for that. One more thing...the sort order is working as it should. I can't believe my book said it could be done w/o code. I can't thank you both enough. Still trying to hold it together. Have a great weekend! -- Aria W. "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first |
#92
|
|||
|
|||
Still Struggling...
I don't have much time to reply today, but I have noted a few things inline.
Please, don't give it a second thought. I appreciate all of the support you have both given me. I understand. If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. I'll check on that. There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Yeah, no kidding. I started thinking about that because I was trying to avoid adding yet another junction table. I recall a previous discussion where we decided that the room phone could go into tblLocations. That would leave district cell phone orphaned and I could not figure out where to put it. It doesn't belong in tblLocations and doesn't belong in tblEmployees so I figured why am I drawing the line at this junction table when there are 5 others just like it. Oh well...c'est la vie! I keep looking at the relationship diagram and I 'm concerned how I will manage all of this. In particular, what kind of form to design and whether I should have another subform for tbKleysRequests on frmEmployees. I'm trying not to borrow trouble; one thing at a time. What became of tblLocks? If one lock may have several keys, locks are the top level. "A lock can have several keys". There have been a number of turning points in this journey. You both have dropped statements that on the surface seemed inconsequential ,but in reality had a major impact. That is one thing I am *never* going to forget. You made 3 on the mark statements in that post. tblLocks is still here. It*is* a junction table, correct? It sure looks like one. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. Won't this cause a problem? With the re-key, they not only changed the lock, they changed the lock identifier. So if the key was previously, let's say, SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as my primary key, I would have thought this would be a nightmarish situation. No? If it happened once, there's always the possibility that it *could* happen again. Keys are lost pretty much on a weekly basis. What do you thinK? Do I need an inactive button for keys? I don't know exactly *how* this would work. Let me know what you think about that and the master keys. Don't concern yourself if you don't have time. I have problems in the db that I need to straighten out. Thanks so much for your time Bruce. -- Aria W. "BruceM" wrote: I don't have much time to reply today, but I have noted a few things inline. "Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. 2.Ever since this started taking off in earnest, I have often thought that this db seems to be complicated; maybe that's just beginner's woe. I don't know because I don't have a point of reference. Both you and Beetle stated in your posts to each other in the beginning that it wasn't simple for a first app. My question is how did you know? We hadn't even really gotten into it past the original 6 tables. How does one determine complexity? There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Hoping this is not too much for you in one post... We know: 1. This is a staff database for a school setting. 2. We encompass 2 campuses. 3.Db emphasis is on keys because they have been problematic. 4. We have already suffered through a re-key of an entire campus, including the stadium, gyms and gates. 5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms., etc.) 6. Master keys allow general access for a single campus. 7. Master keys are unique, employee specific and their allocation is severely restricted. 8. Storage, Stadium, Food Service and Gate Masters are location specific *and* follow rule #7. 9. Wing masters will only open all rooms for a specific section of the site. 10. Key assignments are based on job title, room assignment and extracurricular duties. Where we left off: 1. We had 4 tables relating to keys or location: tblRooms, tblKeysEmployees (junction), tblKeys and tblKeysRequests. What became of tblLocks? If one lock may have several keys, locks are the top level. 2. There was a lot of discussion involving home, personal cell, district cell and room phone #s. The last decision was that home/personal cell is part of tblEmployees; district cell and room phone #s are part of tblphones. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. If your eyes haven't glazed over... You don't have to answer today; I know it's a lot. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. 2. Master keys--This is a sub-type of key, correct? You can have one key and many masters or is it many sub-types (Gate, Stadium, etc.) But you can also have one specific master that has many keys assigned. While the master is employee specific, it *is* essentially the same key that is assigned to every employee who has clearnace. It's just coded so we know in advance who we gave it to. This is a 1:M relationship? You can't have a M:M relationship with sub-types can you? Either way it will have its own PK. This is where I'm confusing myself and going around in circles. I'll have to ponder this one later. I'll stop here for now. Thanks! -- Aria W. "BruceM" wrote: I think that Macros may be considered to be something other than code, but I'm not sure. If you use the wizard the code is added automatically. You can view it, but you may not know it was added. In any case, unless you use macros, which are rather limited, you pretty much need VBA code for anything other than a very simple database. To find the Customize option I mentioned, open your database. In the database window, click the Forms tab. Click View Code, or click the Code icon on the toolbar. What you see is the VBA editor. Right click a blank spot on the toolbar or menu bar. You should see Customize, probably as the last item on the list. There are of course other ways to open the VBA editor, so choose another method if you prefer. The way you open it is not important (although I wouldn't try to customize while debugging). I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. For instance, for tblEmployees: EmployeeID (PK - Number) LastName FirstName etc. may be enough. For tblSiteEmps, list the PK/FK field, the relationship type, and a few fields that will give the idea of how the table is used: tblSiteEmps (1:1 tblEmployees) EmpID (PK/FK) Home Phone-txt Address-txt etc. This is enough for our purposes. Give yourself a break from typing all of the details, unless for instance the fact there is a cell phone number is relevant to the problem at hand, and make it easier for me or another responder to read. Limit your description to a few typical fields, or to fields that are part of your code or that are involved in relationships. "Aria" wrote in message ... You know I had never been to this section of my database before. Originally, I had no intention of putting in any programming code. I tried doing as you suggested but when I go to Customize...Options...I don't see what you are describing. What I see in the Options tab is Personalized Toolbar and Menu but it's greyed. The only button you can push is Reset my Usage Data (?). Maybe I'm in the wrong place. I wanted to return to the 2nd half of the db. I still need to work on tables but I have questions (naturally!) Do you mind? -- Aria W. "BruceM" wrote: I should have said 'Still in the VBA editor, click Debug Compile." This is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click Debug on the left, and drag Compile from the right side to the toolbar. BTW, you could sort the row source for the Employee combo box by the concatenated (LastFirst) field. If you sort on LastName you should sort on FirstName next, in case two people have the same last name. You don't need to show the LastName and FirstName fields. "Aria" wrote in message ... Guess what? It works...it works! I'm trying real hard to hold it together, but I am so happy. sniff Thank you *so* much! OK, so let me tell you what happened. I'm going to combine parts of both your posts. Bruce M wrote: If you view the SQL in datasheet view you will see that the first column is EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup. This statement: rs.FindFirst "[EmpID] = " & Me.cboStaffLookup means "Find the first record in the RecordsetClone in which EmpID is the same as EmpID in the combo box." Beetle wrote: A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No Aria writes: It was the combination of both your posts that allowed me to follow along closely. You explained what was happening and then said this is what it will look like. Bruce M wrote: Private Sub cboStaffLookup_AfterUpdate() End Sub The cursor should be blinking between those lines. Add the code. After you enter: Dim rs As Object press the Enter key to go to a new line (or press it twice to create some space and make the code easier to read). Add: Set rs = Me.RecordsetClone Press the Enter key again, and add the next lines of code, pressing the Enter key after each one. Scroll to the top of the code window and be sure the words Option Explicit are under Option Compare Database. Add them if they are not. If they |
#93
|
|||
|
|||
Still Struggling...
I think I may have a problem that I need to work through. We can discuss this
when you have time. I just wanted to get this down before I lose track and start dealing with other issues. I think we are going to need to re-visit an issue from the very first post: vault keys. Originally, we decided that the situation was unclear and dropped it. In looking through some of the data that I copied to help map my direction, I just noticed something that I probably saw before but did not grab my attention as it has now. During the re-key, among the many replacement keys made, one key was important enough to be "assigned" to the vault. There are probably others, but that happened before my time so I am not aware of the previous history. This does change things a bit. I'm going to have to account for not only that key, but all of the vault keys. I have two trains of thought right now: The first is that the vault key is a sub type key that will need its own table. I don't know about this because it doesn't seem to fit the model of a sub type (at least not as far as I can see). It's not like the masters which are actually a different type of key. Their attributes are significantly different. Not so with the vault key; there's nothing special or different about them. The second thought is that this is really a location type and will need to be included with tbllocations or is it tbllocationtypes. I'm still working on this because I'm unsure right now. It does seem to fit the "where" model though. Just throwing it out there... -- Aria W. "Aria" wrote: I don't have much time to reply today, but I have noted a few things inline. Please, don't give it a second thought. I appreciate all of the support you have both given me. I understand. If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. I'll check on that. There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Yeah, no kidding. I started thinking about that because I was trying to avoid adding yet another junction table. I recall a previous discussion where we decided that the room phone could go into tblLocations. That would leave district cell phone orphaned and I could not figure out where to put it. It doesn't belong in tblLocations and doesn't belong in tblEmployees so I figured why am I drawing the line at this junction table when there are 5 others just like it. Oh well...c'est la vie! I keep looking at the relationship diagram and I 'm concerned how I will manage all of this. In particular, what kind of form to design and whether I should have another subform for tbKleysRequests on frmEmployees. I'm trying not to borrow trouble; one thing at a time. What became of tblLocks? If one lock may have several keys, locks are the top level. "A lock can have several keys". There have been a number of turning points in this journey. You both have dropped statements that on the surface seemed inconsequential ,but in reality had a major impact. That is one thing I am *never* going to forget. You made 3 on the mark statements in that post. tblLocks is still here. It*is* a junction table, correct? It sure looks like one. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. Won't this cause a problem? With the re-key, they not only changed the lock, they changed the lock identifier. So if the key was previously, let's say, SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as my primary key, I would have thought this would be a nightmarish situation. No? If it happened once, there's always the possibility that it *could* happen again. Keys are lost pretty much on a weekly basis. What do you thinK? Do I need an inactive button for keys? I don't know exactly *how* this would work. Let me know what you think about that and the master keys. Don't concern yourself if you don't have time. I have problems in the db that I need to straighten out. Thanks so much for your time Bruce. -- Aria W. "BruceM" wrote: I don't have much time to reply today, but I have noted a few things inline. "Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. 2.Ever since this started taking off in earnest, I have often thought that this db seems to be complicated; maybe that's just beginner's woe. I don't know because I don't have a point of reference. Both you and Beetle stated in your posts to each other in the beginning that it wasn't simple for a first app. My question is how did you know? We hadn't even really gotten into it past the original 6 tables. How does one determine complexity? There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Hoping this is not too much for you in one post... We know: 1. This is a staff database for a school setting. 2. We encompass 2 campuses. 3.Db emphasis is on keys because they have been problematic. 4. We have already suffered through a re-key of an entire campus, including the stadium, gyms and gates. 5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms., etc.) 6. Master keys allow general access for a single campus. 7. Master keys are unique, employee specific and their allocation is severely restricted. 8. Storage, Stadium, Food Service and Gate Masters are location specific *and* follow rule #7. 9. Wing masters will only open all rooms for a specific section of the site. 10. Key assignments are based on job title, room assignment and extracurricular duties. Where we left off: 1. We had 4 tables relating to keys or location: tblRooms, tblKeysEmployees (junction), tblKeys and tblKeysRequests. What became of tblLocks? If one lock may have several keys, locks are the top level. 2. There was a lot of discussion involving home, personal cell, district cell and room phone #s. The last decision was that home/personal cell is part of tblEmployees; district cell and room phone #s are part of tblphones. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. If your eyes haven't glazed over... You don't have to answer today; I know it's a lot. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. 2. Master keys--This is a sub-type of key, correct? You can have one key and many masters or is it many sub-types (Gate, Stadium, etc.) But you can also have one specific master that has many keys assigned. While the master is employee specific, it *is* essentially the same key that is assigned to every employee who has clearnace. It's just coded so we know in advance who we gave it to. This is a 1:M relationship? You can't have a M:M relationship with sub-types can you? Either way it will have its own PK. This is where I'm confusing myself and going around in circles. I'll have to ponder this one later. I'll stop here for now. Thanks! -- Aria W. "BruceM" wrote: I think that Macros may be considered to be something other than code, but I'm not sure. If you use the wizard the code is added automatically. You can view it, but you may not know it was added. In any case, unless you use macros, which are rather limited, you pretty much need VBA code for anything other than a very simple database. To find the Customize option I mentioned, open your database. In the database window, click the Forms tab. Click View Code, or click the Code icon on the toolbar. What you see is the VBA editor. Right click a blank spot on the toolbar or menu bar. You should see Customize, probably as the last item on the list. There are of course other ways to open the VBA editor, so choose another method if you prefer. The way you open it is not important (although I wouldn't try to customize while debugging). I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. For instance, for tblEmployees: EmployeeID (PK - Number) LastName FirstName etc. may be enough. For tblSiteEmps, list the PK/FK field, the relationship type, and a few fields that will give the idea of how the table is used: tblSiteEmps (1:1 tblEmployees) EmpID (PK/FK) Home Phone-txt Address-txt etc. This is enough for our purposes. Give yourself a break from typing all of the details, unless for instance the fact there is a cell phone number is relevant to the problem at hand, and make it easier for me or another responder to read. Limit your description to a few typical fields, or to fields that are part of your code or that are involved in relationships. "Aria" wrote in message ... You know I had never been to this section of my database before. Originally, I had no intention of putting in any programming code. I tried doing as you suggested but when I go to Customize...Options...I don't see what you are describing. What I see in the Options tab is Personalized Toolbar and Menu but it's greyed. The only button you can push is Reset my Usage Data (?). Maybe I'm in the wrong place. I wanted to return to the 2nd half of the db. I still need to work on tables but I have questions (naturally!) Do you mind? -- Aria W. "BruceM" wrote: I should have said 'Still in the VBA editor, click Debug Compile." This is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click |
#94
|
|||
|
|||
Still Struggling...
Is the vault key just like any other key for a particular lock, except that
it is in the vault? If so, is the vault copy essentially a clean copy to be used as the master for additional copies? If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault just as you would assign it to a person. To do this you could add (Vault) to the top of the drop-down list of Employees to whom keys are issued, similar to the way some drop-down lists show (All) at the top. I won't go too far down that road until I hear back from you. Regarding the Locks table, I don't think it would be a junction table. A lock is an entity with certain characteristics such as brand, location, date installed, and so forth. One Lock can have many keys. Iff Master Keys are included in the thinking, one Key can be for many Locks, but in this case the junction table would be tblKeyLock or something like that. Regarding tblKey, I expect there should be a Lost field or something like that. There is no need to keep an active listing of keys nobody can find. Regarding the question of natural key or surrogate key (any "artificial identifier", including autonumber), it really doesn't matter as long as the "natural" number such as SA-2 is used once for one physical (metal) key, and never used again. If you are not sure this is the case, autonumber would be a simpler choice as the PK. The same idea comes into play with the Lock identifier, I expect. If they change the lock in such a way that the old keys can be used you will need to update the FK of those keys so that they are associated with the new lock. From an earlier post you wrote: Suggestions still on the table: 1. Create tblLocations to replace tblRooms Sounds like a good idea .. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. Already discussed. Let me know if something is unclear, or if I am missing something 3. Create Master Key table to account for the special attributes of Master Keys. One possibility is to see the situation as One Lock Many Keys and One Key Many Locks, in which tblKeyLock is needed to resolve the relationship for all locks and all keys. However, I think a separate table for MasterKeys and a junction table tblKeyLock would be simpler to manage. If you are looking at a Lock record you would have a subform listing the ordinary keys and the person to whom they are assigned (some may not be assigned at all, I expect). One Lock Many Keys, so there is a 1:M between tblLock and tblKey, and the subform is based on tblKey. Another subform based on tblKeyLock could list the master key holders. One lock could have keys assigned to many people, and each person could be assigned keys. Another consideration is that a Lock record should have a listing of available keys. If it was me I think I would have an AssignedTo field in tblKeys: tblKeys KeyID (PK) LockID (FK to tblLocks) KeyCode (S2-A, etc., or whatever) AssignedTo (FK to tblEmployees) AssignedDate Retired (Yes/No) If I wanted to list people and the keys they hold I would use a query. This is not necessarily the best design in that AssignedTo and AssignedDate are not really attributes of keys. The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person. Similarly, one lock may have many keys, but other than master keys a key may open only one lock. On the other hand, one person may open many locks, and a lock may be opened by many people. By the way, it just occurred to me that if there may be several locks keyed to accept the same key you may need to add a LockLocation table related to tblLocks to take care of this detail. One solution here may be to have a LockPerson junction table (one person many locks and vice versa). This would be the source for a subform on the Locks form. A list box could contain a listing of unassigned keys for that lock. The list box row source would have to be built as you go, since the available keys are always changing. When somebody needs to be assigned a key you would go to the Lock form, see the listing of available keys, and created a new LockPerson record that stores the KeyID, EmployeeID, and maybe AssignedDate and other details. It may be worth your while to start a new thread on this specific topic. All you would need to say is that you have: A Locks table containing the LockId, LockLocation, etc. A Keys table containing KeyID (PK), LockID (FK), KeyCode, etc. An Employee table Explain that you understand a Lock may have many keys, and that a person may be assigned many keys. However, you are unsure how to store the AssignedTo information for Keys. Should it be in the Keys table? If not, how is that relationship modeled. I suggest this because frankly I am unsure how best to proceed on this point, and in a new thread you would attract the attention of very experienced designers. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Sounds good. ********** "Aria" wrote in message ... I think I may have a problem that I need to work through. We can discuss this when you have time. I just wanted to get this down before I lose track and start dealing with other issues. I think we are going to need to re-visit an issue from the very first post: vault keys. Originally, we decided that the situation was unclear and dropped it. In looking through some of the data that I copied to help map my direction, I just noticed something that I probably saw before but did not grab my attention as it has now. During the re-key, among the many replacement keys made, one key was important enough to be "assigned" to the vault. There are probably others, but that happened before my time so I am not aware of the previous history. This does change things a bit. I'm going to have to account for not only that key, but all of the vault keys. I have two trains of thought right now: The first is that the vault key is a sub type key that will need its own table. I don't know about this because it doesn't seem to fit the model of a sub type (at least not as far as I can see). It's not like the masters which are actually a different type of key. Their attributes are significantly different. Not so with the vault key; there's nothing special or different about them. The second thought is that this is really a location type and will need to be included with tbllocations or is it tbllocationtypes. I'm still working on this because I'm unsure right now. It does seem to fit the "where" model though. Just throwing it out there... -- Aria W. "Aria" wrote: I don't have much time to reply today, but I have noted a few things inline. Please, don't give it a second thought. I appreciate all of the support you have both given me. I understand. If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. I'll check on that. There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Yeah, no kidding. I started thinking about that because I was trying to avoid adding yet another junction table. I recall a previous discussion where we decided that the room phone could go into tblLocations. That would leave district cell phone orphaned and I could not figure out where to put it. It doesn't belong in tblLocations and doesn't belong in tblEmployees so I figured why am I drawing the line at this junction table when there are 5 others just like it. Oh well...c'est la vie! I keep looking at the relationship diagram and I 'm concerned how I will manage all of this. In particular, what kind of form to design and whether I should have another subform for tbKleysRequests on frmEmployees. I'm trying not to borrow trouble; one thing at a time. What became of tblLocks? If one lock may have several keys, locks are the top level. "A lock can have several keys". There have been a number of turning points in this journey. You both have dropped statements that on the surface seemed inconsequential ,but in reality had a major impact. That is one thing I am *never* going to forget. You made 3 on the mark statements in that post. tblLocks is still here. It*is* a junction table, correct? It sure looks like one. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. Won't this cause a problem? With the re-key, they not only changed the lock, they changed the lock identifier. So if the key was previously, let's say, SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as my primary key, I would have thought this would be a nightmarish situation. No? If it happened once, there's always the possibility that it *could* happen again. Keys are lost pretty much on a weekly basis. What do you thinK? Do I need an inactive button for keys? I don't know exactly *how* this would work. Let me know what you think about that and the master keys. Don't concern yourself if you don't have time. I have problems in the db that I need to straighten out. Thanks so much for your time Bruce. -- Aria W. "BruceM" wrote: I don't have much time to reply today, but I have noted a few things inline. "Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. 2.Ever since this started taking off in earnest, I have often thought that this db seems to be complicated; maybe that's just beginner's woe. I don't know because I don't have a point of reference. Both you and Beetle stated in your posts to each other in the beginning that it wasn't simple for a first app. My question is how did you know? We hadn't even really gotten into it past the original 6 tables. How does one determine complexity? There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Hoping this is not too much for you in one post... We know: 1. This is a staff database for a school setting. 2. We encompass 2 campuses. 3.Db emphasis is on keys because they have been problematic. 4. We have already suffered through a re-key of an entire campus, including the stadium, gyms and gates. 5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms., etc.) 6. Master keys allow general access for a single campus. 7. Master keys are unique, employee specific and their allocation is severely restricted. 8. Storage, Stadium, Food Service and Gate Masters are location specific *and* follow rule #7. 9. Wing masters will only open all rooms for a specific section of the site. 10. Key assignments are based on job title, room assignment and extracurricular duties. Where we left off: 1. We had 4 tables relating to keys or location: tblRooms, tblKeysEmployees (junction), tblKeys and tblKeysRequests. What became of tblLocks? If one lock may have several keys, locks are the top level. 2. There was a lot of discussion involving home, personal cell, district cell and room phone #s. The last decision was that home/personal cell is part of tblEmployees; district cell and room phone #s are part of tblphones. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. If your eyes haven't glazed over... You don't have to answer today; I know it's a lot. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. 2. Master keys--This is a sub-type of key, correct? You can have one key and many masters or is it many sub-types (Gate, Stadium, etc.) But you can also have one specific master that has many keys assigned. While the master is employee specific, it *is* essentially the same key that is assigned to every employee who has clearnace. It's just coded so we know in advance who we gave it to. This is a 1:M relationship? You can't have a M:M relationship with sub-types can you? Either way it will have its own PK. This is where I'm confusing myself and going around in circles. I'll have to ponder this one later. I'll stop here for now. Thanks! -- Aria W. "BruceM" wrote: I think that Macros may be considered to be something other than code, but I'm not sure. If you use the wizard the code is added automatically. You can view it, but you may not know it was added. In any case, unless you use macros, which are rather limited, you pretty much need VBA code for anything other than a very simple database. To find the Customize option I mentioned, open your database. In the database window, click the Forms tab. Click View Code, or click the Code icon on the toolbar. What you see is the VBA editor. Right click a blank spot on the toolbar or menu bar. You should see Customize, probably as the last item on the list. There are of course other ways to open the VBA editor, so choose another method if you prefer. The way you open it is not important (although I wouldn't try to customize while debugging). I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. For instance, for tblEmployees: EmployeeID (PK - Number) LastName FirstName etc. may be enough. For tblSiteEmps, list the PK/FK field, the relationship type, and a few fields that will give the idea of how the table is used: tblSiteEmps (1:1 tblEmployees) EmpID (PK/FK) Home Phone-txt Address-txt etc. This is enough for our purposes. Give yourself a break from typing all of the details, unless for instance the fact there is a cell phone number is relevant to the problem at hand, and make it easier for me or another responder to read. Limit your description to a few typical fields, or to fields that are part of your code or that are involved in relationships. "Aria" wrote in message ... You know I had never been to this section of my database before. Originally, I had no intention of putting in any programming code. I tried doing as you suggested but when I go to Customize...Options...I don't see what you are describing. What I see in the Options tab is Personalized Toolbar and Menu but it's greyed. The only button you can push is Reset my Usage Data (?). Maybe I'm in the wrong place. I wanted to return to the 2nd half of the db. I still need to work on tables but I have questions (naturally!) Do you mind? -- Aria W. "BruceM" wrote: I should have said 'Still in the VBA editor, click Debug Compile." This is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click |
#95
|
|||
|
|||
Still Struggling...
First, thank you for your honesty. I smiled reading your post. That is the
same thing that happens to me. I think, "hmmm...it could be like this...but then what about that...oh, and I forgot to account for this...but then I have to switch that...and the next thing you know, I'm right back where I started. g I don't think you go through quite the same thing because you have a *lot* more experience but it *is* confusing. While keys are what drove my desire to create this db in the first place, this is also the section I have been dreading. I reviewed all of our posts and took notes on what was suggested and any decisions that were made. When I refer to a statement as from a post in the beginning, that's what I mean. So, let's see if we can make any progress on some of these areas. Is the vault key just like any other key for a particular lock, except that it is in the vault? Yes. If so, is the vault copy essentially a clean copy to be used as the master for additional copies? No, although at times they may come and borrow a key to make a quick copy if they are already on campus and I just need one or two. It's rare. The district has its own locksmith who are permanent staff. They have the originals or blanks or whatever they call them. If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault just as you would assign it to a person. Ok, what happened here? We have apparently switched our origianl positions. I suggested assigning the vault key like an employee key in the very first post, but after learning about table attributes from the two of you ( I will never forget tblSubs), I no longer hold that position. You two are absolutely right. It isn't an employee attribute. I don't think it should be a yes/no field because of the sheer number of keys we're talking about...approx. 400. Am I going to run into problems with that many yes/no boxes? You may be right about this. I'm not sure because again, I don't have the experience behind me. So where does that leave us? Are you oppossed to the vault table or vault as a location in tbllocations(now that I think about it, somethings wrong with this line of thinking but I'm not sure what)? Regarding the Locks table, I don't think it would be a junction table. I 'm now confused about the purpose and fields in this table. You had posted in the beginning that it should include lookup tables for campus, wing and roomtype. I by no means expect you to remember this because I didn't either. There was a lot of discussion about a great many things. I'm only mentioning it now because you asked me to post my table structure for this part of the db. I was unsure about some of the structure and wanted to make sure I had it right before I posted. It was a confusing mess that I had to keep looking at. I couldn't make heads nor tails of what it should be. I had to live with it awhile. I had to sit down and go through all the posts suggestions and advice, move fields from one place to another, change table names and add fields and lookup tables. When I finished the only fields left in tblLocker were KeyID and LocationID. I originally had campus and wing in this section but once we changed tblRooms to tblLocations, it seemed better suited in tblLocations. Since I was now left with only 2 fields in tblLocks, I started wondering if it was a junction table. There is no need to keep an active listing of keys nobody can find. lol ...good one! No, let's not. I'm going to post my table structure later today, even though it still needs work. I know there are mistakes and that there is plenty of room for improvement, but you have posted additional comments that I believe (if I'm not mistaken) may have been addressed within the structure. -- Aria W. "BruceM" wrote: Is the vault key just like any other key for a particular lock, except that it is in the vault? If so, is the vault copy essentially a clean copy to be used as the master for additional copies? If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault just as you would assign it to a person. To do this you could add (Vault) to the top of the drop-down list of Employees to whom keys are issued, similar to the way some drop-down lists show (All) at the top. I won't go too far down that road until I hear back from you. Regarding the Locks table, I don't think it would be a junction table. A lock is an entity with certain characteristics such as brand, location, date installed, and so forth. One Lock can have many keys. Iff Master Keys are included in the thinking, one Key can be for many Locks, but in this case the junction table would be tblKeyLock or something like that. Regarding tblKey, I expect there should be a Lost field or something like that. There is no need to keep an active listing of keys nobody can find. Regarding the question of natural key or surrogate key (any "artificial identifier", including autonumber), it really doesn't matter as long as the "natural" number such as SA-2 is used once for one physical (metal) key, and never used again. If you are not sure this is the case, autonumber would be a simpler choice as the PK. The same idea comes into play with the Lock identifier, I expect. If they change the lock in such a way that the old keys can be used you will need to update the FK of those keys so that they are associated with the new lock. From an earlier post you wrote: Suggestions still on the table: 1. Create tblLocations to replace tblRooms Sounds like a good idea .. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. Already discussed. Let me know if something is unclear, or if I am missing something 3. Create Master Key table to account for the special attributes of Master Keys. One possibility is to see the situation as One Lock Many Keys and One Key Many Locks, in which tblKeyLock is needed to resolve the relationship for all locks and all keys. However, I think a separate table for MasterKeys and a junction table tblKeyLock would be simpler to manage. If you are looking at a Lock record you would have a subform listing the ordinary keys and the person to whom they are assigned (some may not be assigned at all, I expect). One Lock Many Keys, so there is a 1:M between tblLock and tblKey, and the subform is based on tblKey. Another subform based on tblKeyLock could list the master key holders. One lock could have keys assigned to many people, and each person could be assigned keys. Another consideration is that a Lock record should have a listing of available keys. If it was me I think I would have an AssignedTo field in tblKeys: tblKeys KeyID (PK) LockID (FK to tblLocks) KeyCode (S2-A, etc., or whatever) AssignedTo (FK to tblEmployees) AssignedDate Retired (Yes/No) If I wanted to list people and the keys they hold I would use a query. This is not necessarily the best design in that AssignedTo and AssignedDate are not really attributes of keys. The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person. Similarly, one lock may have many keys, but other than master keys a key may open only one lock. On the other hand, one person may open many locks, and a lock may be opened by many people. By the way, it just occurred to me that if there may be several locks keyed to accept the same key you may need to add a LockLocation table related to tblLocks to take care of this detail. One solution here may be to have a LockPerson junction table (one person many locks and vice versa). This would be the source for a subform on the Locks form. A list box could contain a listing of unassigned keys for that lock. The list box row source would have to be built as you go, since the available keys are always changing. When somebody needs to be assigned a key you would go to the Lock form, see the listing of available keys, and created a new LockPerson record that stores the KeyID, EmployeeID, and maybe AssignedDate and other details. It may be worth your while to start a new thread on this specific topic. All you would need to say is that you have: A Locks table containing the LockId, LockLocation, etc. A Keys table containing KeyID (PK), LockID (FK), KeyCode, etc. An Employee table Explain that you understand a Lock may have many keys, and that a person may be assigned many keys. However, you are unsure how to store the AssignedTo information for Keys. Should it be in the Keys table? If not, how is that relationship modeled. I suggest this because frankly I am unsure how best to proceed on this point, and in a new thread you would attract the attention of very experienced designers. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Sounds good. ********** "Aria" wrote in message ... I think I may have a problem that I need to work through. We can discuss this when you have time. I just wanted to get this down before I lose track and start dealing with other issues. I think we are going to need to re-visit an issue from the very first post: vault keys. Originally, we decided that the situation was unclear and dropped it. In looking through some of the data that I copied to help map my direction, I just noticed something that I probably saw before but did not grab my attention as it has now. During the re-key, among the many replacement keys made, one key was important enough to be "assigned" to the vault. There are probably others, but that happened before my time so I am not aware of the previous history. This does change things a bit. I'm going to have to account for not only that key, but all of the vault keys. I have two trains of thought right now: The first is that the vault key is a sub type key that will need its own table. I don't know about this because it doesn't seem to fit the model of a sub type (at least not as far as I can see). It's not like the masters which are actually a different type of key. Their attributes are significantly different. Not so with the vault key; there's nothing special or different about them. The second thought is that this is really a location type and will need to be included with tbllocations or is it tbllocationtypes. I'm still working on this because I'm unsure right now. It does seem to fit the "where" model though. Just throwing it out there... -- Aria W. "Aria" wrote: I don't have much time to reply today, but I have noted a few things inline. Please, don't give it a second thought. I appreciate all of the support you have both given me. I understand. If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. I'll check on that. There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Yeah, no kidding. I started thinking about that because I was trying to avoid adding yet another junction table. I recall a previous discussion where we decided that the room phone could go into tblLocations. That would leave district cell phone orphaned and I could not figure out where to put it. It doesn't belong in tblLocations and doesn't belong in tblEmployees so I figured why am I drawing the line at this junction table when there are 5 others just like it. Oh well...c'est la vie! I keep looking at the relationship diagram and I 'm concerned how I will manage all of this. In particular, what kind of form to design and whether I should have another subform for tbKleysRequests on frmEmployees. I'm trying not to borrow trouble; one thing at a time. What became of tblLocks? If one lock may have several keys, locks are the top level. "A lock can have several keys". There have been a number of turning points in this journey. You both have dropped statements that on the surface seemed inconsequential ,but in reality had a major impact. That is one thing I am *never* going to forget. You made 3 on the mark statements in that post. tblLocks is still here. It*is* a junction table, correct? It sure looks like one. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. Won't this cause a problem? With the re-key, they not only changed the lock, they changed the lock identifier. So if the key was previously, let's say, SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as my primary key, I would have thought this would be a nightmarish situation. No? If it happened once, there's always the possibility that it *could* happen again. Keys are lost pretty much on a weekly basis. What do you thinK? Do I need an inactive button for keys? I don't know exactly *how* this would work. Let me know what you think about that and the master keys. Don't concern yourself if you don't have time. I have problems in the db that I need to straighten out. Thanks so much for your time Bruce. -- Aria W. "BruceM" wrote: I don't have much time to reply today, but I have noted a few things inline. "Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? |
#96
|
|||
|
|||
Still Struggling...
"Aria" wrote in message ... First, thank you for your honesty. I smiled reading your post. That is the same thing that happens to me. I think, "hmmm...it could be like this...but then what about that...oh, and I forgot to account for this...but then I have to switch that...and the next thing you know, I'm right back where I started. g I don't think you go through quite the same thing because you have a *lot* more experience but it *is* confusing. While keys are what drove my desire to create this db in the first place, this is also the section I have been dreading. I reviewed all of our posts and took notes on what was suggested and any decisions that were made. When I refer to a statement as from a post in the beginning, that's what I mean. So, let's see if we can make any progress on some of these areas. Is the vault key just like any other key for a particular lock, except that it is in the vault? Yes. If so, is the vault copy essentially a clean copy to be used as the master for additional copies? No, although at times they may come and borrow a key to make a quick copy if they are already on campus and I just need one or two. It's rare. The district has its own locksmith who are permanent staff. They have the originals or blanks or whatever they call them. If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault just as you would assign it to a person. Ok, what happened here? We have apparently switched our origianl positions. I suggested assigning the vault key like an employee key in the very first post, but after learning about table attributes from the two of you ( I will never forget tblSubs), I no longer hold that position. You two are absolutely right. It isn't an employee attribute. I don't think it should be a yes/no field because of the sheer number of keys we're talking about...approx. 400. Am I going to run into problems with that many yes/no boxes? You may be right about this. I'm not sure because again, I don't have the experience behind me. So where does that leave us? I don't know if it is a switch or not, but the fact is you are assigning the key to the vault. Take a look at this: http://www.mvps.org/access/forms/frm0043.htm It explains how to produce a list such as this: (Vault) Adams, John Adams, John Quincy Jefferson, Thomas Madison, James Monroe, James Washington, George When you select (Vault) you can have the AssignedTo field be null or 0. In a query you could have something like this: AssignedEntity: IIf([AssignedTo] Is Null,"Vault",[LastName] & ", " & [FirstName]) The code in the link I provided (use the part at the top of the page, not the part that talks about a Value List) adds a sort of artificial record to the Employees listing, but it *does not* create an employee record. Are you oppossed to the vault table or vault as a location in tbllocations(now that I think about it, somethings wrong with this line of thinking but I'm not sure what)? The vault is issued a key. We can modify the part about keys being assigned to people to say that keys are assigned to entities including people. The vault is in a location, but it is not a location. If the vault has a keyed lock there is a corresponding record in tblLocks, including Location information. Regarding the Locks table, I don't think it would be a junction table. I 'm now confused about the purpose and fields in this table. You had posted in the beginning that it should include lookup tables for campus, wing and roomtype. I by no means expect you to remember this because I didn't either. There was a lot of discussion about a great many things. I'm only mentioning it now because you asked me to post my table structure for this part of the db. I was unsure about some of the structure and wanted to make sure I had it right before I posted. It was a confusing mess that I had to keep looking at. I couldn't make heads nor tails of what it should be. I had to live with it awhile. I had to sit down and go through all the posts suggestions and advice, move fields from one place to another, change table names and add fields and lookup tables. When I finished the only fields left in tblLocker were KeyID and LocationID. I originally had campus and wing in this section but once we changed tblRooms to tblLocations, it seemed better suited in tblLocations. Since I was now left with only 2 fields in tblLocks, I started wondering if it was a junction table. Let's say Room 222 of Building A on the South campus has a lock with Serial Number 12345. Your tblLocks would be something like this: tblLocks LockID LNumber LCampus LBuilding LLocation 111 12345 South Building A Room 222 You can store numbers instead of text for LBuilding, etc. If somebody changes the building name the keys will remain the same, and all records will reflect the new name. For now just assume the text value is stored. One Lock can have many keys. Therefore tblKeys is something like this: tblKeys KeyID (PK) LockID (FK) KeyNumber (SA-12 or whatever) Retired (Yes/No) The Key records for Lock 111 could be something like this: KeyID LockID KeyNumber 1 111 SA-1 2 111 SA-2 3 111 SA-3 Note that each key record is associated with a Lock record. KeyID is not a part of the lock record, but rather the other way around. The lock has keys. The key does not have locks. You will note I have used several location fields (Campus, Building, and Location). If several buildings have a Room 222, or several campuses have a Maintenance Shed, or something like that, then you will need all three location types to identify where you are in at least some cases. After selecting South Campus the Building combo box could be limited to just buildings on the South Campus. Similarly, the Room combo box could limit Room Numbers in the selected building. More on this later, if you like, but you can ignore it for now. There is no need to keep an active listing of keys nobody can find. lol ...good one! No, let's not. There are more such listings than you may imagine. I'm going to post my table structure later today, even though it still needs work. I know there are mistakes and that there is plenty of room for improvement, but you have posted additional comments that I believe (if I'm not mistaken) may have been addressed within the structure. I will wait to see what you post, and will add some comments tomorrow, including (if I can) a strategy for the junction table for storing KeyAssignment information, but after that I will be away for a week. I guess Sean will be back, so I hope I'm not at cross purposes with him to too great an extent. -- Aria W. "BruceM" wrote: Is the vault key just like any other key for a particular lock, except that it is in the vault? If so, is the vault copy essentially a clean copy to be used as the master for additional copies? If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault just as you would assign it to a person. To do this you could add (Vault) to the top of the drop-down list of Employees to whom keys are issued, similar to the way some drop-down lists show (All) at the top. I won't go too far down that road until I hear back from you. Regarding the Locks table, I don't think it would be a junction table. A lock is an entity with certain characteristics such as brand, location, date installed, and so forth. One Lock can have many keys. Iff Master Keys are included in the thinking, one Key can be for many Locks, but in this case the junction table would be tblKeyLock or something like that. Regarding tblKey, I expect there should be a Lost field or something like that. There is no need to keep an active listing of keys nobody can find. Regarding the question of natural key or surrogate key (any "artificial identifier", including autonumber), it really doesn't matter as long as the "natural" number such as SA-2 is used once for one physical (metal) key, and never used again. If you are not sure this is the case, autonumber would be a simpler choice as the PK. The same idea comes into play with the Lock identifier, I expect. If they change the lock in such a way that the old keys can be used you will need to update the FK of those keys so that they are associated with the new lock. From an earlier post you wrote: Suggestions still on the table: 1. Create tblLocations to replace tblRooms Sounds like a good idea .. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. Already discussed. Let me know if something is unclear, or if I am missing something 3. Create Master Key table to account for the special attributes of Master Keys. One possibility is to see the situation as One Lock Many Keys and One Key Many Locks, in which tblKeyLock is needed to resolve the relationship for all locks and all keys. However, I think a separate table for MasterKeys and a junction table tblKeyLock would be simpler to manage. If you are looking at a Lock record you would have a subform listing the ordinary keys and the person to whom they are assigned (some may not be assigned at all, I expect). One Lock Many Keys, so there is a 1:M between tblLock and tblKey, and the subform is based on tblKey. Another subform based on tblKeyLock could list the master key holders. One lock could have keys assigned to many people, and each person could be assigned keys. Another consideration is that a Lock record should have a listing of available keys. If it was me I think I would have an AssignedTo field in tblKeys: tblKeys KeyID (PK) LockID (FK to tblLocks) KeyCode (S2-A, etc., or whatever) AssignedTo (FK to tblEmployees) AssignedDate Retired (Yes/No) If I wanted to list people and the keys they hold I would use a query. This is not necessarily the best design in that AssignedTo and AssignedDate are not really attributes of keys. The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person. Similarly, one lock may have many keys, but other than master keys a key may open only one lock. On the other hand, one person may open many locks, and a lock may be opened by many people. By the way, it just occurred to me that if there may be several locks keyed to accept the same key you may need to add a LockLocation table related to tblLocks to take care of this detail. One solution here may be to have a LockPerson junction table (one person many locks and vice versa). This would be the source for a subform on the Locks form. A list box could contain a listing of unassigned keys for that lock. The list box row source would have to be built as you go, since the available keys are always changing. When somebody needs to be assigned a key you would go to the Lock form, see the listing of available keys, and created a new LockPerson record that stores the KeyID, EmployeeID, and maybe AssignedDate and other details. It may be worth your while to start a new thread on this specific topic. All you would need to say is that you have: A Locks table containing the LockId, LockLocation, etc. A Keys table containing KeyID (PK), LockID (FK), KeyCode, etc. An Employee table Explain that you understand a Lock may have many keys, and that a person may be assigned many keys. However, you are unsure how to store the AssignedTo information for Keys. Should it be in the Keys table? If not, how is that relationship modeled. I suggest this because frankly I am unsure how best to proceed on this point, and in a new thread you would attract the attention of very experienced designers. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Sounds good. ********** "Aria" wrote in message ... I think I may have a problem that I need to work through. We can discuss this when you have time. I just wanted to get this down before I lose track and start dealing with other issues. I think we are going to need to re-visit an issue from the very first post: vault keys. Originally, we decided that the situation was unclear and dropped it. In looking through some of the data that I copied to help map my direction, I just noticed something that I probably saw before but did not grab my attention as it has now. During the re-key, among the many replacement keys made, one key was important enough to be "assigned" to the vault. There are probably others, but that happened before my time so I am not aware of the previous history. This does change things a bit. I'm going to have to account for not only that key, but all of the vault keys. I have two trains of thought right now: The first is that the vault key is a sub type key that will need its own table. I don't know about this because it doesn't seem to fit the model of a sub type (at least not as far as I can see). It's not like the masters which are actually a different type of key. Their attributes are significantly different. Not so with the vault key; there's nothing special or different about them. The second thought is that this is really a location type and will need to be included with tbllocations or is it tbllocationtypes. I'm still working on this because I'm unsure right now. It does seem to fit the "where" model though. Just throwing it out there... -- Aria W. "Aria" wrote: I don't have much time to reply today, but I have noted a few things inline. Please, don't give it a second thought. I appreciate all of the support you have both given me. I understand. If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. I'll check on that. There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Yeah, no kidding. I started thinking about that because I was trying to avoid adding yet another junction table. I recall a previous discussion where we decided that the room phone could go into tblLocations. That would leave district cell phone orphaned and I could not figure out where to put it. It doesn't belong in tblLocations and doesn't belong in tblEmployees so I figured why am I drawing the line at this junction table when there are 5 others just like it. Oh well...c'est la vie! I keep looking at the relationship diagram and I 'm concerned how I will manage all of this. In particular, what kind of form to design and whether I should have another subform for tbKleysRequests on frmEmployees. I'm trying not to borrow trouble; one thing at a time. What became of tblLocks? If one lock may have several keys, locks are the top level. "A lock can have several keys". There have been a number of turning points in this journey. You both have dropped statements that on the surface seemed inconsequential ,but in reality had a major impact. That is one thing I am *never* going to forget. You made 3 on the mark statements in that post. tblLocks is still here. It*is* a junction table, correct? It sure looks like one. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. Won't this cause a problem? With the re-key, they not only changed the lock, they changed the lock identifier. So if the key was previously, let's say, SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as my primary key, I would have thought this would be a nightmarish situation. No? If it happened once, there's always the possibility that it *could* happen again. Keys are lost pretty much on a weekly basis. What do you thinK? Do I need an inactive button for keys? I don't know exactly *how* this would work. Let me know what you think about that and the master keys. Don't concern yourself if you don't have time. I have problems in the db that I need to straighten out. Thanks so much for your time Bruce. -- Aria W. "BruceM" wrote: I don't have much time to reply today, but I have noted a few things inline. "Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? |
#97
|
|||
|
|||
Still Struggling...
Here's the structure, mistakes and all:
tblKeys 1:M tblMasterKeys KeyID PK (Autonumber) KeyName MstrKeyID (FK, number long integer to tblMasterKeys) tblMasterKeys MstrKeyID PK (Autonumber) MstrKeyName tblKeysEmployees M:M tblEmployees KeyID (1/2 PK, FK to tblKeys, number, l.i.) EmpID (1/2 PK, FK to tblEmployees, #, l.i.) AllowedtoRetain Yes/No Approvedby DateIssued Date/Time DateLost Date/Time DateRtrnd Date/Time tblKeysRequests 1:M tblKeysEmployees RequestID PK (Autonumber) KeyID FK, #, l.i. to tblKeysEmployees QtyRqstd DateRqstd Date/Time DateIssued Date/Time DateIssued (Hmmm...I just noticed that this is the same as tblKeysEmployees) Funny how you can gloss right over things. Comments tblLocks (I thought this was a junction table but now I don't know) KeyID PK/FK LocationID PK/FK tblLocations 1:M tblLocks LocationID PK (Autonumber) LocationTypeID FK to tblLocationTypes LocationName Remarks tblLocationTypes 1:M tblLocations LocationTypeID PK (Autonumber) LocationType tblCampuses 1:M tblLocations CampusID PK (Autonumber) CampusName tblWings 1:M tblLocations WingsID PK (Autonumber) WingName So I think tblKeysEmployees defines the relationship between keys and employees. Regarding other parts of your previous post: The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person. No, this is not quite true. One key can be assigned to one or many people. One solution here may be to have a LockPerson junction table (one person many locks and vice versa). ....*another* junction table?! Please...is there any way around this? This would be the source for a subform on the Locks form. A list box could contain a listing of unassigned keys for that lock. The list box row source would have to be built as you go, since the available keys are always changing. When somebody needs to be assigned a key you would go to the Lock form, see the listing of available keys, and created a new LockPerson record that stores the KeyID, EmployeeID, and maybe AssignedDate and other details. It would be nice to have a list of unassigned keys but...it's a little overwhelming right now. This just keeps growing and growing. I'm already onto page 2 for printing the relationship diagram. Let me live with this for awhile. I know yesterday I posted, "Oh well, c'est la vie" as far as junction tables but today I can't deal with another junction table. BTW: tblKeys KeyID (PK) LockID (FK to tblLocks) KeyCode (S2-A, etc., or whatever) AssignedTo (FK to tblEmployees) AssignedDate Retired (Yes/No) I like the field name KeyCode. I'm not so sure about the Retired (Yes/No) field although that does have to be taken into account. I will probably end up doing it that way. Let me think about it some more. Let me know if this structure resolves *any* of the issues we have. -- Aria W. "BruceM" wrote: Is the vault key just like any other key for a particular lock, except that it is in the vault? If so, is the vault copy essentially a clean copy to be used as the master for additional copies? If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault just as you would assign it to a person. To do this you could add (Vault) to the top of the drop-down list of Employees to whom keys are issued, similar to the way some drop-down lists show (All) at the top. I won't go too far down that road until I hear back from you. Regarding the Locks table, I don't think it would be a junction table. A lock is an entity with certain characteristics such as brand, location, date installed, and so forth. One Lock can have many keys. Iff Master Keys are included in the thinking, one Key can be for many Locks, but in this case the junction table would be tblKeyLock or something like that. Regarding tblKey, I expect there should be a Lost field or something like that. There is no need to keep an active listing of keys nobody can find. Regarding the question of natural key or surrogate key (any "artificial identifier", including autonumber), it really doesn't matter as long as the "natural" number such as SA-2 is used once for one physical (metal) key, and never used again. If you are not sure this is the case, autonumber would be a simpler choice as the PK. The same idea comes into play with the Lock identifier, I expect. If they change the lock in such a way that the old keys can be used you will need to update the FK of those keys so that they are associated with the new lock. From an earlier post you wrote: Suggestions still on the table: 1. Create tblLocations to replace tblRooms Sounds like a good idea .. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. Already discussed. Let me know if something is unclear, or if I am missing something 3. Create Master Key table to account for the special attributes of Master Keys. One possibility is to see the situation as One Lock Many Keys and One Key Many Locks, in which tblKeyLock is needed to resolve the relationship for all locks and all keys. However, I think a separate table for MasterKeys and a junction table tblKeyLock would be simpler to manage. If you are looking at a Lock record you would have a subform listing the ordinary keys and the person to whom they are assigned (some may not be assigned at all, I expect). One Lock Many Keys, so there is a 1:M between tblLock and tblKey, and the subform is based on tblKey. Another subform based on tblKeyLock could list the master key holders. One lock could have keys assigned to many people, and each person could be assigned keys. Another consideration is that a Lock record should have a listing of available keys. If it was me I think I would have an AssignedTo field in tblKeys: tblKeys KeyID (PK) LockID (FK to tblLocks) KeyCode (S2-A, etc., or whatever) AssignedTo (FK to tblEmployees) AssignedDate Retired (Yes/No) If I wanted to list people and the keys they hold I would use a query. This is not necessarily the best design in that AssignedTo and AssignedDate are not really attributes of keys. The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person. Similarly, one lock may have many keys, but other than master keys a key may open only one lock. On the other hand, one person may open many locks, and a lock may be opened by many people. By the way, it just occurred to me that if there may be several locks keyed to accept the same key you may need to add a LockLocation table related to tblLocks to take care of this detail. One solution here may be to have a LockPerson junction table (one person many locks and vice versa). This would be the source for a subform on the Locks form. A list box could contain a listing of unassigned keys for that lock. The list box row source would have to be built as you go, since the available keys are always changing. When somebody needs to be assigned a key you would go to the Lock form, see the listing of available keys, and created a new LockPerson record that stores the KeyID, EmployeeID, and maybe AssignedDate and other details. It may be worth your while to start a new thread on this specific topic. All you would need to say is that you have: A Locks table containing the LockId, LockLocation, etc. A Keys table containing KeyID (PK), LockID (FK), KeyCode, etc. An Employee table Explain that you understand a Lock may have many keys, and that a person may be assigned many keys. However, you are unsure how to store the AssignedTo information for Keys. Should it be in the Keys table? If not, how is that relationship modeled. I suggest this because frankly I am unsure how best to proceed on this point, and in a new thread you would attract the attention of very experienced designers. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Sounds good. ********** "Aria" wrote in message ... I think I may have a problem that I need to work through. We can discuss this when you have time. I just wanted to get this down before I lose track and start dealing with other issues. I think we are going to need to re-visit an issue from the very first post: vault keys. Originally, we decided that the situation was unclear and dropped it. In looking through some of the data that I copied to help map my direction, I just noticed something that I probably saw before but did not grab my attention as it has now. During the re-key, among the many replacement keys made, one key was important enough to be "assigned" to the vault. There are probably others, but that happened before my time so I am not aware of the previous history. This does change things a bit. I'm going to have to account for not only that key, but all of the vault keys. I have two trains of thought right now: The first is that the vault key is a sub type key that will need its own table. I don't know about this because it doesn't seem to fit the model of a sub type (at least not as far as I can see). It's not like the masters which are actually a different type of key. Their attributes are significantly different. Not so with the vault key; there's nothing special or different about them. The second thought is that this is really a location type and will need to be included with tbllocations or is it tbllocationtypes. I'm still working on this because I'm unsure right now. It does seem to fit the "where" model though. Just throwing it out there... -- Aria W. "Aria" wrote: I don't have much time to reply today, but I have noted a few things inline. Please, don't give it a second thought. I appreciate all of the support you have both given me. I understand. If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. I'll check on that. There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Yeah, no kidding. I started thinking about that because I was trying to avoid adding yet another junction table. I recall a previous discussion where we decided that the room phone could go into tblLocations. That would leave district cell phone orphaned and I could not figure out where to put it. It doesn't belong in tblLocations and doesn't belong in tblEmployees so I figured why am I drawing the line at this junction table when there are 5 others just like it. Oh well...c'est la vie! I keep looking at the relationship diagram and I 'm concerned how I will manage all of this. In particular, what kind of form to design and whether I should have another subform for tbKleysRequests on frmEmployees. I'm trying not to borrow trouble; one thing at a time. What became of tblLocks? If one lock may have several keys, locks are the top level. "A lock can have several keys". There have been a number of turning points in this journey. You both have dropped statements that on the surface seemed inconsequential ,but in reality had a major impact. That is one thing I am *never* going to forget. You made 3 on the mark statements in that post. tblLocks is still here. It*is* a junction table, correct? It sure looks like one. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. Won't this cause a problem? With the re-key, they not only changed the lock, they changed the lock identifier. So if the key was previously, let's say, SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as my primary key, I would have thought this would be a nightmarish situation. No? If it happened once, there's always the possibility that it *could* happen again. Keys are lost pretty much on a weekly basis. What do you thinK? Do I need an inactive button for keys? I don't know exactly *how* this would work. Let me know what you think about that and the master keys. Don't concern yourself if you don't have time. I have problems in the db that I need to straighten out. Thanks so much for your time Bruce. -- Aria W. "BruceM" wrote: I don't have much time to reply today, but I have noted a few things inline. "Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? |
#98
|
|||
|
|||
Still Struggling...
This will probably be my last posting for a while, as I will be away next
week and need to finish up a few things before I go. "Aria" wrote in message ... Here's the structure, mistakes and all: tblKeys 1:M tblMasterKeys KeyID PK (Autonumber) KeyName MstrKeyID (FK, number long integer to tblMasterKeys) I still think the relationship is between keys and locks, not keys and other keys. A key opens one lock. Ignoring master keys for the moment, you have One Lock : Many Keys, so LockID is a FK in tblKeys. A master key opens several locks. One master key can open several locks, and each lock can be opened by several different keys including the master key. If there is to be a junction table I think it would be between locks and keys. Except for the master key this is a one-to-many relationship, but making it many-to-many even though the first "many" is applied infrequently is OK. There may be other ways to model this relationship, but this is one that occurs to me. tblMasterKeys MstrKeyID PK (Autonumber) MstrKeyName tblKeysEmployees M:M tblEmployees KeyID (1/2 PK, FK to tblKeys, number, l.i.) EmpID (1/2 PK, FK to tblEmployees, #, l.i.) AllowedtoRetain Yes/No Approvedby DateIssued Date/Time DateLost Date/Time DateRtrnd Date/Time The question here is how the lock fits into the picture. If you may want to see a listing of who can open a particular lock you need a way to associate locks with employees. tblKeysRequests 1:M tblKeysEmployees RequestID PK (Autonumber) KeyID FK, #, l.i. to tblKeysEmployees QtyRqstd DateRqstd Date/Time DateIssued Date/Time DateIssued (Hmmm...I just noticed that this is the same as tblKeysEmployees) Funny how you can gloss right over things. Comments tblLocks (I thought this was a junction table but now I don't know) KeyID PK/FK LocationID PK/FK It is not a junction table that I can see. One lock has many keys, not the other way around (except for master keys). In the case of a master key it is one of the many keys that can open a lock. tblLocations 1:M tblLocks LocationID PK (Autonumber) LocationTypeID FK to tblLocationTypes LocationName Remarks tblLocationTypes 1:M tblLocations LocationTypeID PK (Autonumber) LocationType tblCampuses 1:M tblLocations CampusID PK (Autonumber) CampusName tblWings 1:M tblLocations WingsID PK (Autonumber) WingName You may be cutting it a bit fine here. I have to disagree with Sean that all fields need to be filled in. It happens all the time that a Middle Initial field is not filled in, for instance. For another example, Apartment Number does not apply if the person lives in a house, but I see no reason in a database of this scale (i.e. not super large) to separate this datum into its own table. Find a way to identify where the lock is located. A lock will be located on a campus, and maybe in a building (unless it is an outdoors lock to an athletic field gate or something). If a building, it may have wings, or maybe not. You can use a lookup table to insert a value into, say, the Wing or Building field in the Locks table. You can even limit the Wing listing to just the Wings in the selected Building. However, you may not need to involve these lookup tables into relationships. So I think tblKeysEmployees defines the relationship between keys and employees. Regarding other parts of your previous post: The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person. No, this is not quite true. One key can be assigned to one or many people. One solution here may be to have a LockPerson junction table (one person many locks and vice versa). ...*another* junction table?! Please...is there any way around this? This is in place of the KeyEmployee junction table, which I maintain is not exactly the relationship you should have. This would be the source for a subform on the Locks form. A list box could contain a listing of unassigned keys for that lock. The list box row source would have to be built as you go, since the available keys are always changing. When somebody needs to be assigned a key you would go to the Lock form, see the listing of available keys, and created a new LockPerson record that stores the KeyID, EmployeeID, and maybe AssignedDate and other details. It would be nice to have a list of unassigned keys but...it's a little overwhelming right now. This just keeps growing and growing. I'm already onto page 2 for printing the relationship diagram. Let me live with this for awhile. I know yesterday I posted, "Oh well, c'est la vie" as far as junction tables but today I can't deal with another junction table. You would use SQL to insert a list into a list box, or maybe into a continuous form. You do not need another table. The data are already there (or will be when this is all set up). BTW: tblKeys KeyID (PK) LockID (FK to tblLocks) KeyCode (S2-A, etc., or whatever) AssignedTo (FK to tblEmployees) AssignedDate Retired (Yes/No) I like the field name KeyCode. I'm not so sure about the Retired (Yes/No) field although that does have to be taken into account. I will probably end up doing it that way. Let me think about it some more. I meant Retired to refer to the key, in case that is not clear. The point is that if a key is lost or damaged or worn out the Retired field can be used to exclude it from a listing of available keys. Let me know if this structure resolves *any* of the issues we have. -- Aria W. Perhaps Sean will have some further insight, and perhaps he and I are not at cross purposes. I repeat that I think it will be a good thing to take what you have and start another thread. I would have asked some questions of the group by now, as I am uncertain in some cases how to model this situation. Instead I have been stretching my mind and my capabilities trying to figure this out. It has been good practice, but I am about at the limit of what I can suggest with confidence. I will check in again, or search for other threads from you, when I return. In the meantime, best of luck. I am confident you will get this figured out, even if you doubt it from time to time. "BruceM" wrote: Is the vault key just like any other key for a particular lock, except that it is in the vault? If so, is the vault copy essentially a clean copy to be used as the master for additional copies? If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault just as you would assign it to a person. To do this you could add (Vault) to the top of the drop-down list of Employees to whom keys are issued, similar to the way some drop-down lists show (All) at the top. I won't go too far down that road until I hear back from you. Regarding the Locks table, I don't think it would be a junction table. A lock is an entity with certain characteristics such as brand, location, date installed, and so forth. One Lock can have many keys. Iff Master Keys are included in the thinking, one Key can be for many Locks, but in this case the junction table would be tblKeyLock or something like that. Regarding tblKey, I expect there should be a Lost field or something like that. There is no need to keep an active listing of keys nobody can find. Regarding the question of natural key or surrogate key (any "artificial identifier", including autonumber), it really doesn't matter as long as the "natural" number such as SA-2 is used once for one physical (metal) key, and never used again. If you are not sure this is the case, autonumber would be a simpler choice as the PK. The same idea comes into play with the Lock identifier, I expect. If they change the lock in such a way that the old keys can be used you will need to update the FK of those keys so that they are associated with the new lock. From an earlier post you wrote: Suggestions still on the table: 1. Create tblLocations to replace tblRooms Sounds like a good idea .. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. Already discussed. Let me know if something is unclear, or if I am missing something 3. Create Master Key table to account for the special attributes of Master Keys. One possibility is to see the situation as One Lock Many Keys and One Key Many Locks, in which tblKeyLock is needed to resolve the relationship for all locks and all keys. However, I think a separate table for MasterKeys and a junction table tblKeyLock would be simpler to manage. If you are looking at a Lock record you would have a subform listing the ordinary keys and the person to whom they are assigned (some may not be assigned at all, I expect). One Lock Many Keys, so there is a 1:M between tblLock and tblKey, and the subform is based on tblKey. Another subform based on tblKeyLock could list the master key holders. One lock could have keys assigned to many people, and each person could be assigned keys. Another consideration is that a Lock record should have a listing of available keys. If it was me I think I would have an AssignedTo field in tblKeys: tblKeys KeyID (PK) LockID (FK to tblLocks) KeyCode (S2-A, etc., or whatever) AssignedTo (FK to tblEmployees) AssignedDate Retired (Yes/No) If I wanted to list people and the keys they hold I would use a query. This is not necessarily the best design in that AssignedTo and AssignedDate are not really attributes of keys. The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person. Similarly, one lock may have many keys, but other than master keys a key may open only one lock. On the other hand, one person may open many locks, and a lock may be opened by many people. By the way, it just occurred to me that if there may be several locks keyed to accept the same key you may need to add a LockLocation table related to tblLocks to take care of this detail. One solution here may be to have a LockPerson junction table (one person many locks and vice versa). This would be the source for a subform on the Locks form. A list box could contain a listing of unassigned keys for that lock. The list box row source would have to be built as you go, since the available keys are always changing. When somebody needs to be assigned a key you would go to the Lock form, see the listing of available keys, and created a new LockPerson record that stores the KeyID, EmployeeID, and maybe AssignedDate and other details. It may be worth your while to start a new thread on this specific topic. All you would need to say is that you have: A Locks table containing the LockId, LockLocation, etc. A Keys table containing KeyID (PK), LockID (FK), KeyCode, etc. An Employee table Explain that you understand a Lock may have many keys, and that a person may be assigned many keys. However, you are unsure how to store the AssignedTo information for Keys. Should it be in the Keys table? If not, how is that relationship modeled. I suggest this because frankly I am unsure how best to proceed on this point, and in a new thread you would attract the attention of very experienced designers. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Sounds good. ********** "Aria" wrote in message ... I think I may have a problem that I need to work through. We can discuss this when you have time. I just wanted to get this down before I lose track and start dealing with other issues. I think we are going to need to re-visit an issue from the very first post: vault keys. Originally, we decided that the situation was unclear and dropped it. In looking through some of the data that I copied to help map my direction, I just noticed something that I probably saw before but did not grab my attention as it has now. During the re-key, among the many replacement keys made, one key was important enough to be "assigned" to the vault. There are probably others, but that happened before my time so I am not aware of the previous history. This does change things a bit. I'm going to have to account for not only that key, but all of the vault keys. I have two trains of thought right now: The first is that the vault key is a sub type key that will need its own table. I don't know about this because it doesn't seem to fit the model of a sub type (at least not as far as I can see). It's not like the masters which are actually a different type of key. Their attributes are significantly different. Not so with the vault key; there's nothing special or different about them. The second thought is that this is really a location type and will need to be included with tbllocations or is it tbllocationtypes. I'm still working on this because I'm unsure right now. It does seem to fit the "where" model though. Just throwing it out there... -- Aria W. "Aria" wrote: I don't have much time to reply today, but I have noted a few things inline. Please, don't give it a second thought. I appreciate all of the support you have both given me. I understand. If you select the combo box, open the Property Sheet (with the tabs for Format, Data, etc.), select an event, click the three dots, click Code Builder, and click OK the name of the control will be filled in for you in the code window. If you type it yourself you need the underscore. I think that is so there are no spaces in the procedure name. I'll check on that. There's no definitive answer for that, but anything that involves junction tables is a lot to get your mind around if you are just starting. Before long, if it hasn't already happened, the need for junction tables, and how to use them, will be clear in your mind. In your case there were a number of junction tables, and a situation that involved keys, locks, people to whom the keys were issued, locations of the locks, campuses, and a number of other entities, with a variety of relationships between the entities. Yeah, no kidding. I started thinking about that because I was trying to avoid adding yet another junction table. I recall a previous discussion where we decided that the room phone could go into tblLocations. That would leave district cell phone orphaned and I could not figure out where to put it. It doesn't belong in tblLocations and doesn't belong in tblEmployees so I figured why am I drawing the line at this junction table when there are 5 others just like it. Oh well...c'est la vie! I keep looking at the relationship diagram and I 'm concerned how I will manage all of this. In particular, what kind of form to design and whether I should have another subform for tbKleysRequests on frmEmployees. I'm trying not to borrow trouble; one thing at a time. What became of tblLocks? If one lock may have several keys, locks are the top level. "A lock can have several keys". There have been a number of turning points in this journey. You both have dropped statements that on the surface seemed inconsequential ,but in reality had a major impact. That is one thing I am *never* going to forget. You made 3 on the mark statements in that post. tblLocks is still here. It*is* a junction table, correct? It sure looks like one. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. There's nothing wrong with a natural key, depending on what it is. With a surrogate key such as autonumber the main thing is that the record needs to be unique for reasons apart from the autonumber field. If two records are identical except for an artificial identifier then they are not really unique. The autonumber is a convenience, but cannot by itself enforce what is known as a unique constraint. Won't this cause a problem? With the re-key, they not only changed the lock, they changed the lock identifier. So if the key was previously, let's say, SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as my primary key, I would have thought this would be a nightmarish situation. No? If it happened once, there's always the possibility that it *could* happen again. Keys are lost pretty much on a weekly basis. What do you thinK? Do I need an inactive button for keys? I don't know exactly *how* this would work. Let me know what you think about that and the master keys. Don't concern yourself if you don't have time. I have problems in the db that I need to straighten out. Thanks so much for your time Bruce. -- Aria W. "BruceM" wrote: I don't have much time to reply today, but I have noted a few things inline. "Aria" wrote in message ... I found the compile button. It was under commands so I added it to the toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? |
Thread Tools | |
Display Modes | |
|
|