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

 

[Corticus]

Ah, now I see.

Its sort of like a 'tag', so I know which query returned the data. That's a good idea.

Thanks for all your help,
Corticus

 

[dmckinney]

...different approach....for economy of sql

SELECT txtTest.Nem, Choose(Int(([period]+1)/2),"12","34","56") AS checkone, Switch(Sum(txtTest.Period)=3,"12",Sum(txtTest.Period)=7,"34",Sum(txtTest.Period)=11,"56") AS checktwo, txtTest.exempt
FROM txtTest
GROUP BY txtTest.Nem, Choose(Int(([period]+1)/2),"12","34","56"), txtTest.exempt;


this is still a work in progress - and will probably stay that way if you don't like it.

It's probably the only time I've managed to use the switch function and the choose function in the same query! Boy am I proud.

It groups by name, block (e.g. 1 and 2), sum for the block, and where checkone = checktwo you've got a result.

Try it on your data and let me know what it gives,

Dave.

 

[Corticus]

On 2002-12-12 10:21, dmckinney wrote:
...different approach....for economy of sql

SELECT txtTest.Nem, Choose(Int(([period]+1)/2),"12","34","56") AS checkone, Switch(Sum(txtTest.Period)=3,"12",Sum(txtTest.Period)=7,"34",Sum(txtTest.Period)=11,"56") AS checktwo, txtTest.exempt
FROM txtTest
GROUP BY txtTest.Nem, Choose(Int(([period]+1)/2),"12","34","56"), txtTest.exempt;

I tried it...but had a couple issues(this is sort of a learning project for me, so any alternate approaches are much appreciated),

I can't figure out what:
txtTest
txtTest.Nem
txtTest.exempt
txtTest.period
are supposed to be.

Are they the fields from the source table?
I can't figure out how to adjust these references for my db, maybe you can help?
Source table:
TblStSch
Fields:
LastName, FirstName, Exempt, Period, Grade, Exempt,Instructor

Thanks!
Corticus
This message was edited by Corticus on 2002-12-12 10:47

 

[dmckinney]

apologies...I made my own little table ...and not very well.

I've sinced tried the crosstab option and must say it's much neater albeit a little longer than my own.

try the following sql...(unchecked as I haven't got your table.)

SELECT TblStSch.FirstName & TblStSch.LastName as fullname,
Choose(Int(([period]+1)/2),"12","34","56") AS checkone,
Switch(Sum(TblStSch.Period)=3,"12",Sum(TblStSch.Period)=7,"34",Sum(TblStSch.Period)=11,"56") AS checktwo,
TblStSch.exempt
FROM TblStSch
GROUP BY TblStSch.FirstName & TblStSch.LastName,
Choose(Int(([period]+1)/2),"12","34","56"), TblStSch.exempt;

 

[Corticus]

Okay,

I fiddled with a bit:
SELECT [TblStSch].[FirstName] & ", " & [TblStSch].[LastName] AS fullname, Choose(Int(([period]+1)/2),"23","45","67") AS checkone, Switch(Sum(TblStSch.Period)=3,"23",Sum(TblStSch.Period)=7,"45",Sum(TblStSch.Period)=11,"67") AS checktwo, TblStSch.ExamExempt AS Expr1
FROM TblStSch
GROUP BY [TblStSch].[FirstName] & ", " & [TblStSch].[LastName], Choose(Int(([period]+1)/2),"23","45","67"), TblStSch.ExamExempt
HAVING (((TblStSch.ExamExempt)=-1));

I just changed exempt to examexempt(thats my field) and I tried to change the periods of interest to 2 and 3, or 4 and 5, or 6 and 7, but the SQL, though correct in syntax, is not retrieving the right records. You don't really have to walk me through this if you don't want, since I've already got the thing working, but I am curious just for the sake of knowledge. I would try to adjust the SQL myself like I did with the crosstab query, but this one is way over my head. I guess that's why I want to get it to work!

Thanks for your time,
Corticus

 

[dmckinney]

sorry for the delay....time zone differences.

I didn't put criteria on the query to filter out irrelevant results.

If I'm right where checkone is equal to checktwo these rows should be your result.

verify that...and if it gives the right result let me know I'll explain putting on the final criteria, and finally whats going on in the query.

(but I'll repeat that while it might be fancy code...it's not the neatest [most satisfactory] solution.)

 

[Russell Hauf]

Ok, I'll chime in (extra time today)...

Open a new query and add your table. Add the fields you want. Right-click on the table that is in your query - on the actual "box" that says the name of your table and has the fields listed. Select "Properties", and under "Alias", type "X".

In a blank field to the right of the fields you have already added, put:

[Period] Mod 2

in the "Field:" line, and put 1 in the "Criteria:" line. Uncheck the "Show:" checkbox.
(If your Period field is a text field, put Val([Period]) Mod 2).

In the next field to the right, put this:

NextPeriodExempt: (SELECT Period FROM tbl121302Corticus WHERE LastName=X.LastName and FirstName=X.FirstName and Period=X.Period+1 and Exempt=X.Exempt and X.Exempt=True)

in the "Criteria:" line, substituting your table name for mine (tbl121302Corticus). In the criteria line, put:

Is Not Null

and uncheck the "Show:" check box for this also.

That should do it!

Hope it helps,

Russell

EDIT: And if you wanted to do one query for each of the 3 "Exempt" categories, you could put a 1,3, or 5 under the criteria for Period. But if you don't, you can still see which "Exempt category" (by this I mean: 1&2, 3&4, or 5&6) they came from, as Period in your query will be 1, 3, or 5.
This message was edited by Russell Hauf on 2002-12-13 15:46

 

[Corticus]

Thanks guys,

I didn't want you to think I was ignoring you, I should be testing this out today, but if not, I will test it out soon, hopefuly by tommorrow. I'm kind of swamped right now, but I'm getting a NEW COMPUTER tonight, so I'll have more time at home to work on this stuff. Thanks for all your help, this is a pretty interesting thread!

Corticus
This message was edited by Corticus on 2002-12-16 14:32