Update query?

[Nathan C via AccessMonster.com]

Good afternoon. This question may have been answered, and I have not
searched for the right criteria in access monster to find it. If so, I
apologize for the repetition.

I need to update a field in several thousand records. There are two fields
involved: LCS number, and CLD number. The information we have is an account
number (LCS Number). The new values (CLD Number) are from an external source,
and we need to match these two numbers up. We have been given a spreadsheet
with the information, and I need to add in the CLD number relative to each
LCS number.

For example:

LCS # CLD #
FM41 10001
GX22 10002
MT53 10003

Ideally, I would like to instruct access to find the LCS# and then input the
related CLD# in the CLD# field. And do this for all 2000+ records at once.

Any assistance is appreciated.

Thank you.

Nathan

 

[Terry]

At first guess Nathan you could try importing the Excel table into Access,
design the Update Query using that table and the Acess table to be updated,
linked on the LCS number.

You may need to make the LCS number field in the imported table a Primary
Key to enable the update to work.

Regards

 

[Nathan C via AccessMonster.com]

Thank you for the excellent suggestion. I will apply it to the table.

However, it just occured to me that some of these tables may have more than
one entry with the same LCS #. Will that prohibit me from using LCS# as the
primary key?

Thanks again,
Nathan

At first guess Nathan you could try importing the Excel table into Access,
design the Update Query using that table and the Acess table to be updated,
linked on the LCS number.

You may need to make the LCS number field in the imported table a Primary
Key to enable the update to work.

Regards

Good afternoon. This question may have been answered, and I have not
searched for the right criteria in access monster to find it. If so, I

[quoted text clipped - 28 lines]

Nathan

 

[John Vinson]

However, it just occured to me that some of these tables may have more than
one entry with the same LCS #. Will that prohibit me from using LCS# as the
primary key?

It certainly will. A Primary Key is by definition unique.

Example: If you had the following data

LCS# Color
3112 Green
3112 Red
3112 Blue

and I asked you "What color is LCS# 3112? Change that record to
Brown."

you (and Access) would be stuck.


John W. Vinson[MVP]

 

[Nathan C via AccessMonster.com]

Mr. Vinson,

Thank you for your post. I see the situation clearly. As a result,
referring back my original question, is there any way then for a query to be
run to fill in the CLD # based upon the related LCS #?

Perhaps had I given a better description initially, that would have helped.

1. We have contracts with vendors, including multiple contracts (in
different areas) with the same vendor.

2. Contracts may have payments associated with them. If they have a payment
they receive an LCS # which we assign internally.

3. A new system in our accounting department has its own numbering system.
They track everything by their "CLD#" in their own system. As a result,
there will always be a one-to-one relationship between any LCS# and the CLD#.

4. Now is where the twist comes in. We have documents for each contract.
And we have instances where more than one document may pertain to the same
LCS #.
5. The Documents table is related to the Contracts table in a one (Contracts)
to many (Documents) relationship.

So you have a contract. And multiple documents may have the same LCS #.

And the bull's eye is to run a query which goes to the documents subtable,
compares it with a list I can upload from an Excel Spreadsheet, and say:
"Where LCS = <value1> in fieldLCS#, place <value2> in fieldCLD#. This would
need to be done for some 2,000 values.

Any thoughts?

Thanks,
Nathan