Newbie needs report help

[GS1]

Hi

I am currently involved in my first access project at work, and have come across a report problem that is beyond my (rather limited) skills.

I need to produce a report that shows a list of activities, along with a list of the people involved in those activities.

The activities are listed in the table 'activities', with the users involved in a 'users' table. In order for many users to be involved in a single given activity, I created a junction table 'users involved'.

To get my list of activities that shows all of the users involved, I used a query that uses a left join, thus showing all of the users for a given activity.

My report, however, shows the activity once for each user

for example

Activity X 1/1/02 Jo Bloggs
Activity X 1/1/02 Jon Smith

What i need is the report to show

Activity X 1/1/02 Jo Bloggs, Jon smith


As that would be much more readable....

Sorry for the huge first post, but I need to get the whole show on the road by Friday (gulp!) and this is my final hurdle.

Any help would be greatly appreciated!

Thanks

Graham

 

[Shane_Mc]

I think the best you will be able to do is group the report by Activity. So you will have

Report Title
Activity 1 Name
_____________________________________
User 1
User 2
User 3

Activity 2 Name
_____________________________________
User 1
User 2

etc.

 

[GS1]

Thanks for your quick reply - the problem is that some activities involve 23 or 30 users, resulting in a report that spans too many pages.

Any other suggestions?

Thanks


Graham

 

[Shane_Mc]

To be honest I don't think you can have a report like you want. That would involve having a variable amount of fields in it. Reports don't work like that.......they are like a DB Table.......you can't (technically you shouldn't) have records of different lengths.

If anyone else knows how I'd love to find out myself.

 

[Rich]

You have a one to many relationship between activities and users.
Use a subreport for users and group on activities

 

[GS1]

Thanks for your response. I am sure that there will be a way around this though, or at least I hope so, as otherwise I'm not going to be able to get a usable report out of the system.

I might look at crystal reports - not sure if they would have an option to format the data in a more aesthetically pleasing manner!

 

[GS1]

Whoah - replies thick and fast now

Thanks Rich - I shall look at tackling the problem this way. I've never made a report like that though, so the results could be, erm, interesting!