Technique for Updating Part Master

[Guest]

I'm faced with I hope is a rather routine problem. On a routine basis, we
receive updates from Suppliers that requires someone to update the PartMaster
(P/N, Description, UOM, List Price, Material Cost, etc.). How can I import
new data into an existing table thereby updating existing information (i.e.
Material Cost) and/or add new records as needed. The table design consists
of PartMaster and PartXReference. PartXReference contains the supplier's p/n
cross-referenced to our internal p/n. At the very minimum, our supplier's
will provide cost updates twice per year. I'd also like to use this for
importing product photos, engineering notes, etc.

The database table will have to continue to autonumber the separate ID
fields. I've found based on past experience that using the canned import
external data routine only screwed up the autonumbering sequencing when I was
adding new parts and prices to an existing table.

 

[Guest]

In what format are you receiveing the suppliers' data? If it is something
you can link to as a table, then update queries for both your tables would
work. If it is a file that for any reason can't be linked, I would import it
to a temporary table, then use the update queries. If it will always be an
update to existing records, it is pretty straight forward. If, on the other
hand, new data will be added, then you will need to do a row by row check to
see if the data (probabably PN) exists and either update or append.

 

[Guest]

The supplier data arrived as a text file/excel spreadsheet containing their
p/n, brief description, and my base price before discount. I've generated
the final price (my cost) and wish to update those records that are already
in my system with their updated material costs. I've already attempted
importing the text file into the "sandbox" database and created an inner join
between the dbo.PartXRef P/N field and the P/N field that the supplier
provided within their file. Unfortunately, I can only return a fraction of
the number of items that I need. My hope was that upon successfully
generating the relatinoship between the two files I could create a query that
would provide my with my p/n, suppliers p/n, updated price/cost.

Bottom line...it didn't work and I'm SLOWLY updating the individual records
one at a time. Please help...

 

[Guest]

The way you describe the approach that did not work seems okay. Why it
didn't, I could not say without the data in front of me. You say you did get
a fraction in. What was it about them that allowed them to be updated?

You could try a slightly different approach if you are good with VBA. You
could link directly to your Excel sheet as a table, then loop through the
rows in the table, find a match in the table to update, then do the update
there.

 

[Guest]

Unfortunately, I am not a VBA person. I've read about it, taken classes and
found that VB and VBA is a limitation of my capabilities and patience.

 

[Guest]

don't know what else to suggest. The only way I can think of without code:

Create the Query:
This should be an update query that updates the part master.
Create a macro:
Delete old data in the import table
Import the data into the table
Run the query to do the update

If you can send the table layout for your Part Master, and the layout of the
files you are importing, I can probably help you with a query that will work.