grouping items in a report (Access97)

Topic

New Reply

I have a Database with a number of activities in one field. The all have an identifying number (UID). I used a text mask 0000.0 for the UID field. Most activities end in .0, however if they are sub activities (related to the main
activity but performed by a different dept) they end in either .1 or .2.

I can sort the activities by dept, because I have a field for each record to indicate the dept. However I would like to produce a report that would look like this:

UID
1000.0
— 1000.1
____________________________
1050.0
— 1050.1
— 1050.2
____________________________
1052.0
— 1052.2

Can anyone suggest a way that I might be able to accomplish this?

Thanks for any ideas.

Reply | Quote

Replies

Create a calculated field in a query based on this table called MainActivity set it to: Left(UID,4) and add all the other fields you want in the report into the grid as well. Base your report on this query. It sounds like you have a report group called Dept, so add another called MainActivity. Sort the report by Dept, then MainActivity, then UID. This should produce the desired results, as long as there are always 4 characters defining the MainActivity.

However, if there are Main Activities and SubActivities, these should be held in two tables with a one to many relationship between them. It does not seem normalized they way that you are describing it.

Reply | Quote

Thanks Thomas,

I tried the Left(UID,4) but the query simply listed all of the activities without the decimal place eg
1000
1000
1000
1050
1050

I think I will have to consider making a separate table for SubActivities. I was hoping to avoid this, as once the users start making their own input to the database, it is likely to confuse them. Originally I just wanted all of the activites to follow a logical numbering sequence. I put the decimals in place in case one or more activities had to be inserted between 2 that were consecutive in number. However as I was building the database and showed it to my manager, he wanted some activities divided into two or more pieces. eg.
Dept A will provide evidence of all required insurance in the form of a completed certificate of insurance.
Dept B will verify that Dept A has provided evidence of all insurance.

This originally was one activity, but I divided it into 2. One Dept is more of a managing area so many of it’s activities are simply verifying that Dept A completed the required task.

As you can see the activites are directly related to one another and I wanted some way to reflect that with the numbering. If I could continue to have total control over the input of the activities, it would be no problem, but I am only on contract here and eventually the users will be doing a lot more of the creation of activities.

I suppose I could create an input form for them, so that they wouldn’t really have to know that the activities are in different tables.

Reply | Quote

That was what Left() was expected to do. It’s purpose was to create a calculated field that you could sort and group on. You can still list the entire UID by placing that on the query grid and report as well.

If you create two tables, they can be linked on the MainActivity field, which will keep all associated records together by the one to many relationship. Your numbering can still be utilized. In this case, you could create an input form using a main/subform which will make the actual two table structure transparent to the users inputting data. Once they pick the MainActivity, display the linked activities in the subform and that will “control” the association. They can add new records to the subform and you can write the MainActivity field from the main form into the many side of the relationship.

Personally, I would not depend on users to number items correctly. I’d prefer the database handled this by creating normalized tables and placing referential integrity on the relationship. HTH

Reply | Quote