Custom Message for Duplicate Entries

[Chipster]

Thanks... I kind of get it now!

Going forward, I would first go backwards and fix those tables. Have a look here...
http://www.access-diva.com/dm7.html

This is just an example to show more of what we should be seeing for Table structure.

I realized you inherited this database but what you inherited was a database that thinks its an Excel Spreadsheet on steroids. Fixing that will make a lot of things easier, including adding a new Student.

Wow, you are good. Currently some of this is tracked using a basic excel workbook and I am trying to streamline that process as well as include all of the things that my office tracks. Let me take a look at this link and I'll get started on fixing things.

 

[GinaWhipp]

Thanks!

That link also comes with a download, so you can open and *play* with it!

 

[GinaWhipp]

I have uploaded my db again with all objects. Take a look at the ProductionCharts report to see what I am tracking. As far as your question here, my assumption is that the fewer tables you have to link with relationships, the easier it is to pull queries and reports, etc. Also, I always seem to run into issues trying to update records when say a form is based on a query that is based on two tables. Your guidance is appreciated.

I'm going to hold on this one until you review the link I posted. I did want to make a comment on the Relationships. It is not true that fewer Relationships makes it easier to pull data. While you can overdo under-doing is not a good thing.

As for UPDATE queries, very tricky and best done on one Table. That said, if you have to do it on two we will look into how to best do so the query does not *shut down*.

 

[Chipster]

I looked through the various links noted above. I can see where a there are naming conventions for field names that may better improve my db. It seems there are some who would use the tblStudentInfo naming convention and some who would use StudentInfo. The latter is more of what I was using, but again I could better name my fields within each table.

Going forward, I would first go backwards and fix those tables. Have a look here...
http://www.access-diva.com/dm7.html
This is just an example to show more of what we should be seeing for Table structure.

As for the sample db in this quote, I attempted to add some sample data to it but I don't quite understand how it works. I added a student to the tblStudents table and a couple courses to the tblCourses table. However, when I attempted to add a record to the tblStudentCourses table, I was told that a record already existed in the tblStudents table. Also, when I attempted to add a record to the tblStudentCourses table where there was no record in the tblStudents table, I received an error that a student must exist in the tblStudent table. Confused on this db's operations, but I somewhat understand its design.

What should be my first step in fixing my db? Field naming convention?

 

[GinaWhipp]

And that should have happened. Usage is...

To enter Courses you would need a Main Form for Courses (or Students) and then the Subform would be based off of tblStudentCourses. You could then select a Course from the Main Form and then add Students to the Subform based on already existing Students in the Combo Box that is set on the Form. Clear now?

Now, if you wanted to add a new Student you would have Not_In_List event on the Student Combo Box that would pop-up a Form for Data Entry. Once completed and closed would then add that new Student to the Combo Box.

Trying to add directly to the tblStudentCourses you would need to have an already existing Student and an already existing Course or that Table, also referred to as a Junction Table, will not accept the data. Okay?

 

[Chipster]

Ok, I do understand how this works better now, thanks. As for my students, we have thousands go through each year and a combo box would get very long at some point. My db currently has over 400 students with more being added daily. Also, each student is really treated independently therefore I would not need to add multiple students except new student to the Students table upon starting their first course, which by the way may be separate courses. Not all student follow the same path. For example, within the Primary course I mentioned earlier, there are 6 different "companies" that teach the same course. So some go to 1 and others go to a different "company" for their training.

What I'm after in the end is to update each student independently upon completion of each phase of training. So, I would need to open a form that allows me to add a new student to the students table OR allow me to search for a student to complete their phase of training. I'm hoping that this can be the same form as well as include fields from all phases (Primary, Advanced, and FRS) as well as sub-courses they may attend while awaiting the next phase. My db currently allows me to do most of this, but I'm trying to make it operate better.

 

[GinaWhipp]

Hmm, okay well... then working with your existing form I would still use a Combo Box so when typing in an existing Student ID it would come up BUT when entering a new one you could use the Not_In_List event which would then open a pop-up. Then the control would do double duty.

 

[Chipster]

Hmm, okay well... then working with your existing form I would still use a Combo Box so when typing in an existing Student ID it would come up BUT when entering a new one you could use the Not_In_List event which would then open a pop-up. Then the control would do double duty.

Gina, I just sent you a pm. Not sure the protocol on this kinda stuff, but if you can help educate me a little I would greatly appreciate it. If I just need to keep looking around, that is fine too....just let me know.

Thank you for your help. I think I need to take a big step back and rethink my entire db design.

-Chip

 

[GinaWhipp]

Saw it... I had to get some *real* work done and now I need to eat something. I will get back to you some time tonight.

 

[Chipster]

I went through a lot of the links that Gina recommended and tried to understand normalization as much as possible. I also used some of the recommendations on forms provided in this post. I have attached a version of my db showing just 3 tables that are related. I believe these tables are in a normalized state. Is this more appropriate than what I was doing previously?

Thanks

 

[Chipster]

I went through a lot of the links that Gina recommended and tried to understand normalization as much as possible. I also used some of the recommendations on forms provided in this post. I have attached a version of my db showing just 3 tables that are related. I believe these tables are in a normalized state. Is this more appropriate than what I was doing previously?

Thanks

I also thought about adding tables for specific Squadrons that teach each of the courses, for example:
VT-2, VT-3, VT-6, VT-27 & VT-28 all teach the Primary course
VT-7, VT-9, VT-21, VT-22, VT-31, VT-35, HT-8, HT-18, & HT-28 all teach Advanced courses
(Within the Advanced Courses are Strike (VT-7, 9, 21, 22), Maritime (VT-31), Tilt Rotor (VT-35), and Rotor(HT-8,18,28) courses). I'm not sure that I need to separate these into separate tables. I only track when each student completes Primary and Advanced and identify from which Squadron they completed these courses with a simple "Squadron" field within tblPrimary and tblWinging.

Also, is there a better forum for me to move this post to and how would I do that?

 

[GinaWhipp]

I am tied up doing some *real* work. I will get to this later today!

 

[GinaWhipp]

Hmm, what is the difference between tblPrimary and tblWinging? Because I am thinking they should be combined.

 

[Chipster]

Hmm, what is the difference between tblPrimary and tblWinging? Because I am thinking they should be combined.

They generally collect similar data, but in different phases of training. Not all students in tblPrimary will complete the second phase (tblWinging). I also have an attrition table formatted similarly, but only those students who fail any phase will have a record in that table. The last table is tblTAD, which includes any records from tblStudents who complete subcourses between phases (Primary & Advanced). I have included an EDIPI (which is a unique code for each member) field on each of these tble. My intent is that a main form based on tblStudents included subforms for the other tables and each subform could be updated whenever a phase is complete, student fails, or goes to a subcourse. Again, not all of these things will occur; most students attend Primary and then Advanced and then move on.

 

[GinaWhipp]

Hmm, I see my post did not go thru so trying again...

It should be one table for Training and you would separate by a combination of Training Types and using queries.