Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: How to query database to show the date of registration for participants of courses

    Hello I have a relational access 2013 database which is storing data of particpants who attend courses in various centre. There are three tables - tblCourses, tblparticipants and tblCentres. One participant can attend many courses. One centre can have many courses.

    Im trying to create a Query to display the first date of which every Participant was first registered on to the database. To do this I am using the first the date that they attended a course and am displaying name of the centre at which that course was held.

    For the query I have used two of the tables in the database and have added the following fields -

    (tblParticipants)
    ParticipantNumber
    ParticipantFirstName
    ParticipantSurname

    tblCourses
    courseDate
    courseTitle
    courseID
    centreName

    I have set the total to Min for the courseDate and centreName, and to First for the courseID and courseTitle, and then have created a second query to count the totals but I am getting incorrect query results which display some courses on the wrong dates and / or at the wrong centres.

    What is the correct way to show the first date of registration? I have tried first / min and keep getting incorrect centres and dates when I check the query results against the actual tabes.

    Cheers

    Tom

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there seems to be no link between participants and courses
    as by inference a peson can attned many courses this needs to be an intersection table which identifies whihc person attneded whihc course.

    I'd also expect a course (the title etc) to be a spearate table from specific editions of the course which run on a specific date(s) in a specific location(s). students can then attend recorded in an i9ntersectin table bewteen that course instance and the student
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Hello, sorry I should have mentioned that all the relationships are already created in the database. I have attached screen shots of the database relationships and also the query itself. Its just the query which is showing certain incorrect results. I think I have not got the First / Min criteria correct in the fields on the query.

    Click image for larger version. 

Name:	Picture2.jpg 
Views:	5 
Size:	28.1 KB 
ID:	15955

    Click image for larger version. 

Name:	Picture1.jpg 
Views:	5 
Size:	70.0 KB 
ID:	15954

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm not too certain what you are trying to achieve
    but
    Im trying to create a Query to display the first date of which every Participant was first registered on to the database
    could be, either record that fact as part of the participant table (ie when that entry is recorded)
    or
    this should get you close to where you want. your inclusion of the course name is irrelevant to finding the first registration of a particiapnt attending a course (its probabloy relavant to the first registration of each and every course but thats not what you say you want.
    Code:
    SELECT min(t3.coursedate) as EarliestRegistration, t1.participantFirstName, t1.participantSurname from tblparticipants as T1
    left join tbldailyregister as T2 on t1.ParticipantNumber = T2.[Ref No]
    left join tblCourses as T3 on T2.courseref = T3.courseref
    order by t1.participantSurname, t1.participantFirstName
    as to whether that works I dunno. Ive got no test data to test it so you will have to work out any errors as you go along. I do remember that Access can be falky as its join syntax is (IIRC) at variance with the SQL standard. I used the table Alias of T1,t2 & T3 as I couldn't be (*&^*& to type your table names more than once

    frankly to me your tables are a mess. either use CamelCase or all_lower_case_separated_like_this
    don't use spaces, it can cuase problems over time, it means you MUST enclose column/table names with spaces with [ ], eg [Ref No]

    There's stylistic issues, for me seeing the table name repeated in the column names in the same table is yucky. use abbreviations where appropriate (I forget what the limit in Access is but there is a finite limit of how long a column/table name can be, offhand I thinkit may be as low as 32)
    One neat stylistic trick is where a column is a foreign key to another table uses the name of the primary key's table as part of the column name.. it makes the design easier to read
    eg in tblparticipanjts
    ID (instead of the long winded particiantnumber
    in tblDailyRegister
    [Ref No] becomes ParticiapantID (or use an abbreviation such as PtID

    why do you think you need an ID column in tbldailyregister? isnt the composite primary key of the participant number and the course reference sufficient?. don't it your way means that there is a very clear risk that you may duplicate data ie the same pairing of participant and course

    there's nearly as many naming conventions for SQL tables and columns as there are developers using SQL, but whatever convention you elect to use, stick to it
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    All I'm trying to do is display in the query the first date of which someone attended a course.

    So if for example Billy Jones attended on 20 different dates at 15 different centres I need the query to show the first date he attended his first course on along with that course name and the centre at which that course was held. And I have 400 participants who have attended 180 courses. So I need the first date, that course name and that centre for everybody. That's all.

    I haven't created the database or the naming conventions, but the naming won't affect the data or the way it is displayed and they can be changed later.

    Last edited by moss2076; 11-05-14 at 16:28.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So add the centrename column to the query.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    CentreName is already there?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by moss2076 View Post
    CentreName is already there?
    not in this it aint
    Code:
    SELECT min(t3.coursedate) as EarliestRegistration,
      t1.participantFirstName,
      t1.participantSurname
    from tblparticipants as T1
    left join tbldailyregister as T2
      on t1.ParticipantNumber = T2.[Ref No]
    left join tblCourses as T3
      on T2.courseref = T3.courseref
    order by
      t1.participantSurname,
      t1.participantFirstName
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •