help with query

I have a query that returns:
[Name] [Event]
Turner Completion
Turner Interim 1
Kemp Completion
Steel Completion
Steel Interim 1
Steel Completion
Steel Interim 1
Webb Completion
Webb Interim 1
Wilkinson Interim 1


what I want to do is where the student has an event of completion in one
row and interim in the other to return completion OR where the student
has one row (one course only)to return either event

Itried doing a group by with a count on event as I can assume that where
two rows exist one is completion. but not sure how to extract the event
description where ther is only one row.

sql so far:
SELECT Qrygetintrimcomplist.Date, Qrygetintrimcomplist.Centre,
Qrygetintrimcomplist.[Course Code], Qrygetintrimcomplist.[Course Name],
Qrygetintrimcomplist.[Last Name], Qrygetintrimcomplist.Event
FROM Qrygetintrimcomplist;

Regards in advance
Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: help with query

Hi Peter,

If the only values in the field are "Completion" and "Interim 1" (or
"Interim [x]"), how about a simple Totals query:

SELECT Last_Name, MIN(Event)
FROM MyQuery
GROUP BY Last_Name;

------------
LoopyNZ
------------

----------------------------
Original Message:

help with query
From: Peter
Date Posted: 9/24/2004 1:43:00 AM

I have a query that returns:
[Name] [Event]
Turner Completion
Turner Interim 1
Kemp Completion
Steel Completion
Steel Interim 1
Steel Completion
Steel Interim 1
Webb Completion
Webb Interim 1
Wilkinson Interim 1


what I want to do is where the student has an event of completion in one
row and interim in the other to return completion OR where the student
has one row (one course only)to return either event

Itried doing a group by with a count on event as I can assume that where
two rows exist one is completion. but not sure how to extract the event
description where ther is only one row.

sql so far:
SELECT Qrygetintrimcomplist.Date, Qrygetintrimcomplist.Centre,
Qrygetintrimcomplist.[Course Code], Qrygetintrimcomplist.[Course Name],
Qrygetintrimcomplist.[Last Name], Qrygetintrimcomplist.Event
FROM Qrygetintrimcomplist;

Regards in advance
Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: help with query

I tried but that failed. I will explain more clearly

each student does a course and the events are interim (due an interim
payment before course end) and then completed in which case they are
entitled to all the finding.

each student can have 1 or two events

in my pasted rows example one row had interim against student A and a
row completed against student A.

student B with one row as interim
student c with one row as completed.

I need the completed row where 2 events exist (interim and completed)
and the 1 row where the row is interim or complete.




Regards in advance
Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: help with query

Peter <peterandaluz@devdex.com> wrote in
news:41541363$0$26172$c397aba@news.newsgroups.ws:
[color=blue]
> I tried but that failed. I will explain more clearly
>
> each student does a course and the events are interim (due an
> interim payment before course end) and then completed in which
> case they are entitled to all the finding.
>
> each student can have 1 or two events
>
> in my pasted rows example one row had interim against student
> A and a row completed against student A.
>
> student B with one row as interim
> student c with one row as completed.
>
> I need the completed row where 2 events exist (interim and
> completed) and the 1 row where the row is interim or complete.
>
>[/color]
If your events were numeric, with 1 representing interim and 2
indicating complete, and the descriptions in a lookup table, you
could simply build a query to return the maximum event for each
student ID. and use a combobox to show the event description on
your form.

doing it with a count might work by building an expression around
the count of rows to drive an iif() statement as in
expr1: iif(count([EVENT])=2,"Completion",[event])

By thew way, your system will return erroneous results if you
have 2 or more students named "Smith"

--
Bob Quintal

PA is y I've altered my email address.

re: help with query

Regards in advance
Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: help with query

Thanks Bob

In the end I took the event field out (as I only needed the name and the
course) then grouped to get rid of the duplicate row.

what I was left with was the correct number of students that had interin
or completion.

However I prefer your methods and will use the numeric method.

Thank you!


Regards in advance
Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!