Filling Missing Data

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

Guest

I have a file that I've pulled into Access. When the information downloaded,
it didn't completely fill in some data, i.e. I have a customer column and an
SIC Code file. In the SIC code column, I want all occurances of the customer
number to contain the same SIC code. My file is almost 253,000 lines, so I'm
helping there is a relatively easy way to do this.
 
Ash said:
I have a file that I've pulled into Access. When the information
downloaded,
it didn't completely fill in some data, i.e. I have a customer column and
an
SIC Code file. In the SIC code column, I want all occurances of the
customer
number to contain the same SIC code. My file is almost 253,000 lines, so
I'm
helping there is a relatively easy way to do this.
Click to expand...

Probably the easiest way is to create a temp table that has the customer
number and the correct SIC code and then do an update query off of that.

HTH;

Amy
 
BACK UP your data first and then try this update query.

UPDATE YourTable as T INNER JOIN YourTable as S
ON T.CustomerNumber = S.CustomerNumber
SET T.SIC = .[SIC]
WHERE T.SIC is Null and S.SIC is Not Null

That should work as long the SIC is consistently one value or null for any
customer. You might want to check that first. You can do that by building
a query that gets the unique values of CustomerNumber and SIC and saving
that query. THen using the saved query, you can run a query that will give
you customer numbers that have two or more different SIC's.

SELECT DISTINCT CustomerNumber, SIC
FROM YourTable
WHERE SIC is Not Null

SELECT CustomerNumber, Count(SIC)
FROM SavedQuery
HAVING Count(SIC) > 1

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks John, this looks like a great help as well as Amy's. I will
definitely keep a record of it for future issues. Many thanks!
--
Thanks,

Ash


John Spencer said:
BACK UP your data first and then try this update query.

UPDATE YourTable as T INNER JOIN YourTable as S
ON T.CustomerNumber = S.CustomerNumber
SET T.SIC = .[SIC]
WHERE T.SIC is Null and S.SIC is Not Null

That should work as long the SIC is consistently one value or null for any
customer. You might want to check that first. You can do that by building
a query that gets the unique values of CustomerNumber and SIC and saving
that query. THen using the saved query, you can run a query that will give
you customer numbers that have two or more different SIC's.

SELECT DISTINCT CustomerNumber, SIC
FROM YourTable
WHERE SIC is Not Null

SELECT CustomerNumber, Count(SIC)
FROM SavedQuery
HAVING Count(SIC) > 1

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
 
Back
Top