Combine Records - Please help me

[Chun]

Hello Everybody

I am trying to set up a query for a table which has following 3 fields
GroupNo
Name
Points

which has values in the following manner

GroupNo-Name-Points
204-------Jack---- 20
204-------Ryan---40
204-------Tita-----30

202-------Jack----35
202-------Ryan----24

205-------Jack-----56
205-------Ryan----73
205-------Tita------45

Is it possible to combine the above records by the GroupNo in the following manner with 7 different fields now?

GroupNo---Name1---Points1---Name2---Points2---Name3---Points3
204---------Jack------20---------Ryan-----40----Tita------30
202---------Jack------35---------Ryan-----24
205---------Jack------56---------Ryan-----73----Tita------45

Any help in this regard will he highly appreciated.

Thanks

 

[The_Doc_Man]

I might consider a CROSSTAB query to get something close to this. Read up on it in the Help files and then try it. If that is not what you want, come back to this thread with more details of what you need.

 

[Chun]

Thank you The_Doc_Man for your suggestion.
Crosstab query does not help me with my problem.

However I tried this query, but the only problem with this is it repeats the records. (I know for sure I am missing only one little thing somewhere, but just cannot seem to trace it out) Pls help me.

SELECT tblGrade.GroupNo, tblGrade.Name AS Name1, tblGrade.Points AS Points1, tblGrade_1.Name AS Name2, tblGrade_1.Points AS Points2, tblGrade_2.Name AS Name3, tblGrade_2.Points AS Points3
FROM (tblGrade INNER JOIN tblGrade AS tblGrade_1 ON tblGrade.GroupNo = tblGrade_1.GroupNo) INNER JOIN tblGrade AS tblGrade_2 ON tblGrade_1.GroupNo = tblGrade_2.GroupNo;

I attaching the db here with the table and query, if somebody can pls have a look at it and help me out please. I would really appreciate it.
Thanks

 

[boblarson]

Why do you need this done in a query? You can do it in a report, but I think you are out of luck if you want it done in a query (the cross tab will get you close, but not exactly how you showed).

 

[Chun]

Thanks Bob Larson for your help. Appreciate it.
The actual database I have is for a hospital system.
I have to do some program checks, validation check for the database which has approx 2000 records and about 30 fields.
For each groupno, i have to do some validation checks for the different Names(Graders).
For example, for groupno 202, what is the value for Jack, value for Ryan and why there is no value for Tita....that kind of checks i need to do.
Actually I had another old db of the same kind, where the Names where entered in 3 different fields - like Name1, Name2, Name3...so I never had any such problem working with that old db.
Any inputs please?
Thanks again

 

[Cosmos75]

Seems like the crosstab query gets you what you need, even though it isn't what you are used to seeing. How are you performing your validation, manually or via some sort of automation?

Do you only have these three names that never vary? If so, I guess you could do something like this (even though it is a clunky way of doing it)

SELECT DISTINCT tblData.GroupNo, 
       "Jack" AS Name1, (SELECT Sum(Point) FROM tblData as a WHERE a.FirstName="Jack" And a.GroupNo=tblData.GroupNo) AS Points1, 
       "Ryan" AS Name2, (SELECT Sum(Point) FROM tblData as a WHERE a.FirstName="Ryan" And a.GroupNo=tblData.GroupNo) AS Points2, 
       "Tita" AS Name3, (SELECT Sum(Point) FROM tblData as a WHERE a.FirstName="Tita" And a.GroupNo=tblData.GroupNo) AS Points3
FROM tblData;