Custom Groups

[Claire]

This is something I would like to do in multiple reports, but here's my
example:

I have a table of jobs that lists Job #, Sales Rep, and lots of other job
information. I currently have a report grouping by Sales Rep. I would
*like* to put certain sales reps together. ie It would have the following
groups for the sales reps: A, B, C & E, D, etc. This grouping may change
depending on who needs the reports, but should be basically consistent over a
couple months or so.

I am using Access 07 and have played around with a bunch of its
capabilities, but have yet to delve into VBA (though it's on the list of
things to learn).

Thanks for your suggestions,
Claire

 

[Duane Hookom]

This should all be accomplished through values stored in tables. A flexible
solution would involve creating a table of groups.
tblGroups
===========
[GroupID] primary key autonumber
[GroupName] text field

And a table that links sales reps with a group:
tblRepGroups
============
[SalesRepIDField]
[GroupID]

You could then create and delete groups and membership. You would only
maintain data, not code or SQL or other objects.

 

[Claire]

Thanks Duane. I've started setting up these tables.

I have a follow-up question. A bunch of this data (thousands) is residual,
and not so pretty. There may be a sales rep with the initials ABC who is
listed as ABC, ABC-1, ABC-2, ABC-3, etc. My inclination is that I am going
to have to list all of these in the tblRepGroups table. Currently they're
being grouped by the first 3 characters of the sales rep. Is there a way to
use wildcards in a table? Or can I link the first characters of a sales rep
to a group another way?

Thanks again,
Claire

This should all be accomplished through values stored in tables. A flexible
solution would involve creating a table of groups.
tblGroups
===========
[GroupID] primary key autonumber
[GroupName] text field

And a table that links sales reps with a group:
tblRepGroups
============
[SalesRepIDField]
[GroupID]

You could then create and delete groups and membership. You would only
maintain data, not code or SQL or other objects.

--
Duane Hookom
Microsoft Access MVP

This is something I would like to do in multiple reports, but here's my
example:

I have a table of jobs that lists Job #, Sales Rep, and lots of other job
information. I currently have a report grouping by Sales Rep. I would
*like* to put certain sales reps together. ie It would have the following
groups for the sales reps: A, B, C & E, D, etc. This grouping may change
depending on who needs the reports, but should be basically consistent over a
couple months or so.

I am using Access 07 and have played around with a bunch of its
capabilities, but have yet to delve into VBA (though it's on the list of
things to learn).

Thanks for your suggestions,
Claire

 

[Duane Hookom]

I would work to clean up the data. You can append ABC-1, ABC-2, etc to
tblRepGroups using a wild card in the append query.

--
Duane Hookom
Microsoft Access MVP

Thanks Duane. I've started setting up these tables.

I have a follow-up question. A bunch of this data (thousands) is residual,
and not so pretty. There may be a sales rep with the initials ABC who is
listed as ABC, ABC-1, ABC-2, ABC-3, etc. My inclination is that I am going
to have to list all of these in the tblRepGroups table. Currently they're
being grouped by the first 3 characters of the sales rep. Is there a way to
use wildcards in a table? Or can I link the first characters of a sales rep
to a group another way?

Thanks again,
Claire

This should all be accomplished through values stored in tables. A flexible
solution would involve creating a table of groups.
tblGroups
===========
[GroupID] primary key autonumber
[GroupName] text field

And a table that links sales reps with a group:
tblRepGroups
============
[SalesRepIDField]
[GroupID]

You could then create and delete groups and membership. You would only
maintain data, not code or SQL or other objects.

--
Duane Hookom
Microsoft Access MVP

This is something I would like to do in multiple reports, but here's my
example:

I have a table of jobs that lists Job #, Sales Rep, and lots of other job
information. I currently have a report grouping by Sales Rep. I would
*like* to put certain sales reps together. ie It would have the following
groups for the sales reps: A, B, C & E, D, etc. This grouping may change
depending on who needs the reports, but should be basically consistent over a
couple months or so.

I am using Access 07 and have played around with a bunch of its
capabilities, but have yet to delve into VBA (though it's on the list of
things to learn).

Thanks for your suggestions,
Claire