Filter out records that have the same data

[JoeBruce]

Note: This is not asking how to make a query to eliminate duplicate records.

My database is for an education program, and stores the teacher info and data on the programs they have attended. Sometimes, a program is given to multiple classes on the same day - this means that 2 or more teachers might have the same program on the same day, and the total number of students at the program is also the same.

I am designing a query to return statistics on how many students we have given programs to. However, if Teacher A and Teacher B combined their classes into one program, the query will count that program twice (because the teachers are on separate records, and both get credit for the program). How would I go about filtering out the duplicate info (keeping only one program statistic)? In this scenario, fields such as Date of Program, School Name, and Name of Program would all be the same, so I would somehow use those for criterion.

FYI, I am still very new to Access, so I struggle with making codes and macros and many advanced features. I can figure the stuff out, but appreciate as many details as can be provided. Thanks for any help!

[ranman256]

Make a query on these and set the query propery UNIQUE VALUE = true.
you will get only 1 record.

[aytee111]

Do you keep the students' names? You could use that. Or is there a standard/average number of students? If that number is too high maybe you could know that way that it was combined. Do you have more than two teachers at the same school - with or without combining?

[JoeBruce]

@ ranman256 - Do you mean make a separate query using the date, school name, program name (etc.) fields? Or is this something to enter as a criteria? If it's the former, I assume there is a way to build a query from a query (something I can probably figure out on my own).

@ aytee111 - No student names are recorded; number of students will be all over the place, so that won't work (but good ideas). There will be multiple teachers from the same school, and there will be scenarios that those multiple teachers participated in the same program, and other scenarios where they were in different programs. Hopefully that clarifies your questions.

[aytee111]

Somehow you have to know and with the options you have provided there is no obvious way. The only other way is to set it manually, update a table with the information that it was a combined class.

[JoeBruce]

I have considered this problem from the perspective of my data (i.e. adding or changing something to facilitate the query). Perhaps a simple yes/no field on the form that indicates if the program was attended by multiple teachers. It would not be an extreme amount of work to go back and update the records with this new field.

However I feel that there is enough information present in the data, and Access has enough tools and capabilities, to implement a process to accomplish my goal. Perhaps a (somewhat complicated) if/then statement? Any thoughts?

Thanks for the insight so far.

[aytee111]

We are still missing that one (or more) criteria. Even if you use code and If's and whatever you still need to look at something. Date same, teachers variable, students variable - what else do you have? Start/end time?

[JoeBruce]

I'm going to type this out as I think and maybe I'll stumble upon the answer

If the number of students are the same, the date is the same, and the program name/type are the same, but the teachers are different, then that indicates the same program was given to multiple teachers, and all but one of those records should be eliminated from my query results.

Re-reading your last post, I think there was some confusion in one of my previous replies; the number of students is different for the various programs I do, but for this particular problem the number of students will be listed as the same in the multiple records (i.e. Teacher A has 30 students, Teacher B has 20, and they both bring their class on a field trip - Teachers A & B have separate records that show their attendance to the program, and both records will indicate 50 students for both Teachers).

[aytee111]

Yes, confusion - on my part! To go back to basics:

how many students we have given programs to

Why are we worrying about teachers?

[JoeBruce]

"Number of Students" is just a field within my "Programs Attended" table, but Teachers have a specific record (they are in their own table that is joined to Programs Attended). I want to get the sum total of students, but a simple query will return a bloated number, since I've had programs that multiple teachers attend. Big picture, I want the data to show (give "credit") all Teachers that were a part of the program; but when I count my stats (i.e. run the query) I don't want the same program counted twice - the only way to differentiate would be the Teacher.

Hope that helps. Thanks for sticking with me so far - I do appreciate it! Might not be able to make any more replies today, but I will be back at this tomorrow.

[aytee111]

Two separate issues here - (1) number of students per program and (2) teacher/program stats, giving credit. The trouble you run into is when you try and mix it all up! Such as counting how many students per teacher per program because of the way your data is structured. If I were you I would consider changing it to be like that, it would give far more accurate readings and stats. A table for programs, a table for teachers, and a table that links the two containing program PK and teacher PK and such details as date, number of students, etc.

[JoeBruce]

A table for programs, a table for teachers, and a table that links the two containing program PK and teacher PK and such details as date, number of students, etc.

I feel like that's what I have, at least almost. I have a tblTeacherInfo, and the PK is "TeacherID" so they all have a unique identity. I have a tblProgramInfo, which just has the name (e.g. guided hike or history) and type of program (e.g. field trip or outreach); the PK is "ProgramID" (so the types of programs have a unique ID). The third is "tblProgramsAttended," which has the stats (name of program, teacher, number of students, etc.)

Now, the PK for "tblProgramsAttended" is called "ProgramID," which assigns a unique ID to each program I enter, and that might be my problem. The data is entered via a form, and tblProgramsAttended is a subform under the main form that shows/enters the teacher info. So each teacher gets a page (record), and the same program could be entered multiple times if multiple teachers participated.

Furthermore, the PK for "tblProgramsAttended" does not have a relationship - the PK from "tblTeacherInfo" is joined to a "TeacherID" field on this table, and the PK from "tblProgramInfo" is joined to a "NameofProgram" field. See below for a screenshot of the relationships.

You gave me a little bit of advice back in September on this thread - the sample database I included there should be relatively the same as the one I'm currently working on, if you wanted to take a look.

Thanks again for the advice. It has been helpful just to talk/type it out. Not sure if I need to scrap this DB and start over, or if there is a way to salvage things...

[aytee111]

If what you say is right then the structure of the tblProgramsAttended isn't right. First, tho, I must make a comment on your naming conventions. Never use the same name twice, anywhere, for 2 different fields. Above you have the same name as the key to two different tables, this is going to confuse everything and everyone every time! Secondly, when a field is linked it must be the same name. I would recommend you make those changes straight away. Oh, this is optional, just recommended!

This table says that this program/this teacher was on this date with this number of students. If I understand you correctly that is not true as teacher A and teacher B will both put in the same number of students, hence destroying any data integrity in this table. If the number of students on this table actually applies to the program and not to the teacher then you must redesign it, remove teacher for starters. You can have a separate table for program/teacher reference. You cannot mix and match! You use an auto-number as your primary key but that is not so - your primary is actually program and teacher. People use autonumbers as primary keys so as to disguise the underlying bad table design! Database normalization rules state that all fields within a table must directly apply to the primary key - hence the problem with the number of students.

[JoeBruce]

Thanks for input - very constructive. This is the first database I've ever designed so I was bound to make mistakes somewhere.

The data for number of students was something I added shortly after I got the database up and running, so my design problems partially stem from making a major change to the design after I had things started. I'm undecided if I want to try to correct this database (likely), scrap it and start fresh, or remove the whole idea of tracking number of students with the database and keep it simply to teachers and the programs they've attended (I could record my stats a different way).

I'm still confused about a lot of things, but it's been a learning process so far Thanks again for the help.

[aytee111]

Definitely use this exercise as an opportunity to learn. Start by deciding what you want to keep track of, knowing that you can build on more later. Then redesign the tables to database normalization specs. You may find that you don't have to make that many changes to the current design and that you can use a lot of it. Building a database is like building a house - the foundation is everything! With a good solid foundation you can add on later - as you have found out, without that you come a cropper. Note also, Access is just a tool, once you understand building databases and table design you can use that information with any tool.