Hi, re-posted! See below.
Hi, thanks for that- it worked perfectly. There is just one snag however and I didn't think of it until I was testing. Many of the suppliers use specific currencies so the information I need copied from the Supplier tab is actually the Local Cost. When I view the SupplierList table, Local Cost doesn't appear as a column. Is it just a calculation based on exchange rates for information or does it actually exist somewhere within the database? Your help once again would be much appreciated.
T.
"c> Hi Tara - backup your db first....
> first run
> select * from supplierlist order by cost asc
> -- this will show you the contents in the supplierlist table first listing
> items with zero cost (or the lowest cost) - fix zero cost entries first by
> editing item properties and setting the correct cost in the supplier tab > > then run
> update item
> set item.cost = supplierlist.cost
> from item inner join supplierlist on item.id = supplierlist.itemid
> -- the inner join will only update items that are both in the item table and
> supplierlist table
>
> to see items that are not in the supplier list table run
> select itemlookupcode, description
> from item
> where id not in (select itemid from supplierlist)
>
> Hope this helps....
>
> "Tara" wrote:
>
> > Hi, I've a customer who has a number of items in their HQ database at zero
> > cost. However, for these items there is a cost price detailed on the Supplier
> > tab. Items carried only have one supplier. They'd like to get this cost
> > copied into the cost price field on the General tab and because we're talking
> > about a few thousand items, this isn't something they want to do by hand! I'm
> > familiar to a point with SQL but because we're talking two seperate tables, I
> > really wouldn't be sure where to start with the syntax. Could someone please
> > point me in the right direction? Or if there's an easier way using Wizards
> > etc, even better! Thanks!