Add column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I am fairly new to access.

I have a table with some queries, but I want to group the items, ie, add a
column to the table with a generic code. In Excel, I would copy and paste by
highlighting the rows, but I can only seem to do them one by one.

Is there any way of doing them in groups?
 
I have 19000 rows of data, one of the columns has about 300 differents items.
I want to reduce this to about 30 different items by grouping them so want a
quick way of doing it. I was going to create a new column and then put the
groups in, but don't want to have to type it 19000 times.
 
If you know how you want to group the items, you can do it in a query using
IIF statements.
 
First, you need to define your grouping so you know which individual values
of the 300 tranlate to the 30. In other words, if ABC, BCD, CDE, etc always
translates to the new X1A, then you can write a query to make the change.

Here is a criteria example that assumes the 300 values are in a field named
OLD_CODE and you want to put the new value in NEW_CODE. And the translation
is like:
Old New
123 1
234 1
345 1
456 2
124 2
432 2
424 3
505 3
656 3
700 4
701 4
702 4
703 4

=IIf([OLD_CODE] IN(123, 234, 345), 1, IIf([OLD_CODE] IN(456, 124, 432), 2,
IIF([OLD_CODE IN(424, 505, 656), 3, IIf([OLD_CODE] BETWEEN 700 AND 703, 4,
0))))
 
Add a new table
TblConversion
OldValue
NewValue

List each unique old value in the table
List the corresponding new value.

Now you can use the tblConversion to display the newValues and group by the
new values. Just add it to your queries and join the two tables on the
relevant field when you need the grouped values.

If you wish you can update the existing table by adding a new column to it
and then running an update query. The SQL for the update would look
something like the following

UPDATE OldTable INNER JOIN tblConversion
On OldTable.OldField = tblConversion.OldValue
Set OldTable.NewField = [tblConversion].[NewValue]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I have 19000 rows of data, one of the columns has about 300 differents items.
I want to reduce this to about 30 different items by grouping them so want a
quick way of doing it. I was going to create a new column and then put the
groups in, but don't want to have to type it 19000 times.
Click to expand...

The simplest way would be to create a translation table with 300 rows; each
row would have an ItemID (matching the item number in your big table) and a
GroupID, one of 30 different "new" codes. You can then create a Query joining
your table to this translation table by the ItemID, and you'll have the new
group ID available.

John W. Vinson [MVP]
 
Back
Top