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" <kturner@visuallink.com> wrote in message
news:vn1qq6h518bp07@corp.supernews.com...[color=blue]
> Thank you. I appreciate your quick response.
>
> Each serial number and 4 digit code is unique. There are no two alike.[/color]
The[color=blue]
> file of serial numbers that was provided to me is over 1051 pages with[/color]
each[color=blue]
> 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[/color]
it[color=blue]
> 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:vn1mkiirjdfs09@corp.supernews.com...[color=green][color=darkred]
> > > I have an Excel file that has list of serial numbers followed by a 4[/color][/color]
> digit[color=green][color=darkred]
> > > code. I have an Access database that has the serial numbers but the[/color][/color]
> field[color=green][color=darkred]
> > > for the 4 digit code is either blank, has the 4 digit code, or[/color][/color][/color]
contains[color=blue]
> 4[color=green][color=darkred]
> > > 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[/color][/color]
> Excel[color=green][color=darkred]
> > > spreadsheet. If the serial number in Access has a NULL value or a[/color][/color]
> string of[color=green][color=darkred]
> > > 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[/color][/color]
> think[color=green][color=darkred]
> > > this might be. I am only a novice programmer.[/color]
> >
> > Is there some value (or set of values) in the Excel "table" that would[/color]
> link each[color=green]
> > record to it's corresponding record in the Access table? If not, it's[/color]
> unlikely[color=green]
> > that this could be done with an update query or even using VBA.
> >
> > --
> > Bruce M. Thompson, Microsoft Access MVP
> >
bthmpson@mvps.org (See the Access FAQ at
http://www.mvps.org/access)[color=darkred]
> > >> NO Email Please. Keep all communications[/color]
> > within the newsgroups so that all might benefit.<<
> >
> >[/color]
>
>[/color]
