Can this be done?

[Kathleen Turner]

I have an Excel file that has list of serial numbers followed by a 4 digit
code. I have an Access database that has the serial numbers but the field
for the 4 digit code is either blank, has the 4 digit code, or contains 4
X's.

What I would like to do is go through each record in Access, check the
serial number and then compare that serial number with the one in the Excel
spreadsheet. If the serial number in Access has a NULL value or a string of
four X's then I would like extract the 4 digit code from the Excel
spreadheet and populate the 4 digit code in the corresponding field.

Can I do this with VB and Access? How difficult do you professionals think
this might be. I am only a novice programmer.

I hate to think of doing this by hand. We have about 40,000 records to
update.

Your thoughts and advice are well appreciated.

Kathleen

[Bruce M. Thompson]

> I have an Excel file that has list of serial numbers followed by a 4 digit

code. I have an Access database that has the serial numbers but the field
for the 4 digit code is either blank, has the 4 digit code, or contains 4
X's.

What I would like to do is go through each record in Access, check the
serial number and then compare that serial number with the one in the Excel
spreadsheet. If the serial number in Access has a NULL value or a string of
four X's then I would like extract the 4 digit code from the Excel
spreadheet and populate the 4 digit code in the corresponding field.

Can I do this with VB and Access? How difficult do you professionals think
this might be. I am only a novice programmer.

Is there some value (or set of values) in the Excel "table" that would link each
record to it's corresponding record in the Access table? If not, it's unlikely
that this could be done with an update query or even using VBA.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Kathleen Turner]

Thank you. I appreciate your quick response.

Each serial number and 4 digit code is unique. There are no two alike. The
file of serial numbers that was provided to me is over 1051 pages with each
page containing well over 100 serial numbers. Not all of them are in use.
I have an Access database with the serial numbers and a field for the 4
digit code. But over the past 2 years employees have been either leaving it
blank or putting 4 Xs in it when they could not find the code.

My head honcho would like to see our MS Access DB, which is about 40,000
records strong, to reflect the 4 digit codes. What will be requested is
thaat I assign 4 employees the task of manually updating these fields. I
think there has to be a better way.

Thanks again.

Kathleen


Bruce M. Thompson <bthmpson@big_NOSPAM_foot.com> wrote in message
news:vn************@corp.supernews.com...

I have an Excel file that has list of serial numbers followed by a 4 digit

code. I have an Access database that has the serial numbers but the field for the 4 digit code is either blank, has the 4 digit code, or contains 4 X's.

What I would like to do is go through each record in Access, check the
serial number and then compare that serial number with the one in the Excel spreadsheet. If the serial number in Access has a NULL value or a string of four X's then I would like extract the 4 digit code from the Excel
spreadheet and populate the 4 digit code in the corresponding field.

Can I do this with VB and Access? How difficult do you professionals think this might be. I am only a novice programmer.
Is there some value (or set of values) in the Excel "table" that would

link each record to it's corresponding record in the Access table? If not, it's unlikely that this could be done with an update query or even using VBA.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access) NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Mike MacSween]

"Kathleen Turner" <kt*****@visuallink.com> wrote in message
news:vn************@corp.supernews.com...

I have an Excel file that has list of serial numbers followed by a 4 digit
code. I have an Access database that has the serial numbers but the field
for the 4 digit code is either blank, has the 4 digit code, or contains 4
X's.

What I would like to do is go through each record in Access, check the
serial number and then compare that serial number with the one in the Excel spreadsheet. If the serial number in Access has a NULL value or a string of four X's then I would like extract the 4 digit code from the Excel
spreadheet and populate the 4 digit code in the corresponding field.

Can I do this with VB and Access? How difficult do you professionals think this might be. I am only a novice programmer.

I hate to think of doing this by hand.

Me too!

You seem to be mixing up your terminology, in your 2nd para you talk about
the serial number in Access being null or a string of 4 X's. Do you mean the
code? I assume you do.

You've got:

Excel file:
serial# code
100302 2435
349098 3458
etc.

Access table
serial# code
100302 NULL
349098 3458
324554 XXXX
etc.

Yes? And once this stuff is all into Access then you can discard the Excel
file?

First of all get you Excel table into Access. In Access File/Get External
Data/Import. You'll get a wizard asking you questions about how you want to
import. I don't know what the format is in Excel, so do you best there.
Anyway you'll end up with a new table in Access.

So you've now got 2 tables. I'll call them accessData and excelData. Make a
new query. Add those two tables. Join them on serial#:

SELECT accessData.[serial#], accessData.code, excelData.[serial#],
excelData.code
FROM accessData INNER JOIN excelData ON accessData.[serial#] =
excelData.[serial#];

Make sure the records 'line up' how you expect.

Change the query to an update query. And change the SQL to this:

UPDATE accessData
INNER JOIN excelData
ON accessData.[serial#] = excelData.[serial#]
SET accessData.code = [excelData].[code]
WHERE (((accessData.code)="xxxx")) OR (((accessData.code) Is Null));

You don't have to run it yet. Click the datasheet icon to see what WILL be
changed when you do run it. OK? then run it (Query/run). Change it back to
the first SELECT query. Does that look like what you expected?

That's if I've understood what you want. I'd strongly advise you to do all
this on a copy of the database first. There are all sorts of questions about
the data too. Is serial# unique? Are you sure that the only values in code
are a valid code, "xxxx" or null? If serial# is unique (or meant to be) then
I'd set unique a unique index on it to make sure.

HTH, Mike MacSween

[Terry Kreft]

I'm still not absolutely clear about the layout of the Excel spreadsheet.

Your description implies two scenarios to me
1) A column of serial numbers and a second column of codes
or
2) A column containing the serial number and code in combination

************************************
No matter what you do always try your update strategy against copies of your
Access database and Excel spreadsheet first before doing a live run
************************************

With scenario 1
a) link to the speadsheets from Access.
b) Run update queries to copy from the code column in the linked Excel
sheets to the code column in your Access table.

Exactly how you do b) this depends on whether the serial numbers are unique
or whether the Serial number/code is unique.

With Scenario 2
a) link to the speadsheets from Access.
b) run queries to split the serial number and code out into temporary
Access tables
c) Run update queries to copy from the code column in the temporary
Access tables to the code column in your Access table.

Exactly how you do c) this depends on whether the serial numbers are unique
or whether the Serial number/code is unique.

Terry
"Kathleen Turner" <kt*****@visuallink.com> wrote in message
news:vn************@corp.supernews.com...

Thank you. I appreciate your quick response.

Each serial number and 4 digit code is unique. There are no two alike. The file of serial numbers that was provided to me is over 1051 pages with each page containing well over 100 serial numbers. Not all of them are in use.
I have an Access database with the serial numbers and a field for the 4
digit code. But over the past 2 years employees have been either leaving it blank or putting 4 Xs in it when they could not find the code.

My head honcho would like to see our MS Access DB, which is about 40,000
records strong, to reflect the 4 digit codes. What will be requested is
thaat I assign 4 employees the task of manually updating these fields. I
think there has to be a better way.

Thanks again.

Kathleen


Bruce M. Thompson <bthmpson@big_NOSPAM_foot.com> wrote in message
news:vn************@corp.supernews.com...

I have an Excel file that has list of serial numbers followed by a 4 digit code. I have an Access database that has the serial numbers but the field for the 4 digit code is either blank, has the 4 digit code, or

contains

4 X's.

What I would like to do is go through each record in Access, check the
serial number and then compare that serial number with the one in the Excel spreadsheet. If the serial number in Access has a NULL value or a string of four X's then I would like extract the 4 digit code from the Excel
spreadheet and populate the 4 digit code in the corresponding field.

Can I do this with VB and Access? How difficult do you professionals think this might be. I am only a novice programmer.

Is there some value (or set of values) in the Excel "table" that would

link each

record to it's corresponding record in the Access table? If not, it's

unlikely

that this could be done with an update query or even using VBA.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

> NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<