mindbender

[Pharcyde]

Hi, I have 2 tables: Student, and TimeTable. There are 5 groups of students numbered 1-5, and a group LG, containing all students.

Student only contains groups 1-5, whereas TimeTable also contains LG.

Currently, I have no problem in displaying the numbered groups, but there is no sign of LG. I need the query to display all students in groups 1-5 when LG is entered - but I just can't get my head around it....

Please help!!!!

Lee

 

[namliam]

How are you currently running the query?

Regards

 

[Pharcyde]

SELECT DISTINCTROW StudentData.StudID, [Surname] & ", " & Firstword([Forenames]) AS FName, TimeTable.Group FROM StudentData INNER JOIN TimeTable ON StudentData.Group = TimeTable.Group WHERE (((TimeTable.TimeTableID)=[forms]![StudentAbsence]![TTID]) AND ((StudentData.Status) In ('R','T'))) ORDER BY [Surname] & ", " & Firstword([Forenames]);

See it doesnt specifically ask for the groups (1-6), but discounts LG because it doesnt appear in the Student(Data) table...

Oh, and it is run as a rowsource - but dont think i should change this because it hevaily affects the other tables. Thanks!

 

[Fuga]

Hi, I have 2 tables: Student, and TimeTable. There are 5 groups of students numbered 1-5, and a group LG, containing all students.

Student only contains groups 1-5, whereas TimeTable also contains LG.

Are you saying all students are duplicated?

Can´t you just have groups 1-6 and then just run the query with no criteria when you wish to display all records?

With an inner join, I think you only select those records that are equal, meaning if "lg" is in only one table, those records will be omitted.

Fuga.

 

[Pharcyde]

i think i know what you mean, but without the criteria the query wont work - because it actually needs the groups to run and display the data... :S

 

[namliam]

Let me get this right...

TimeTable:
TimeTableID Group
1           MyGroup
2           YourGroup
3           HisGroup
4           HerGroup
5           Groupies
6           LG

where LG = all groups, and your using this query:

SELECT DISTINCTROW
    StudentData.StudID,
    [Surname] & ", " & Firstword([Forenames]) AS FName,
    TimeTable.Group
FROM
    StudentData INNER JOIN
    TimeTable ON StudentData.Group = TimeTable.Group
WHERE
        TimeTable.TimeTableID = [Forms]![StudentAbsence]![TTID]
    AND StudentData.Status In ('R','T')
ORDER BY
    [Surname] & ", " & Firstword([Forenames]);

Assuming that TTID is a combobox displaying both Group and TimeTableID (probably hidden) you can change it to this:

SELECT DISTINCTROW
    StudentData.StudID,
    [Surname] & ", " & Firstword([Forenames]) AS FName,
    TimeTable.Group
FROM
    StudentData INNER JOIN
    TimeTable ON StudentData.Group = TimeTable.Group
WHERE
        TimeTable.Group like iif([Forms]![StudentAbsence]![TTID].column(1) = 'LG';"*";[Forms]![StudentAbsence]![TTID].column(1) )
    AND StudentData.Status In ('R','T')
ORDER BY
    [Surname] & ", " & Firstword([Forenames]);

It not the most beautifull way and I have not tested it but it should work.

Regards

 

[Pharcyde]

that all makes sense, and I can see how it would work - but TTID is a simple number, guess it just dictates the entry in the table. That seems to stop the query you gave me from running...

 

[namliam]

You can still do the same.... except with the ID:

SELECT DISTINCTROW
    StudentData.StudID,
    [Surname] & ", " & Firstword([Forenames]) AS FName,
    TimeTable.Group
FROM
    StudentData INNER JOIN
    TimeTable ON StudentData.Group = TimeTable.Group
WHERE
        TimeTable.TimeTableID like iif([Forms]![StudentAbsence]![TTID] = 6;"*";[Forms]![StudentAbsence]![TTID])
    AND StudentData.Status In ('R','T')
ORDER BY
    [Surname] & ", " & Firstword([Forenames]);

But let me get this, you are asking users to input a PK? Not a userfriendly sollution i think...

Also in this setup you need 6 to be ALL might be smarter to make 0 all to allow for adding groups later on...

Regards

 

[Pharcyde]

sorry I'm probably confusing you now! Not one of my cleverest days I'm afraid! TTID can be any number, random integers, which mostly are 235, 300 - around that mark.

With reguard to the groups:
StudentData.Group
1
2
3
4
5


TimeTable.Group
1
2
3
4
5
LG - as in LG doesnt have a group no to itself, its just LG.

LG does equal all groups - but I havent defined that, thats mainly the problem

The combo box where the query is run from takes a (user given) group number (along with date, time, ID etc) and returns the names of the pupils in that group