Combining several records into one

[cjany]

My table consists of part_id, case_number, cal_dt, counselor,ending_balance, name, prty_sts. One participant (part_id) may have several records with different case numbers. I want a report that gives all cause numbers for each participant. The report should also include the counselor, ending_balance, cal_dt, name and prty_sts, but this information is the same for each record for each participant, so it should only be included once on the report for each participant. I'm looking for something like this: For each participant, list all case numbers and the cal_dt, counselor, ending_balance, name, prty_sts. Group by part_id.

 

[lespaul]

you can't do that in a query, you can however take a query that has all the information:

part_id, case_number, cal_dt, counselor,ending_balance, name, prty_sts

1 1 1/1/05 Joe 15.60
1 2 2/15/05 Sam 16.86

and group by the Part_ID in the report and format the way you want there.

As far as I know, you can't do it with just SQL in a query.

leslie

 

[cjany]

Yeah, I tried that, but it lists the other infomation for each record. I only want to list it once. This is what the report should look like.

part_id counselor

case_number
case_number
case_number

cal_dt

ending_balance

 

[lespaul]

which cal_dt do you want? won't each case number have a different date?

The rest is just formatting and grouping on the report. You may want to post in the Access Report Forum703 if you are still having problems.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[cjany]

No, cal_dt is when the person should appear. They appear on the cal_dt. for all cases.