It would be possible, but the referenced table would have to include all
possible combinations of the criteria. You haven't really given us enough
information to say what this would imply as regards the detailed contents of
the referenced table, but it depends on how many different attributes make up
the 25 different criteria for determining the category.
Taking your simplest possible example of cost being the sole determining
factor for a category, lets say items with a cost of 100 are category A, of
200 category B of 300 category C and so on. The table would then simply have
columns Cost and Category with values like so:
100.00 A
200.00 B
300.00 C
<and so on>
But this assumes every item has a cost of 100, 200, 300 etc. If you specify
a range of costs with LowerCost and HigherCost columns in pace of the Cost
column then the table would be:
100.00 199.99 A
200.00 299.99 B
300.00 399.99 C
<and so on>
so items with intermediated costs can be accommodated.
If you then start introducing other criteria then things begin to get a
little more complicated. You gave length as an example in your first post,
so lets say that for each item, if the length is over 25 this puts the item
in another category, D, E or F say for each cost range, then you also need
LowerLength and HigherLength columns in the referenced table.
0 25 100.00 199.99 A
25.01 1000 100.00 199.99 D
0 25 200.00 299.99 B
25.01 1000 200.00 299.99 E
0 25 300.00 399.99 C
25.01 1000 300.00 399.99 F
<and so on>
where the 1000 values are artificially high upper length values more than no
item will ever be, so the range 25.01 to 1000 in effect means any length more
than 25.
Now lets say you have a category X which is dependent solely on a cost of
1000 USD or more regard less of length an further crow would be added to the
table:
0 1000 1000.00 1000000.00 X
where in this case the one million HigherCost value is an artificially high
one which no item will reach, so the cost range 1000.00 to 1000000.00 in
effect means 1000 USD or more or more, and the length range 0 to 1000 in
effect means any length.
If you introduce other criteria than cost and length the number of rows in
table is growing to grow as you'll need to combine these criteria with the
above in the same way that the length criteria were combined with the cost
criteria in the above example. Just how much it will grow and how manageable
the table would thus be depends on the number of determining factors involved
and the number of combinations of those factors. In fact, even with a large
number of determining factors and combinations of them it is possible to fill
the table without having to enter each row one by one, but this does require
building and executing an 'append' query in code behind an unbound dialogue
form in which the values for the different parameters. You have indicated
that you are not comfortable with SQL, so that might be a challenge, and I
would not want to mislead you into thinking it’s a trivial task.
In the above examples each category is determined by one combination of
factors only, but there is no reason of course why one category cannot be
determined by more than one combination of criteria, e.g. a lower length and
higher cost might put an item in the same category as one with a higher
length and lower cost.
So, whether it can be done or not really depends on just how easy it is to
firstly fill the ItemCategories table, and then maintain it as you change the
parameters over time. You would of course use a form bound to the
ItemCategories table for this to provide a user-friendly interface, but it’s
the really the number of rows which the table would require which is the key
factor, as these must not be self-contradictory, otherwise you could find
that a row in the Items table has more that one match in the ItemCategories
table.
If you believe the table can be correctly filled and managed thereafter, then
you could execute an 'update' query by joining the Items table to the
ItemCategories table and update the Category column in Items with that in
ItemCategories. By using ranges of values the join is not a simple one which
can be created in query design view, but you could first create a query in
design view using straightforward joins such as Items.Cost = ItemCategories.
LowerCost etc, then go into SQL view and amend the join expression in each
case to (Items.Cost BETWEEN ItemCategories.LowerCost AND ItemCategories.
HigherCost) etc. Note that enclosing each BETWEEN….AND expression in
parentheses is crucial here. So some fairly simple editing of the SQL would
be all that's required.
However, the main point here is that if it is possible to have the
determining values in a separate table, then you do not need to execute an
update query at all. The reason for this is that the Items table would then
redundantly contain the category for each item, so, dispensing with the
category column in Items and simply joining Items and ItemCategories in the
same way as you would for an UPDATE query but this time in a SELECT query
would give you the category for each item. When the determining factors for
a category change its then a case of just editing the ItemCategories table.
What the ItemCategories table would really be doing here is modelling the
relationship between Items and Categories, so you should also have a
Categories table, with just one column and 25 rows. This allows referential
integrity to be enforces so that ItemCategories can only contain legitimate
Category values, A-Y. You are probably familiar with a many-to-many
relationship being modelled by a table in this way (sometimes called a
'junction' table) but in this case the relationship is one-to-many,
Categories being related one-to-many to ItemCategories, and ItemCategories
being related one-to-many to Items, i.e. each item can be in only one
category at any one time, but each category can encompass many items.
Ken Sheridan
Stafford, England
First, let me thank both of you for your responses.
I have approximately 25 different categories (actually called classes A-Y)
The 2 criteria that determine which class an item is assigned to are each
different (25 different set of criteria)
I was wondering about creating a table with the criteria and then using
those criteria in Update Queries that use the value in the table field as the
search criteria in the query.
For example, if I create a table with "cost" as a field and make its value
100, can I reference that table field in the Update Query? The Update Query
would look at all items in the database and update the class of those items
(with a cost = 100) to the new class.
That way I can change the table value (via a Form) and simply run the Update
Query every time there is a need to make a change
I hope I'm asking the question clearly.
By the way, I am not working with SQL. I don't understand how to do it. I am
strictly trying to do this with the standard Update Query. Is it possible?
If the parameters which determine the category are the same set of attributes
in each case then this is a relational issue rather than an update one.
[quoted text clipped - 66 lines]
Many thanks for helping me figure this one out!
--
.
