tricky query

[Corticus]

Hi all,

I've got this tricky query I'm trying to do. I have actually accomplished the retrieval of the data through means of reorganizing the data into new tables, and querying these, but I would like to know if a query could accomplish what I want right from the original table.
Anyways, here's the field names:
LastName/FirstName/Period/Professor/Course/Exempt(Y/N)
and data would look like this;
Doe/John/1/Mr. Smith/English/Y
Doe/John/2/Mr. James/Math/Y
Adams/Julie/1/Mrs. Jones/Chorus/N

I need to retrieve the students name for students who are exempt from either periods 1 and 2, or periods 3 and 4, or periods 5 and 6. In the case of the above data, John Doe would retreived.
What's tricky is neither the student, nor the course have a primary ID. The data was inherited in this format and consists of 8,063 records.
I used append queries to reorganize the data so there is a teacher table, each with a pka, and a course table, each with a pka, and then used an update querry to attach these pkas to the original table described above. I don't have the SQL I used handy but I'll post if it anybody cares.
Anyways, I would like to know how to query this table directly just for academia sake, as after a week of beating my head against a wall trying to figure it out, I had nothing.

edited to change things

Thanks,
Corticus
This message was edited by Corticus on 2002-12-08 12:45

 

[Anne Troy]

Cort:

Make 3 separate queries, one for each of the 3 groups. Then run a query that combines the 3? That's what it looks like to me.

 

[Bariloche]

Corticus,

Here's my (crude?) solution:

I created a Crosstab query with Last Name and First Name as the rows, Period as the column and Exempt as the data (using the "First" predicate(?)). The Exempt data also has a criterion of equal to "Y" (this needs to be a separate, not displayed field).

Then I ran the following query against that crosstab query (called "Query1"):
<pre>
SELECT "E1,2" AS Code, Query1.LastName, Query1.FirstName
FROM Query1
WHERE (((Query1.[1])="Y") AND ((Query1.[2])="Y"))
UNION
SELECT "E3,4" AS Code, Query1.LastName, Query1.FirstName
FROM Query1
WHERE (((Query1.[3])="Y") AND ((Query1.[4])="Y"))
UNION
SELECT "E5,6" AS Code, Query1.LastName, Query1.FirstName
FROM Query1
WHERE (((Query1.[5])="Y") AND ((Query1.[6])="Y"))
ORDER BY Code ASC;
</pre>

It gave me the result I was expecting (I added additional data to your dataset), but I don't know if its necessarily correct. Your large dataset would be the proof.

enjoy

 

[Anne Troy]

Sure, Bariloche. Come in here making me look bad by posting all that fancy Union stuff!!

LOL!

 

[Bariloche]

Actually, though, Dreamboat, it isn't much different than what you proposed with the "... query that combines the 3 ...". I was going to try a self-join, but this didn't make my head hurt as much.

It will be interesting to see if it works out.

have fun

 

[Corticus]

Thanks for the replies everybody!

I'll let you know how they work out(hopefully this morning), looks good, though!

take it easy,
Corticus

 

[Corticus]

Yes!

Worked perfect. I never really used crosstab queries and this was a perfect application. I couldn't get the SQL to work as provided, I had to adjust to:
SELECT ExemptStudents.LastName, ExemptStudents.FirstName
FROM ExemptStudents
WHERE (((ExemptStudents.[01])=1) AND ((ExemptStudents.[02])=1))
UNION
SELECT ExemptStudents.LastName, ExemptStudents.FirstName
FROM ExemptStudents
WHERE (((ExemptStudents.[03])=1) AND ((ExemptStudents.[04])=1))
UNION
SELECT ExemptStudents.LastName, ExemptStudents.FirstName
FROM ExemptStudents
WHERE (((ExemptStudents.[05])=1) AND ((ExemptStudents.[06])=1));

I couldn't get the 'code' part to work, but I don't know what that was supposed to do so its probably my fault.

Thanks, though, awesome solution!
Corticus
This message was edited by Corticus on 2002-12-09 11:30

 

[Bariloche]

Corticus,

The "Code" part was to identify which Group of periods the student was exempted from: 1 and 2, 3 and 4 or 5 and 6. If you don't need to know that (or don't want to know that), then it doesn't matter.

have fun

 

[Corticus]

Thanks,

The user informed me that they just wanted 1 query for each group of exempt students(one for periods 2 and 3, one for 4 and 5, and one for 6 and 7), so the union wasn't necessary, after all. Since there is one query per group, the periods from which the student is exempt can be determined by the query from which the students name originated.

But, I am interested in learning how to use that part of the SQL correctly, so if you wouldn't mind explaining what I was doing wrong, I would appreciate it.

Thanks for your help, your solution was really great, and it allows for a lot of flexibility down the road.

cya,
Corticus

 

[Bariloche]

Corticus,

When I create a Union query I first create a Select query and then modify it as necessary to get the other info. For this one I created a query based on the crosstab query and added a "field" using an alias, which was "Code: "E1,2"" so for each record that meets the criteria of Period 1 and Period 2 being "Y" there would be the value "E1,2" added to it in another column. So, your first select statement would then look like this:

SELECT "E1,2" AS Code,
ExemptStudents.LastName, ExemptStudents.FirstName
FROM ExemptStudents
WHERE (((ExemptStudents.[01])=1) AND ((ExemptStudents.[02])=1))

Modify the other parts of the Union query and re-run it and you'll see what I mean.

enjoy