1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by CinBar, Apr 2, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. CinBar

    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!!!!
     

    Attached Files:

  2. OBP

    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.
     
  3. CinBar

    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
     
  4. CinBar

    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!
     
  5. OBP

    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.
     
  6. CinBar

    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.
     
  7. OBP

    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.
     
  8. CinBar

    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!
     
  9. OBP

    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?
     
  10. CinBar

    CinBar Thread Starter

    Joined:
    Jan 15, 2008
    Messages:
    54
    It's also in the Repeat Only DB. Thx.
     
  11. OBP

    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.
     
  12. CinBar

    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!
     
  13. OBP

    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?
     
  14. CinBar

    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));
     

    Attached Files:

  15. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/699507

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice