Group existing records in a table into categories

[marti]

I am a beginning access 2003 user.

I have a database that is used to track incoming phonecalls based on the
type of issue.

The different issues are listed in a table and appear in a drop down menu on
the main form.

I would like to reduce the number of issues listed in the dropdown menu,
without losing the historical data, by grouping the existing 60 issues into
18 categories.

Any ideas on how I can do this?

Thanks!

 

[Philip Herlihy]

I am a beginning access 2003 user.

I have a database that is used to track incoming phonecalls based on the
type of issue.

The different issues are listed in a table and appear in a drop down menu on
the main form.

I would like to reduce the number of issues listed in the dropdown menu,
without losing the historical data, by grouping the existing 60 issues into
18 categories.

Any ideas on how I can do this?

Thanks!

I had a similar issue with my billing database. I'd been logging
expenses by type, using an ad-hoc classification of my own. I later
found that HMRC (that's Her Majesty's Revenue & Customs, for the
amusement of non-Brits) have their own classification, with less categories.

My table for expense items has a link (foreign key) to a table of "my"
expense classes. I created a new table of HMRC expense classes, and
extended the table of "my" classes with a further field to hold a
reference to one of the HMRC-class records. (Classic many-to-one
relationship). I've carried on classifying things my way, as it
happens, but can easily run a report showing the HMRC classes instead if
I choose.

How you handle this may depend on whether your 18 categories are a
subset of the full 60, or a generalisation of them. In my case the HMRC
classes were a rather different breakdown, although it wasn't difficult
to put mine in their pigeon-holes. If they are a subset, and you simply
want to make it impossible to choose some of the minor ones in future,
then it's simply a question of adjusting the RowSource of the relevant
combo box to select only the ones you want to use in the future.

Alternatively, with judicious use of make-table and update queries, you
could first create a table holding a reference to each phonecall with a
reference to the old class, and you'd use this this to look up
historical data. Then (assuming this is appropriate) you could
re-classify everything with the new categories, based on the old ones.

Broad-brush, but I hope you get the idea?

Phil, London

 

[KenSheridan via AccessMonster.com]

You first need to create a Categories table with column Category as its
primary key. Then add a Category column to your Issues table and create a
relationship between the two on the Category columns, enforcing referential
integrity and cascade updates, the latter in case you ever change a category
name in which case the matching category values in Issues will be
automatically updated.

If you leave it at that you'll still have to select an individual issue per
phone call of course as there is no direct relationship between phone calls
and categories. As I understand it, however, you now want to select from
just the 18 categories when entering new records. A solution to this would
be to add an extra 18 issues, one for each category, to the issues table
along these lines:

Issue Category
________________________________
Category 1 Unspecified Category 1
Category 2 Unspecified Category 2
< and so on to>
Category 18 Unspecified Category 18

Your Issue combo box (what you refer to as a 'drop down menu') on the form
would now have a RowSource property of:

SELECT Issue, Category
FROM Issues
WHERE Issue LIKE "*Unspecified"
ORDER BY Category;

and its other properties would be:

ControlSource: Issue
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

You'll now select a category from the combo box's list, but the actual value
of the control and the Issue column to which it is bound, will be the
relevant 'unspecified' issue for that category. If you wish you can also
have a text box bound to the Issue column on the form and make it read only
by setting its Locked property to True (Yes) and its Enabled property to
False (No). This text box will show the original issues in the historical
records and the relevant 'unspecified' issue for any new ones once you select
a category in the new combo box. For the historical record the new combo box
will be blank, however, as only the 'unspecified' issues are in its
underlying list, albeit hidden. If you wanted the historical records to show
the category as well as the issue you could change the RowSource of the Issue
combo box, requery the control and lock/unlock it in the form's Current event
procedure with code along these lines:

Dim strSQL As String

If Me.NewRecord Or Right(Me.Issue, 11) = "Unspecified" Then
strSQL = _
"SELECT Issue, Category " & _
"FROM Issues " & _
"WHERE Issue LIKE """*Unspecified""" " & _
"ORDER BY Category;"
Me.cboIssue.Locked = False
Else
strSQL = _
"SELECT Issue, Category " & _
"FROM Issues " & _
"ORDER BY Issue;"
Me.cboIssue.Locked = True
End If

Me.cboIssue.RowSource = strSQL
Me.cboIssue.Requery

Note that this would only work with a form in single form view, not in
continuous form or datasheet view as in the latter cases when the RowSource
of the combo box is restricted to the 'Unspecified' issues the combo boxes in
the rows for the historical records would go blank; the data would still be
there in the underlying column, but you wouldn't see it in the historical
rows in the form.

Ken Sheridan
Stafford, England