Custom Groups

  • Thread starter Thread starter Claire
  • Start date Start date
C

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
 
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.
 
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

Duane Hookom said:
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


Claire said:
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
Click to expand...
Click to expand...
 
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


Claire said:
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

Duane Hookom said:
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


Claire said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top