Results 1 to 2 of 2
2004-04-26, 20:37 #1
- Join Date
- Feb 2004
- Burgas, Bulgaria
- Thanked 0 Times in 0 Posts
Append during updating (Access 2000)
I have to update the field DDU in the table Products from the field DDU in the table Preisliste.
Both tables contain the field Code.
The function works very good and updates all the prices in the table Products with the new prices from the table
However, there are some new code numbers in the table Preisliste.Can i add on some line in my function
to append all the codes that do not exist in the tables Products,and also append their DDU price.
Also,is it possible to append the whole row, with the ProductName etx for the non existing products?
Below is my function
Public Function FncUpdateDDU()
'This function copies the DDU prices from the table Preisliste into the DDU prices of the table Products
' and by dividing to 100 converts the price to price for liter instead of price for 100 ltrs.
Dim sqlUpdateDDU As String
sqlUpdateDDU = " UPDATE products INNER JOIN Preisliste ON products.code = Preisliste.Code SET products.ddu =
2004-04-26, 21:22 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Append during updating (Access 2000)
Try the following, first on a copy of the database.
- Activate the Queries section of the database window.
- Click New.
- Select "Find Unmatched Wizard" and click OK.
- In the first step of the Wizard, select Preisliste, then click Next.
- In the second step, select Products, then click Next.
- In the third step, select the field Code on both sides, then click the <=> button, and click Next.
- In the fourth step, add all other fields you want to append, but not an AutoNumber field if there is one. Then click Next.
- In the fifth and final step, specify a name for the query, then click Finish.
- Check that the query returns the correct records.
- Switch to design view.
- Select Query | Append Query, and specify Products as table to append to, then click OK.
- Make sure that the fields from Preisliste are appended to the correct fields in Products.
- Edit the DDU field to put / 100 after it (the division by 100 you require.)
- Clear "Append to" for the field from Products, with the Is Null criteria.
If you still want to do this in code, select View | SQL. You will see the SQL instruction you need. You will have to remove the line ends, then you can copy the SQL statement into the Visual Basic Editor.