How to stop repeat of results in report, despite using Select Distinct...

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

CinBar

Thread Starter
Joined
Jan 15, 2008
Messages
54
Hi all,

The duplication of results in the Cost Report only happens when in the Course_Details table, in the Course_ID field, there is two StartDate.

Meaning to say, there wouldn't be any problem when generating the cost report when there is only 1 BCF2 in the Course_Details table, with the start date of 24/03/2008. It has 7 related records in Stud_Attend_Courses table. So the cost report will correctly show the 7 records as results.

However, once I added in another BCF2 with the start date of 24/09/2007 in the Course_Details table, the cost report will show the 14 records. There is a duplication.

Attached is my DB. Hope there is a method to solve this problem. Thanks a lot!!!!
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
CinBar, I do not get any results at all for the Cost Report when I select any period this year and BCF1.
 

CinBar

Thread Starter
Joined
Jan 15, 2008
Messages
54
U need to select BCF2...

There are 7 records for BCF2 in Stud_Attend_Courses table. Thx
 

CinBar

Thread Starter
Joined
Jan 15, 2008
Messages
54
Basically, the deisgn of my DB is very simple.

1) A user must first register a course by clicking on the Course button on the switchboard.
E.g: Word, power point, Access... . Data are saved into Course table.

2) For a Word class, there can be few start dates. Meaning different starting dates for teaching the same course. User will enter in the different start dates by clicking on the Course Details button on the switchboard. Data are saved into Course_Details table.

3) A student will have to be registered by clicking on the Student Profile button on the switchboard. All data are saved in the Student_Profile table.

4) After a student has registered, they can register for courses, by clicking on the Student Course Registration button on the switchboard.

5) User can check how many students attend the course (E.g.: Word) by clicking on the Cost Report button on the switchboard.


Thanks n hope this problem will be soon solved. Thx. Pls help!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
CinBar, the problem arises with your Course_Details table, it has 3 records for BCF2, 2 with a subsidy of 0.5% and one with a subsidy of 0%, this means that for every Course taken for BCF2 you have 2 possible answers.
So to make your Query/report work you need to Unjoin the Course to Course_Details and instead Join Stud_Attend_Courses to Course_Details via the Start Date to identify which Actual BCF2 course is being taken by the Student. Also set the Join property to All records from Stud_Attend_Courses and only those matching from Course_Details.
 

CinBar

Thread Starter
Joined
Jan 15, 2008
Messages
54
Thanks for your reply, OBP.

OK, I deleted the link between Course & Course_Details table.
Then I join Link Course_Details & Stud_Attend_COurses via StartDate.
I ticked Enforce referential integrity & Cascade Update Related Fields.
I set the join property as "Include ALL records from 'Stud_Attend_courses' and only those records from 'Course_Details' where the joined fields are equal. "

When I clicked on the Create button, it returns "No unique index for the referenced field of the primary table."

Do I have to set a primary key? Actually, for a particular date, I may have more than 1 class. E.g.: On 1/4/2008, I'll have Word, Power Point classes, etc. However, a student can only attend a class per day.

How about the Cost_Report_Query? Is there a problem there?


Thanks for replying.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
CinBar, I am sorry, you misunderstood my instructions, it is not the Main Table Relationships that you needed to change, they have to be as you had them to work correctly for the tables.
It is the Joins in the actual Cost_Report_Query that supplies the Report with it's data that have to be changed.
 

CinBar

Thread Starter
Joined
Jan 15, 2008
Messages
54
Thanks OBP!

I still have another problem.

Whenever I'm closing the Enter_Course_Details form (U can open this form by clicking on the Course Details button on the switchboard), it will show the message "You cannot add or change a record because a related record is required in table 'Course' ". This message only appears after I change the Course_ID field in the Course_Details table to required=Yes.

The reason for changing required from "No" to "Yes" was because in the Course_Details table, the first record in the Course_ID field keeps changing it's data without any reason.

Hence, it affects the combo box in the forms when I click on the Course details, course & cost report button on the switchboard. So I changed this field's required to "Yes"

After I changed the Course_ID field to required="Yes". It indeed stop changing my data, but instead it pop out the message "You cannot add or change a record because a related record is required in table 'Course' ".

What can I do to stop this message? Thanks!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Are you opening the Form to view old data or to make a new entry?
This sounds like a "New Entry" type error, where you are creating a new record in Details but no record in the Course table.
Can you post the database again?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The original "Blank" Record in your table and this current problem are both related to your "Resetting" of the Course Combo box to "", when you do that you are in effect trying to create a new record and Access doesn't like that.
If you do not use Unbound fields and work directly in the table you will not have this problem. You also would not need the "Save" button.
 

CinBar

Thread Starter
Joined
Jan 15, 2008
Messages
54
Thanks for answering, OBP.

Ok, u see I can't let my user enter directly into the table, and thus, they have to input their data throught the forms.

If I have to use the form, and I have to clear the combo box, how can I do it?

I can't use Me.Combo.Value = Null cos I have set the Course_ID field in the Course_Details table to required=Yes. And now, I also can't use Me.Combo.Value = "", so what should I do?

Thanks a million!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I do not understand why you say "I can't let my user enter directly into the table".
What is the difference, the data ends up in the table whether it is directly or after they have clicked the Save Button?
Also why is it necessary to clear the Combo, aren't they going to use it to make another selection?
 

CinBar

Thread Starter
Joined
Jan 15, 2008
Messages
54
Dear all,

Below is the SQL for the Cost_Report_Query. The problem arises when in the Cost Report form (Open by clicking on the Cost Report button on switchboard), I entered Course Code: BCF2, start date: 01/01/2007, end date: 01/01/2009. By right, it should return me 20 results. (24/09/2007: 11; 24/03/2008: 7; 12/03/2008: 2)

However, it returned me 40 records.

This problem will not occur if it didn't cross years.
E.g.1) Course Code: BCF2, start date: 01/01/2007, end date: 01/01/2008 (Result: 11)
E.g.2) Course Code: BCF2, start date: 01/01/2008, end date: 01/01/2009 (Result: 9)
E.g.3) Course Code: BCF2, start date: 24/03/2008, end date: 24/03/2008 (Result: 7)


Any idea what is the problem??? Thanks a lot!


SELECT DISTINCT Stud_Attend_Courses.InvoiceNo, Student_Profile.[SSD_Subsidy(%)], Student_Profile.[IAC_Sudsidy(%)], Course_Details.[General_Subsidy(%)], Student_Profile.Name, Student_Profile.NRIC, Stud_Attend_Courses.Course_ID, Stud_Attend_Courses.StartDate, Course.CourseFee AS [Orginal Course Fee], ([CourseFee]*0.8) AS [80% IDA Grant], ([General_Subsidy(%)]*([CourseFee]*0.2)) AS [General Subsidy], IIf(((([CourseFee]*0.2)-([General_Subsidy(%)]*([CourseFee]*0.2)))-((([CourseFee]*0.2)-([General_Subsidy(%)]*([CourseFee]*0.2)))*(([IAC_Sudsidy(%)])+([SSD_Subsidy(%)]))))<0,(([CourseFee]*0.2)-([General_Subsidy(%)]*([CourseFee]*0.2))),((([CourseFee]*0.2)-([General_Subsidy(%)]*([CourseFee]*0.2)))*(([IAC_Sudsidy(%)]+[SSD_Subsidy(%)])))) AS [SSD & IAC Subsidy], [Orginal Course Fee]-[80% IDA Grant]-[General Subsidy]-[SSD & IAC Subsidy] AS [Net Fee Payable], [Net Fee Payable]*0.07 AS GST, [Net Fee Payable]+[GST] AS [Fee Payable], Course.CourseName
FROM Student_Profile INNER JOIN ((Course INNER JOIN Course_Details ON Course.Course_ID=Course_Details.Course_ID AND ((Course_Details.StartDate) Between Forms!Cost_Report_Form!AttendCourseStartDate_From_Combo And Forms!Cost_Report_Form!AttendCourseStartDate_To_Combo)) INNER JOIN Stud_Attend_Courses ON Course.Course_ID=Stud_Attend_Courses.Course_ID) ON Student_Profile.IAC_Stud_No=Stud_Attend_Courses.IAC_Stud_No
WHERE (((Stud_Attend_Courses.Course_ID)=Forms!Cost_Report_Form!CourseID_Combo) And ((Stud_Attend_Courses.StartDate) Between Forms!Cost_Report_Form!AttendCourseStartDate_From_Combo And Forms!Cost_Report_Form!AttendCourseStartDate_To_Combo));
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top