grouping question

[capri]

I have a database with a number of activities. The activities are either the responsibility of the Service Provider (SP), the Service Manager(SM) or a Department. Obviously the largest number of activities are for the SP. I numbered my activites in such a way that if #2000.0 states the the SP must produce proof of insurance, then #2000.1 states the SM must ensure the SP produced the proof. If there was a corresponding activity for a Deparment then it would have been numbered 2000.2

I have queries that can separate out just the SP (or SM or Dept) activities.

Can anyone suggest a way I might be able to develop a report that could somehow show the corresponding activities grouped together?

2000.0
2000.1
2000.2
-------------
2001.0
2001.1
-------------
2002.0
2002.2
--------------

Thanks for any suggestions or ideas.

 

[capri]

I'm not sure I understand you correctly Pat.

Are you suggesting I separate the Activity number field into 2 ( or perhaps 3) fields?

I have one table that includes 4 fields, ActivityNumber, SP (yes/no) SM (yes/no) and Dept. (yes/no).

I run a query on that table to separate out the activities. I only use the decimal places when a particular activity is performed in some fashion by more than one group. If there was an activity that was only done by the SM, then it would not have a decimal place. I could tell just looking at the number (eg 3305.0) that it was only done by the SM.

I think I need all the activities in one field because at some point I might want to print a report showing the date the activity was completed. In that case I would not be differentiating which group performed the activity, only that the activity was completed. I also have a number of other tables that relate to the activities (such as security/marketing, IT/delivery, channel, financial etc). Having 2 or 3 tables containing activity numbers increases the chance of errors, especially as new activities are added where there might be a corresponding activity by another group.

 

[thhakala]

Since you have the activities within one integer (2000.x), why don't you just use the inbuilt grouping in Access? I don't have an English version, but I suppose it would be in the menu View/*Grouping*. There just select (the names are probly incorrect)
Field: Activity
Direction: Rising
Beginning: Sequence
Sequence: 1
That should do the trict. In addition you can do some fancy titles for each activity.

 

[capri]

thanks thhakala

will have to check grouping out. I have Access97 and there doesn't seem to be anything on the menus. I did have a look thru the Help section and found a few topics on Group objects and collections so will have a read thru those and hopefully find out how to do what you suggested. Unfortuantely I got the office to buy a QUE book on Access because of previous success with QUE books. However it only discusses Group Macros and Workgroups.