Form with sub form - duplicating info

[ITempleman]

I created my form with two sub forms (so far) the top form has the:
studentID. firstname, lastname, acadplan, acadlevel.

On one of the sub forms I have the date they came into the office, counsellor, reasons and notes. The second form will record the courses each student signs up for, the year and eventually the grade.

Now my problem is, that when I enter the data for the students, it should record one form and then multiple entries in the sub form. Well it is doing the latter but also duplicating the main form - so I have one student who comes twice to see a counsellor, it shows twice in the main form and then both appoints show in the sub form for both of the main forms. I have 519 main forms when in reality I should only have about 430-450.

I was really proud of myself for getting this far and thought I had cracked it.

Can anyone tell me if I should have been done something in the original table for the appointments ie - no duplicates for 'student number', but allow duplicates in the subform table.

Any help appreciated with grateful thanks.

 

[GinaWhipp]

Sounds like a query issue but it could be the way the tables are set up and joined. What are you table and their relationship to each other?

 

[XelaIrodavlas]

Have you set the Link Master and Link Child fields on your subform?

If not that I agree with Gina, how are your tables setup?

To be clear, which subform is causing the issue? The one that records office visits or the one recording the course they are attending?

I assume you've dealt with many-many relationships before (so please don't think i'm being patronising) You should be looking at a many-many between Student and Counsellor, but you can just as easily look at it like a One-Many between Students and 'OfficeVisits'.

Something like the attached?

Hope this helps!

 

[ITempleman]

I am trying to self teach myself many of these tasks using all the wonderful youtube videos - so I welcome any advice more specific to my situation.

I think my initial problem was I imported all the data from excel, and forgot to remove duplicates for the data that would go in the main form - which lists the students personal details.

My tables are linked as follows
table **** Content *** link *** record ** to table
2015_15 *** all student info *** 1 to 1 *** student ID *** appointments
(this the main form)
Appointments *** student info *** 1 to many *** student ID *** date seen

The studentID only shows on the main form, it is hidden in the subform

I have now imported the data and it looks ok (so far) but my next question is going to be really stupid (well it appears to be to me!).

I want people to be able to go to the form and type in the student number and it should bring up the students records. Most of the youtube videos only show tables that have 1 -- 6 records so they use the drop down box.

I have over 2000 records in my student table. At present I have input appointments for 343 students, for a total of 519 actual appointments. When you open the form it opens to the first student entered in 'studentID' order.

Is there away to set up the form so that when it opens it brings up fro example a blank record - the user enters the student number and then it autofills the student data (as it does now) but it would then automatically bring up the relevant sub form.

When I tried it with one of my existing appointments using a 'new record' and typed in the number for someone I know is already recorded as having had an appointment it just made the new record, autofilled the main form but it did not bring in the details already recorded in the sub form.

I apologize for my ignorance and no advice is viewed as being patronizing - you are the ones helping me!!!

 

[GinaWhipp]

Okay, so Excel is a flat file and Access is a relational file so no way simply importing is the correct table set up as you would need to *break up* the Excel spreadsheet. Following that path your tables should be something like...

tblStudents (only Student data)
sStudentID (PK)
etc...

tblCounselors
cCounselors (PK)
etc...

tblAppointments (only Appointment with Student ID only)
aAppointmentID (PK)
aStudentID (FK - related to tblStudents:sStudentID)
aCounselorID (FK - related to tblCounselors:cCounselors)
etc..

Is that what they look like?

Also, you must allow duplicates of the StudentID in tblAppointments because a Student can certainly have more than one appointment. You can control what shows on the Subform so no worries there.

 

[ITempleman]

I split the excel file into two sheets - the first contained all the information for the top form - Appointments - which lists the students that come to visit us and includes their studentID, name, last name, program and academic year - in this one they only show once as it records that they came to see us not how many times.

The second sheet in the excel file held the number of times they came to see us and why, which counsellor and any notes.

When I imported the data it put the info in the right place - I have attached a screen shot to show how I have it looks.

I was wondering if I should set up a query on the top of the form to find the student by their id (which I tried and it still didn't work).

Although.... after posting this I thought what if I did a query to find the student and put in an option that if they were not there, it opened a blank form ( as students from other departments come to see us who are not on my main 2015_16 list) - now I am getting way beyond my skills.

 

[GinaWhipp]

Hmm, where is the Students table? As you can see from my example you really should have a minimum of three tables.

 

[ITempleman]

all my 5 tables:

• 2015_16 - list all the academic details of the student, id, name, program, year - this is the MAIN table for nearly everything.
• TblAppointments - lists: studentID, first name, last name, program and year - it is linked on a one to one relationship with the 2015_16 table as the student should only show once in this table - the data for this table comes from the form and it autfills using the studnetnumber and automatically enters the data formthe 2015_16 table
• TDateSeen - lists date they came to office, counsellor they seen, reason and option to enter notes. - this is linked to the TblAppointments by the studentID on a one to many relationship as they will have multiple appointments the data for this table comes from the form
• Counsellors -- this just a list of the names of the counsellors and there is a one to many relationship with appointments - although I select them from a combo box as the student can see the same counsellor many times or one of the other 5 counsellors.
• Reason - this just a list of reasons, there is a one to many relationship with appointments - although again I select them from a combo box as the student can have one or multiple reasons to see someone

 

[GinaWhipp]

In a relational database there is no main table that holds everything. TblAppointments should only contain the Student ID and should be related to tblStudents.

TDateSeen (which in my mind is appointments) should be linked to TblAppointments by aAppointmentID not by Student ID.

So, table 2019_16 is just the imported data and should be broken up to individual tables and not included in any queries or used as a Recordsource for anything.

 

[pkraja]

To answer your question - "Is there away to set up the form so that when it opens it brings up fro example a blank record - the user enters the student number and then it autofills the student data (as it does now) but it would then automatically bring up the relevant sub form.". What I do is to created an unbound combo box field (Search_Student_Number) and then in the "After Update" Event Property, type "DoCmd.SearchForRecord , "", acFirst, "[Student_ID] = " & "'" & Screen.ActiveControl & "'"

 

[ITempleman]

It may be my terminology that is wrong as a non-DB person..

the 2015_16 table stores all the student data which was supplied to us by our central registrar's office

• The form is created based on two tables - appointment as the main and reasons as the sub form
• When I type in the student number in the form it auto-fills using the data from 2015_16
• I then type in the data for the sub form, selecting the date from the calendar, counsellor and reason from the relevant combo box and add any note if relevant.
• this data is then input into the 'appointment table' and the data from the sub form is input into the 'dateseen' table.

 

[GinaWhipp]

Hmm, could be me not understanding, so let's take this a bit slower...

Table 2015_16 are there duplicate Student Names?

 

[ITempleman]

Hi Gina;
table 2015_16 has 2000 plus records - none are duplicated as the studentID (student number) is specific to each student. other items in this table can be duplicated ie names, the program and academic level

we could in a sense ignore the counsellor or reason table just now.

Appointment form - when I type in the student number - it brings in the data from the 2015_16 table. If the student is not in the 2015_16 table then I can manually type them in, and I have created an append query that I can run and it adds them to the 2015_16 table. - everything in the appointment form wil be recorded in the Tappointments.

in the sub form I type in:

• the date they came into the office
• the counsellor they seen
• the reason they came into the office
• any specific notes from the appointment if relevant.

all date entered into the subform is stored in the TDateseen

my problem is when entering the data I want the user to be able to type in the studentID - NOT SURE WHERE, THIS MIGHT BE THE PROBLEM - it auto fills, the data with the student specific date from 2015_16 - but also when it autofills it will also show on the screen the associated sub form showing all the appointments that the student has had over this academic year.

I had posted a screen shot of the form above, so hopefully it makes more sense

I REALLY appreciate your patience...

 

[GinaWhipp]

Okay, I think I got it...

Now, the Recordsource for the Subform is?

No, problem... *patience* is my middle name!

 

[ITempleman]

Hi Gina,

not sure what happened to the reply I had sent on Friday so I will redo it and hope it makes sense- here goes:
I could in the first instance ignore the counsellor and reason tables as I use them from a combo box although I do have a relationship with the appointments table

2015_16 Tblappointments TblDAteSeen
StudentID (PK) -- 1 to 1 StudentID (PK) --- 1 to many StudentID
AcadPlan -- FirstName --- Autonumber (PK)
LastName -- LastName --- Date_Attended
FirstName -- AcadPlan --- Counsellor
Email -- AcadLevel --- Reason
Sex -- --- -- Notes
AcadLevel --

The student number should only be allowed once in the Appointments main form and it does not show in the actual sub form as it is hidden but they will be shown many times as they can come to the office more than once.

In the 2015_16 table I have 2000+ records and if I add a student to the appointments who is not in the 2015_16 table (this happens when students from another department come to see us) I run an append query after I finish updating the appointments to add the students details to the 2015_16 table.

I hope this makes sense...

 

[ITempleman]

Have you set the Link Master and Link Child fields on your subform?

If not that I agree with Gina, how are your tables setup?

To be clear, which subform is causing the issue? The one that records office visits or the one recording the course they are attending?

I assume you've dealt with many-many relationships before (so please don't think i'm being patronising) You should be looking at a many-many between Student and Counsellor, but you can just as easily look at it like a One-Many between Students and 'OfficeVisits'.

Something like the attached?

Hope this helps!

thanks Alex, this looks pretty good - my big problem is, how to bring up the students record to input the data. I need to have a field where I can enter the student number and the existing form appears for that student - but I have no idea how to do it - there are too many to scroll through - easy to add a form for a new students, but not update.

 

[GinaWhipp]

Monday kind of crazy for me.. will get back to you as soon as I get a free minute (or two)

 

[GinaWhipp]

Okay, so the big problem is you can't only allow 1 Student ID in the Appointments table as Students till have more than one appointment. If you don't change that (along with how you are storing the data) you will never be able to add another appointment.

Then you can use a Combo Box on the Main Form to look-up a Student and pull in all the pertinent information.