Find a record with a maximum value in a group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tintin - 14 Jun 2006 21:56 GMT

Hi everyone,
I have a problem which I think you people can solve.
I am having problems. I have a table which stores surveys submitted. We
have multiple types of surveys, hence a person can have multiple
surveys under his/her name. These surveys have priorities from 0 (min.)
to 3 (max.). My problem is, I want to pick the whole record for a
survey with the maximum priority. For example, I might have 3 survey
records under the name "John Smith" with survey priorities 0,1,2. I
wish to pick the whole survey record which has survey priority 2. And I
wish to do this for each person in the table. I hope I was clear in
explaining the problem. Can anyone help me with this.

Thanks

Nitin

Reply to this Message

pietlinden@hotmail.com - 14 Jun 2006 22:11 GMT

Could you post the schemas for your tables so we have a clue where to
start?

Sounds like you need a group by query as a correlated subquery to get
what you want, but without your table structures, it's hard to tell.

Reply to this Message

Tintin - 14 Jun 2006 22:52 GMT

Hi,
Some of the fields for my table are:

First Name:
Last Name:
Survey Type (Priority)
Time submitted
Surveyer Last Name:
Surveyer First Name:
Info parameter 1:
Info parameter 2:

Actually in the survey submission process, there are 2 people involved.
One whose survey is to be taken and another one who asks the survey
question to the other person. Here a teacher and a student. The teacher
will be the surveyer and the student will be the person who will be
asked the survey questions. You will ask why multiple surveys? Say the
student was surveyed in two periods of times in a semester. One maybe
after the midterm by a teacher and another after the final exam by
another teacher. And everytime Info parameters were obtained. I wish to
keep the whole record for the most recent survey for that student.

I hope I was clear in explaining in this.

Thanks for the feedback

Nitin

> Could you post the schemas for your tables so we have a clue where to
> start?
>
> Sounds like you need a group by query as a correlated subquery to get
> what you want, but without your table structures, it's hard to tell.

Reply to this Message

pietlinden@hotmail.com - 15 Jun 2006 06:09 GMT

If I were you, I'd read Keri Hardwick's post on creating surveys in
Access. If you build it right, querying is child's play. If you build
it wrong, it's difficult at best and impossible at worst.

Here's the link:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/bc33ab
3129eae0b/afb0a9d0315245c2?q=%22Keri+Hardwick%22%2Bsurvey&rnum=3#afb0a9d0315245c
2

(careful, the link scrolls to the right...)

Keri's advice has always been spot on, so I'd take it.

Reply to this Message

Tintin - 15 Jun 2006 17:30 GMT

Hi,
Thanks for the link. I'll have a look. Actually I am using
ASP/HTML pages to collect data and then submit the data to the Access
database. So it could be a bit tricky to handle it for me using the
Access functionality you have mentioned. I'll surely see if it helps.

Thanks for the feedback...

Nitin

> If I were you, I'd read Keri Hardwick's post on creating surveys in
> Access. If you build it right, querying is child's play. If you build
[quoted text clipped - 6 lines]
>
> Keri's advice has always been spot on, so I'd take it.

Reply to this Message