Pardon me for stepping in.
STEP 1: BACKUP your data before attempting any of the following.
STEP 2: BACKUP your data before attempting any of the following.
You could try an update query that looks like the following.
UPDATE [YourTable]
SET [Number] = DLookup("Number","YourTable","Number is Not Null AND
State=""" & [State] & """ AND [Company Name] = """ & [Company Name] & """")
WHERE [Number] is Null
If you are using an update query and building it in the grid then the update
to line would be
DLookup("Number","YourTable","Number is Not Null AND State=""" & [State] &
""" AND [Company Name] = """ & [Company Name] & """")
More efficient would be to build a query that gets all the Number values and
then use that to do the update.
First query (save as qCompanyNumber)
SELECT Distinct [Company Name], [State], [Number]
FROM [Your Table]
WHERE [Your Table].[Number] is not null
Now using that
UPDATE [Your Table] INNER JOIN qCompanyNumber
ON [Your Table].[Company Name] = qCompanyNumber.[Company Name]
and [Your Table].[State] = qCompanyNumber.[Number]
SET [Your Table].Number = qCompanyNumber.[Number]
WHERE [Your Table].Number is Null
IF that is confusing, you can do this all in the query grid.
-- Open a new query and select your table
-- Add the three fields to it
-- enter Is Not Null as the criteria under the NUMBER field
-- Save the query as qCompany number
-- Open a new query and select your table and the new query you just created
-- Click on Company name in the table and drag to company name in the query
-- Click on State in the table and drag to State in the query
(that should create two join lines between the query and the table)
-- Add the table.number field to the field list
-- Select Query Update from the menu
-- In the UPDATE to line under table number type [qCompanyNumber].[Number]
One possible problem is that you could have more than one Number in the
table for any combination of Company and State. If that is the case, you
will get a number assigned but it won't necessarily be consistent.
ABC MD 2112
ABC MD 2121
ABC MD ____
ABC MD ____
IN the above the third line would end up with 2112 or 2121 and the fourth
line would end up with 2112 or 2121. HOWEVER there is no way to determine
which value the lines would end up containing.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Mattie said:
I'm so sorry. The first table should have had ABC instead of JKL.
(corrected
below). Yes, updating the record is correct. Mainly, there are a whole
list of companies that repeat along with their respected states. The
column
I'm wanting to convert is a four digit numeric code that is used for
billing.
If the Company Name and State match I simply want it to insert into the
same
rows.
John W. Vinson said:
On Wed, 2 May 2007 19:00:00 -0700, Mattie
<
[email protected]>
wrote:
I have a table with one column that has data separate from the rest of
the
table. I need to move it to the same row as the other columns when the
information matches.
Example:
Table Before
[Company Name] [State] [Number]
ABC CA
DEF AZ
GHI NE 3
ABC CA 4
Corrected Table (Move number 4 because it matches the Company Name(ABC)
and
state CA.
[Company Name] [State] [Number]
ABC CA 4
DEF AZ
GHI NE 3
ABC CA 4
I'm sorry, this makes no sense. In what way does JKL match ABC? And
you're not
*moving* the 4, you're updating the ABC record to convert a NULL to a 4.
Is
that based just on CA, or on something else?
Care to describe the meaning of Number and just what you're trying to
accomplish?
John W. Vinson [MVP]
