Hi,

I'm using Access 2003 on Windows XP.

My client wants to classify his customers according to ten
classifications. Each customer can fall into one or more of these ten
classifications.

I want to display each of the possible ten classifications for each
customer record, and then highlight the ones that are applicable,
whilst keeping the others in place but not highlighted. The idea is
to have a standard layout that is the same for each customer, so that
the user can identify just by the position of the highlighted whether
this customer has the appropriate classification.

I have set up the data with a company table, a classifications table
and a x-ref (cross-reference) table which has this sort of structure
x-refID, customerID, classificationID so that I can record more than
one classification per customer.

Are there any in-built tools that can do this?

The only thing I can think of is to list the ten possible
classifications as unbound text boxes, and then using the OnCurrent
event, loop through a recordset of the classificationIDs which are in
the x-ref table for that customerID and then highlighting the
appropriate text box with bold or a color or similar.

Thanks in advance,

Franc.

franc sutherland wrote:

One method. You could use a listbox. Set the .Selected property to
true or false for the item. Set the listbox as MultiSelect or Simple so
you can select more than 1 row.

On Mar 23, 10:38*am, franc sutherland
<franc.sutherl... (AT) googlemail (DOT) com> wrote:
you could use a continuous subform to show the classifications, with a
checkbox indicating that a particular classification is assigned to
the customer
you just need to create a cross-product query (no joins) that always
returns all classificationId's

On Mar 23, 4:55*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Hi Salad,
Thanks for your reply. I have set up the list box as Simple and can
select more than 1 row.
What should it's data source be? Should it have a control source?
Thanks,
Franc.

On Mar 23, 7:29*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Hi Roger,
Thanks for your reply,
How do I connect together the continuous forms showing the ten
classifications with the data in the x-ref table which lists the
classificationIDs against customerID?
Thanks,
Franc.

On Mar 24, 6:09*am, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:
assuming we have three tables,
tblCompany
customerId
customerName

tblClassification
classificationId
classification

tblCompanyClassification
customerId
classificationId

qryAllCC - this query is a cross-product (ie. no joins),
giving us all combinations of customer / classification

select customerId
, classificationId
from tblCompany
, tblClassification


qryCompanyClassification - this query gives all
classifications by customer, flagged which are assigned

select a.customerId
, a.classificationId
, iif(nz(b.customerId, "") = "", 0, 1) as assigned
from qryAllCC as a left join
tblCompanyClassification as b on a.customerId = b.customerId
and a.classificationId =
b.classificationId

franc sutherland wrote:
I don't know. Are the customer classifications static? Or are the
classifications different based on a status type?

I might have a table of 10 classifications if static. Otherwise select
/present your classifications based on customer type. There you have a
list.

Then you'd have a table to store the selections. I might have a command
button to update the customer classifications once all classifications
are selected or create/update/delete a customer status record when the
row is selected/deselected.

'clears/sets all rows to unselected.
'ListType is name of control in example
Dim var As Variant
If Me.ListType.ItemsSelected.Count > 0 Then
For Each var In Me.ListType.ItemsSelected
Me.ListType.Selected(var) = False
Next
Endif

'selects the first row as true
Me.ListType.Selected(0) = True 'or set to false/unselected

You could enumerate the listbox. The following example will display all
rows in the listbox that are selected and at the end present a list of
all items in the listbox. It will select/display the second column (1
as the listbox is zero based) to the op. The listbox contains an ID
(col 0, hidden) and the value (col 1, visible).
Dim s As String
Dim i As Integer
For i = 0 To Me.ListType.ListCount - 1
If Me.ListType.Selected(i) Then
MsgBox Me.ListType.Column(1, i) & " is selected"
End If
s = s & Me.ListType.Column(1, i) & vbNewLine
Next
If s > "" Then MsgBox s

You could have a table with the customerid and customertype. Grab the
customer type from the listbox (col 0) and search the table for the type
and custid. If found, selected is true else false. Scan the list after
edit. If not selected, see if record exists and if so delete it. If
selected and rec now found, add it.

Using a listbox is one method in doing what you want.

Look at Selected, ItemsSelected, ListCount, Column in Help for examples
and clarification.

On Mar 24, 4:34*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Hi Roger,
Thanks for your explanation. I have tried to create the queries as
you suggested using the names I had already set up, but I got a list
of all the companies and all the classifications with a 0 in the
assigned column.

Here is the SQL from my version of qryCompanyClassification.

SELECT qry_all_cc.company_id , qry_all_cc.classification_id,
IIf(nz(tbl_company_classification.company_classifi cation_company_id,"")="",
0,1) AS assigned
FROM qry_all_cc LEFT JOIN tbl_company_classification ON
(qry_all_cc.classification_id =
tbl_company_classification.company_classification_ classification_id)
AND (qry_all_cc.company_id =
tbl_company_classification.company_classification_ company_id);

Could you tell me where I've gone wrong, please?

Many thanks,

Franc.

On Mar 24, 5:08*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Hi Salad,

There are ten classifications in a table, tbl_classifications. There
is no additional segregation of the customers by, for instance, type.
Each of the companies will fit into at least one, and possibly, all
ten classifications.

These are linked to the specific companies with the table,
tbl_company_classifications, which has the fields
company_classification_id, company_id, classification_id.

I don't understand how to associate the data in
tbl_company_classifications with a 'simple' list box.
How would I update the data when the selections are changed?

Thanks for your help,

Franc.

On Mar 30, 4:23*pm, franc sutherland <franc.sutherl... (AT) googlemail (DOT) com>
wrote:
Actually, some companies will fit in to none of the classifications.
Apologies,
Franc.