Query help needed

[JellyMan]

I am trying to do something with an Access SQL query that I have never
done. I can see how it SHOULD work in my head, but can't seem to make
it work in reality. I think I have it close, but my SQL syntax must be
off.

What I WANT to do:
I have two tables. One (tbl_hours_new) is an SQL linked table through
ODBC, which reads just like any other table in Access. The second
(tbl_hours_Palm 1) is just a regular table. I want to be able to run a
single query that will read the data in the first table and update (or
replace) the information is the second table based on the id number
(which will be identical).

Here is my current SQL statement:
UPDATE tbl_hours_new SET
`Date` = (Select `Date` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
Employee = (Select `Employee` from tbl_hours_new where
tbl_hours_new.i d = tbl_hours_Palm1 .id),
Job = (Select `Job` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
Hours = (Select `Hours` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
PalmID = (Select `PalmID` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id)

WHERE exists (Select id from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id);
Can anyone tell me where I have gone wrong?

Andrew

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're updating the table tbl_hours_new w/ it's own data. You say you
want to update/replace data in the "second table." From the context of
that paragraph I'm assuming that you want to update tbl_hours_Palm1 (the
second table you mentioned) w/ data from tbl_hours_new (the first table
you mentioned). If so then try this (these queries will only update
current data. It will not add new data):

UPDATE tbl_hours_Palm1
SET [Date] = (Select [Date] from tbl_hours_new
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
Employee = (Select Employee from tbl_hours_new
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
Job = (Select Job from tbl_hours_new
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
Hours = (Select Hours from tbl_hours_new
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
PalmID = (Select PalmID from tbl_hours_new
where tbl_hours_new.i d = tbl_hours_Palm1 .id)

WHERE exists (Select * from tbl_hours_new
where tbl_hours_new.i d = tbl_hours_Palm1 .id);

If you want to update tbl_hours_new w/ data from tbl_hours_Palm1 :

UPDATE tbl_hours_new
SET [Date] = (Select [Date] from tbl_hours_Palm1
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
Employee = (Select Employee from tbl_hours_Palm1
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
Job = (Select Job from tbl_hours_Palm1
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
Hours = (Select Hours from tbl_hours_Palm1
where tbl_hours_new.i d = tbl_hours_Palm1 .id),
PalmID = (Select PalmID from tbl_hours_Palm1
where tbl_hours_new.i d = tbl_hours_Palm1 .id)

WHERE exists (Select * from tbl_hours_Palm1
where tbl_hours_new.i d = tbl_hours_Palm1 .id);

Note: In JET SQL the column name delimiter is a square bracket and not
a single quote. In standard SQL the column name delimiter is a double
quote.

If you want to add new data try this:

INSERT INTO <target table> ([Date], Employee, Job, Hours, PalmID)
SELECT [Date], Employee, Job, Hours, PalmID
FROM <source table> As S LEFT JOIN <target table> As T
ON S.id = T.id
WHERE T.id IS NULL

Replace <target table> w/ the name of the table you want to insert data
into, and replace <source table> w/ the name of the table you want to
get the data from.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnZ9noechKq OuFEgEQKfbACgyl dj6aaFWhFJOA8ue CBmnmqQmp8AoPYU
dwE+V6RVxdRe2uU jBKWz5xD0
=lZm4
-----END PGP SIGNATURE-----
JellyMan wrote:

I am trying to do something with an Access SQL query that I have never
done. I can see how it SHOULD work in my head, but can't seem to make
it work in reality. I think I have it close, but my SQL syntax must be
off.

What I WANT to do:
I have two tables. One (tbl_hours_new) is an SQL linked table through
ODBC, which reads just like any other table in Access. The second
(tbl_hours_Palm 1) is just a regular table. I want to be able to run a
single query that will read the data in the first table and update (or
replace) the information is the second table based on the id number
(which will be identical).

Here is my current SQL statement:
UPDATE tbl_hours_new SET
`Date` = (Select `Date` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
Employee = (Select `Employee` from tbl_hours_new where
tbl_hours_new.i d = tbl_hours_Palm1 .id),
Job = (Select `Job` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
Hours = (Select `Hours` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id),
PalmID = (Select `PalmID` from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id)

WHERE exists (Select id from tbl_hours_new where tbl_hours_new.i d =
tbl_hours_Palm1 .id);

Can anyone tell me where I have gone wrong?