Changing Suppliers for Many Items

Is there a quick and accurate way to change suppliers for groups of items? Such as making all items in one Department or Department Code to be supplied by a specific distributor?

Currently, most of my items do NOT have supplier which makes a Purchase Order impossible.

Thanks.

Reply to
Paul
Loading thread data ...

You could use a query.

ALWAYS BACKUP FIRST.

Something like:

UPDATE Item SET SupplierID = X WHERE DepartmentID = Y This will not update the SupplierList table.

In another thread, this query was recommended for updating the SupplierList table: INSERT INTO SupplierList ( SupplierID, ItemID, ReorderNumber) SELECT Supplier.ID, Item.ID, Item.ItemLookupCode FROM Item, Supplier WHERE Supplier.Code = ''

You could use: SELECT * FROM Supplier - to find the Supplier IDs

SELECT * from Department - to find the Department IDs

ALWAYS BACKUP FIRST.

Tom

Reply to
Terrible Tom

You run the risk that not all the items in a specific department belong to the same supplier and you'll screw up your data; you're probably better off adding the supplier manually; or maybe jot down a list of hot sellers without a supplier assigned and then using a query that will limit the update to just those items...for example, there's five items you want to update, they have the following item lookup codes: 234532, 123412, 346345, 2345234, and

3453244.

first backup your database the query would be update item set supplierid = X where itemlookupcode in ('234532', '123412', '346345', '2345234', '3453244');

Use Tom's query to find the supplier ID and department ID to use; also consider running a SELECT query first to see which items will be affected by the UPDATE query. SELECT * from item where DepartmentID = Y

h> Is there a quick and accurate way to change suppliers for groups of items?

Reply to
convoluted

Is there a quick and accurate way to change suppliers for groups of items? Such as making all items in one Department or Department Code to be supplied by a specific distributor?

Currently, most of my items do NOT have supplier which makes a Purchase Order impossible.

Thanks.

Reply to
CptSoft

Thanks for the help by all.

After trying to do it on my own using the minimalist SQL knowledge that I have, I managed to add a Supplier to groups/departments of items without a problem. Then, when I tried to add those items into the SupplierList I added all items to EACH Supplier instead of just moving them to the ONE they were supposed to be listed as.

At the moment, I'm leaning towards Jeff's recommendation. I keep hoping for something easy like a cut and paste as you might in an Excel spreadsheet.

Paul

"Jeff @ Check Po> Paul,

_sold_ recently and start with them. You can modify the info by double clicking the ILC. Start with last week or 2, then work back for 2 weeks before that. You can also run the report for items that you have sold out of and you need it now. At the same time of selecting the vendor for the product, you can setup your mins and re-order points too.

Reply to
Paul

The only 'cut & paste' solution would be linked tables in MS Access. You can view/edit your RMS tables directly this way. THIS COULD BE WORSE THAN A BAD QUERY IF YOU MAKE A MISTAKE.

The functionality exists but I would advise against this practice for anybody and every reason. That said, I just tested it. I linked to my SupplierList table, made a single change, saved the change then checked RMS. Sure enough, the change made it through.

BE CAREFUL if you attempt this.

Tom

Reply to
Terrible Tom

I do quite a bit of bulk editing, like changing sale dates, suppliers, prices and costs using Access and it can work very well. Like you said, you have to be CAREFUL, but what a timesaver!

If there is any common thread to draw a query upon, you can update a large amount of records with a few clicks, compared to handling items one by one. It provides far more flexibility than using the built-in wizards. I create tables from supplier's catalogs and update supplier costs for a thousand items in a click. As suggested by others before, it is always a good idea to run a select query before running an action query to make sure you are getting the correct records.

But, it is definitely not recommended if you are not sure what you are doing, you can screw things up real fast.

Marc

Reply to
Marc

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.