Help to created query!!! (1 Viewer)

[nc]

Hi all,
I have a very very tough problem to be solved. I have a table which is the answer of a survey. It includes the field of userid and ans(contain the council the user belongs to and the quarter selected). The records are not unique because each user can be in more than 1 council and answer for more than 1 quarter for a particular council. Eg.
|userid |ans |ans_id|
-------------------------
|mychan |EHS |12345 |
|mychan |Q1 |12346 |
|ter |PS |15557 |
|ter |Q2 |15558 |
|mychan |PS |64598 |
|mychan |Q1 |64599 |
|ter |PS |46512 |
|ter |Q1 |46513 |
-------------------------
what i need to do is sort out a user with the council he/she belongs to and the corresponding quarter. Eg.
|userid |Council|Quarter|
---------------------------
|mychan |EHS |Q1 |
|mychan |PS |Q1 |
|ter |PS |Q1 |
|ter |PS |Q2 |
How to design the query??? i have tried all kinds of combination but still i couldn't get what i want. pls help!!! thanks...

 

[KKilfoil]

This a strange structure! Am I correct to state that a complete 'concept' is separated into two records in the same table?

Will the quarter records ALWAYS be the next record after the council record (i.e. id always one greater?)

If so, create a query that selects all of the records with an [ans] value starting with Q (Like "Q*"). Add a calculated field like: CouncilID: [ans_id] - 1

Create a second query that uses the above query and your table as datasources. Relate the calculated field above to [ans_id] in the table. Now show the three fields to want to show, renaming as required.


[This message has been edited by KKilfoil (edited 05-23-2002).]

 

[nc]

oh, ya. thanks KKilfoil.
i've got that done!!