UPDATE query

I need to create a UPDATE query eihter as a stored query or an RUNSQL string.

It's a attendance system:
I have a select query that shows ceratin records for date_id, in, out, employee_id.
I have a table with existing records of the same structure.
I need to update the table with records from the query where the date_id is the key.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Harvester_CZ,
Something like:


        Set in = b.in,
        out = b.out
FROM tblTableToUpdate a INNER JOIN otherTable b ON a.Date_ID = b.DateID


Dave :-)
Harvester_CZAuthor Commented:
(year_table - to be updated by at_in_out)
Ok, so I did:

UPDATE year_table
SET year_table.number = at_in_out.date_id, year_table.in = at_in_out.in, year_table.out = at_in_out.out, year_table.employee_id = at_in_out.employee_id
FROM year_table INNER JOIN at_in_out ON at_in_out.date_id = year_table.number;

and I get a syntax error (missing operator)...

What's wrong?
Not sure why it didn't work, should have worked in SQL Server I'm sure.

This should work though:
UPDATE  year_table y INNER JOIN at_in_out t ON t.date_id = y.number
SET y.[number] = t.date_id,
       y.[in] = t.[in],
       y.[out] = t.[out],
      y.employee_id = t.employee_id

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Harvester_CZAuthor Commented:
Ok, it goes further, however I get error that "operation must use an updateable query" (so translated from Czech lang.)
Try renaming the columns ESPECIALLY the column "IN".  It is best to avoid using RESERVED WORDS as column names and IN is used in SQL as a shortcut to ORing many conditions (e.g. IN('TX', 'WA')).

Here's a list of words to stay clear of:
List of Microsoft Jet 4.0 reserved words

List of reserved words in Access 2002 and Access 2003

List of reserved words in Access 2000

Reserved words in Microsoft Access 97

A workaround is to wrap them in [] (which I did), still best to keep clear of them to begin with
Harvester_CZAuthor Commented:
It should not matter as long as they are in [] brackets...but I'll try..
Harvester_CZAuthor Commented:
yes, I made:

UPDATE  year_table y INNER JOIN at_in_out_go t ON t.date_id = y.number
SET y.[number] = t.date_id,
       y.[go_in] = t.[go_in],
       y.[go_out] = t.[go_out],
      y.employee_id = t.employee_id

and still the same, so it's not the reserved words issue. I am aware of the reserved words however while the brackets works....
What is the primary key on the table year_table?

Harvester_CZAuthor Commented:
For a version matter: I have MS Access 2003, Windows XP Pro.
Harvester_CZAuthor Commented:
It doesn't have a primary key as the purpose of the table is to be a dummy table just to flush the data there, update, use and delete.
Harvester_CZAuthor Commented:
I tried to make a primary key at year_table.number - but no improvement.

The year.table.number is actually a number of the day in a year so it is unique thus can be a unique primary key but does it help?
I'm sorry...I need to know about the primary key in the table at_in_out_go.  

An alternative SQL would be:

UPDATE  year_table y
SET (y.[go_in], y.[go_out], y.[employee_id]) =
(SELECT [go_in], [go_out]
FROM at_in_out_go
WHERE [date_id] = y.[number])

I left off setting the y.number column since it is the join condition and would already have the t.date_id value.  
You could have a problem if the at_in_out_go table has more than 1 row for the same date_id value.  This would render the original (and this version) not updateable.  This is reasonable, since the query doesn't want to decide which of the multiple rows to use as the update values.

Harvester_CZAuthor Commented:
at_in_out_go is not a table, it's a select query. What I need is to update the year_table by/with selected values from the at_in_out_go query.

I tried the last sql, but

- I got syntax error with cursor after "SET (" - maybe it's too much for a JetSQL?
- shouldn't it be (SELECT [go_in], [go_out],[employee_id]...

about  y.number - yes, I thought so, makes sense.
about more than 1 row for the same date_id value - no, it's unique.
Try changing the SQL:

UPDATE  year_table y INNER JOIN (SELECT STATEMENT OF QUERY) t ON t.date_id = y.number
SET y.[go_in] = t.[go_in],
       y.[go_out] = t.[go_out],
      y.employee_id = t.employee_id

That is, cut and paste the SQL statement of the query and place "(" ")" around it, keep the alias of t.  Be sure to remove the ";".
Harvester_CZAuthor Commented:
Aj, still I get the "operation must use an updateable query"

UPDATE  year_table y
INNER JOIN (SELECT at_in_out.date_id, at_in_out.[in] AS go_in, at_in_out.out AS go_out, at_in_out.employee_id

FROM at_in_out) t ON t.date_id = y.number
SET y.[go_in] = t.[go_in],
       y.[go_out] = t.[go_out],
      y.employee_id = t.employee_id;

There again appears the reserved IN as I only created a view for this, but I don't guess it's the problem.
Please verify that date_id is truly unique in the query.  Create a dummy table from the query.  Then try to set the date_id field as a primary key in the dummy table.  If it can't, then you know it is not unique.

Harvester_CZAuthor Commented:
Yes, it is unique, look, here are the data:

date_id      go_in          go_out                            employee_id
58      28.2.2006 6:26:38      28.2.2006 18:00:13      59
59      1.3.2006 6:51:20      1.3.2006 15:30:20               59
60      2.3.2006 6:54:23      2.3.2006 15:42:59             59
61      3.3.2006 5:57:14      3.3.2006 16:33:46             59
65      7.3.2006 6:29:32      7.3.2006 17:12:04             59
67      9.3.2006 6:55:32      9.3.2006 16:07:34              59
68      10.3.2006 6:50:10  10.3.2006 15:32:55      59
71      13.3.2006 6:25:41  13.3.2006 16:01:43      59
72      14.3.2006 6:37:40        14.3.2006 15:42:50      59
73      15.3.2006 6:24:43        15.3.2006 16:10:32      59
74      16.3.2006 6:29:12        16.3.2006 16:04:44      59
75      17.3.2006 6:22:50        17.3.2006 16:31:05      59
78      20.3.2006 6:30:03        20.3.2006 16:02:51      59
79      21.3.2006 6:30:38        21.3.2006 15:46:29      59
80      22.3.2006 6:26:30        22.3.2006 15:44:36      59
81      23.3.2006 6:29:49        23.3.2006 15:04:35      59

I made a dummy table though and it passed.

The only thing that might be involved is, that the original at_in_out query (before we came to at_in_out_go - for the reserverd words reason) has a parameter for an employee name which, if omitted, gives data where the date_id is NOT unique and ACCESS would for some reason verify the conditions of the query regardless on the parameter limiting. But this case shall never happen as there will be always some employee_id selected. The at_in_out is:

SELECT at_pairs_in.at_pair_id, at_pairs_in.FirstOfat_time AS [in], at_pairs_out.FirstOfat_time AS out, at_pairs_in.at_work_desc, [Employees - All].employee_id, DateDiff("d","1.1." & Year(Now()),[in]) AS date_id
FROM (at_pairs_in INNER JOIN at_pairs_out ON at_pairs_in.at_pair_id = at_pairs_out.at_pair_id) INNER JOIN [Employees - All] ON at_pairs_out.at_employee_id = [Employees - All].employee_id
WHERE ((([Employees - All].Employee) Like "*" & [Employee] & "*"));
Harvester_CZAuthor Commented:
I don't know but maybe it's better to see the database in real:


The query we are talking about is Dotaz1
As a parameter for the Employee name (shall look like "Zamestnanec") enter "neumann".

The query at_in_out doesn't work, the Date_ID field is all #Error (well for me it is)
Harvester_CZAuthor Commented:
Hm, that is strange, it is date_id: DateDiff("d";"1.1." & Year(Now());[in]) which shouldn't be a problem... oh maybe - the date format is different for your country.

Well, I tried american 1/1/ but it still works for me..

Let's compare the versions - I use Access 2003
However the function DateDiff should work.

The table at_up is the source for [in] and contains data like:

at_id      at_employee_id      at_time      at_work_id      at_manual_edit      at_pair_id
383      50      27.2.2006 15:30:13      17      ne      142
385      59      27.2.2006 16:33:30      17      ne      327
391      59      28.2.2006 6:26:38       2      ne      391
ahh.. Didn't see that :P
I have 2003 going, but regional settings are set to US or UK.. Just depends on what I'm working on.. I'll take another look
Sorry for the delay, as I had to get the db converted to Access 2000.  

Unfortunately, I have some bad news for you.  There is no way Access is going to run this update.  The query you referred to is also based on a query, of which is a parameterized left join.  Because of the update query using a query as a data source, which is also a query (2 of them), each of which is based on queries (these are finally based on tables).  Access has no way to know that the update values will be unique and as such will not run the query.  Even if the data was unique, Access won't execute this query.  In addition, your example with "neumann" works but most of the other employees have "suspect" data, that is causing duplicate date_id values.  For example running the "at_in_out" query for "Muzikantová Věra" gives the following results:

at_pair_id      in      out      at_work_desc      employee_id      date_id
393      2/28/2006 11:07:58 AM      2/28/2006 2:30:40 PM      Úklid      57      58
401      3/1/2006 10:21:31 AM      3/1/2006 3:21:31 PM                      Úklid         57      59
408      3/2/2006 10:17:13 AM      3/2/2006 2:30:49 PM                      Úklid      57      60
421      3/3/2006 10:19:56 AM      3/6/2006 2:33:54 PM                      Úklid      57      61
459      3/8/2006 10:20:52 AM      3/8/2006 2:36:57 PM                      Úklid      57      66
476      3/9/2006 10:17:53 AM      3/9/2006 2:31:17 PM                      Úklid      57      67
483      3/10/2006 10:18:31 AM      3/10/2006 2:32:03 PM      Úklid      57      68
499      3/13/2006 10:24:45 AM      3/13/2006 2:32:55 PM      Úklid      57      71
499      3/13/2006 10:24:45 AM      3/13/2006 2:33:23 PM      Úklid      57      71
510      3/14/2006 10:17:08 AM      3/14/2006 2:32:04 PM      Úklid      57      72
510      3/14/2006 10:17:08 AM      3/14/2006 2:32:12 PM      Úklid      57      72
520      3/15/2006 10:20:56 AM      3/15/2006 2:32:59 PM      Úklid      57      73
528      3/16/2006 10:21:59 AM      3/16/2006 2:33:48 PM      Úklid      57      74
542      3/17/2006 10:20:40 AM      3/17/2006 2:33:49 PM      Úklid      57      75
552      3/20/2006 10:30:25 AM      3/20/2006 2:34:43 PM      Úklid      57      78
562      3/21/2006 10:18:01 AM      3/21/2006 2:33:07 PM      Úklid      57      79

Note the duplicate values for date_id 71 and 72.  You are going to have to approach this from another direction.  Perhaps using a macro to perform several steps, such as building a temp table (with unique data) then run a query using the temp table to update from.  You could also do this in code behind a form or in a module.

Harvester_CZAuthor Commented:
Hmm, interesting. I didn't know about the obsolete data for another emplyees - that's actually caused by improper inputs..or, what actually is right - a person comes 2 times in one day - and that would generate two regular and same date_id records - hmm I must first fix this.

That actually brings the initial problem somewhere else so I suppose it's finished for now :-)

So thank you both, guys, I'll distribute the points..

Harvester_CZAuthor Commented:
Oh, sorry, I've switched the accepted/assisted answer - hope it doesn't matter with the poitns..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.