Rob wrote:The best way to figure this out is to open your database in Access and use> Hi all,
> I'm trying to update one table in an access db from another table in
> the same db. I'm sure it must be possible to do it in one go in asp.
> I'm wanting to update table1 which already has values in its date
> field and update the user, shift and ot with values from table2.
> Table2 is basically a teamplate of data with 42 rows and when
> updating into table1 i'd need to specify the date range of the rows
> to update.
the Query Builder to create your query. Here's a step-by-step:
1. In the Queries tab, click the "Create query in Design View"
2. Select your two tables and close the dialog
3. Click and drag the date field from one table field list to the other to
create the join
4. Go to Query|Update Query in the menu to change it to an update query
I like to use table aliases so:
1. Right-click table1 and select Properties if the Properties
dialog is not already visible
2. Change the alias from "table1" to d (for destination)
3. Click into the Source box just to maqke sure your
4. Highlight table2 and set its alias to s (for source)
5. Click and drag the date field from the table1 (or d) list into the Field
row of the first column of the grid.
6. In the Update To row, type "s.[user]" (without the quotes of course), or
"table2.[user]" if you decided not to use table aliases
7. Repeat for the rest of the columns that need updating
8. Switch to SQL View to see what it looks like. Something like this:
UPDATE Table1 AS d INNER JOIN table2 AS s ON d.date = s.date
SET d.[user] = [s].[user], d.shift = [s].[shift];
or this if you didn't use table aliases:
UPDATE Table1 INNER JOIN table2 ON Table1.date = Table2.date
SET Table1.[user] = [Table2].[user], Table1.shift = [Table2].[shift];
My advice is to ALWAYS use the query builder to create and test your queries
before attempting to run them from asp (or any other client application)
Incidently, this is one of the differences between JetSQL and Transact-SQL
(SQL Server). The corresponding Transact-SQL query would look like this:
SET d.[user] = [s].[user], d.shift = [s].[shift]
FROM Table1 AS d INNER JOIN table2 AS s ON d.date = s.date
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the