Automated Field entry based on another field

  • Thread starter Thread starter Brendan Mather
  • Start date Start date
B

Brendan Mather

I a table of 27000 records, one of the fields (STRAT_TYPE) has 1500
different entries in it. I would like to apply a number (COLORCODE) to each
entry in STRAT_TYPE based on whatever the entry is. So that all similar
field in STRAT_TYPE will have the same COLORCODE. I would like to know how
to make a query do this for me automatically,

ie. STRAT_TYPE COLORCODE
lMrgV 2
lRdvR 3
lMrgV 2
lRdddV 1
lRdvR 3

etc. etc.

Thanks,
Brendan
 
Hi Brendan

you'll need to do an UPDATE query for this

basically the steps are:
1) add a field into the table called COLORCODE
2) go to the query section and choose create query in design view
3) choose the table that has the STRAT_TYPE and COLORCODE fields in it
4) double click on the COLORCODE field to put it into your query grid
5) from the menu change the query type to UPDATE (Query / Update Query)
6) in the UPDATE TO line type
2
7) in the criteria line type
STRAT_TYPE = "lMrgV"
8) run the query
9) check the results
10) repeat for all other types & colours

Of course, before implementing changes such as these, please ensure that you
have backed up your database.

Cheers
JulieD
 
I a table of 27000 records, one of the fields (STRAT_TYPE) has 1500
different entries in it. I would like to apply a number (COLORCODE) to each
entry in STRAT_TYPE based on whatever the entry is. So that all similar
field in STRAT_TYPE will have the same COLORCODE. I would like to know how
to make a query do this for me automatically,

ie. STRAT_TYPE COLORCODE
lMrgV 2
lRdvR 3
lMrgV 2
lRdddV 1
lRdvR 3
Click to expand...

Another way to do this would be to have a 1500 row table with one
record for each STRAT_TYPE, and its corresponding colorcode. You could
then just create a query joining your table to this table by
STRAT_TYPE; the colorcode would be one of the fields in the Query.
 
good idea - i missed the word "different" in my reading of the original
question - you certainly wouldn't want to use my method if you have 1500
different types!!!!
 
Alright, I now have the two separate tables, one of 1513 different
Strat_Type each with its own COLORCODE. Now I need to merge this table with
my main table of 27000 records to that all similar STRAT_TYPE's will have
the same COLORCODE. What type of query should I use and how do I implement
it?

Thanks,
Brendan
 
Alright, I now have the two separate tables, one of 1513 different
Strat_Type each with its own COLORCODE. Now I need to merge this table with
my main table of 27000 records to that all similar STRAT_TYPE's will have
the same COLORCODE. What type of query should I use and how do I implement
it?
Click to expand...

Create a new Query. Join this new table to your big table by
STRAT_TYPE. Select the ColorCode field from this new table and any
fields that you want to see from your big table.

Open this query in datasheet view - you'll see that each record has
its corresponding COLORCODE. Base a Report, a Form, whatever you like
on this Query.

If you insist on storing the colorcode redundantly in your main table
(it *is* redundant if the STRAT_TYPE unambigously defines the
COLORCODE), change the query to an UPDATE query and update
yourtable.colorcode to

[this-new-table].[colorcode]

Run the query by clicking the ! icon and it will move the color codes
into the main table (repeats and all).
 
Back
Top