?
Solved

UPDATE query

Posted on 2006-03-23
26
Medium Priority
?
395 Views
Last Modified: 2008-03-04
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.
0
Comment
Question by:Harvester_CZ
  • 14
  • 6
  • 6
26 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 16268383
Hi Harvester_CZ,
Something like:

** BACK UP FIRST PLEASE **

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

** BACK UP FIRST PLEASE **

Dave :-)
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16268533
(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?
0
 
LVL 34

Accepted Solution

by:
flavo earned 150 total points
ID: 16268615
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
Dave
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16268636
Ok, it goes further, however I get error that "operation must use an updateable query" (so translated from Czech lang.)
0
 
LVL 6

Expert Comment

by:bknouse
ID: 16268652
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')).

Brent
0
 
LVL 34

Expert Comment

by:flavo
ID: 16268665
Here's a list of words to stay clear of:
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/kb/286335/

List of reserved words in Access 2000
http://support.microsoft.com/kb/q209187/

Reserved words in Microsoft Access 97
http://support.microsoft.com/kb/q109312/

A workaround is to wrap them in [] (which I did), still best to keep clear of them to begin with
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16268669
It should not matter as long as they are in [] brackets...but I'll try..
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16268709
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....
0
 
LVL 6

Expert Comment

by:bknouse
ID: 16268723
What is the primary key on the table year_table?

Brent
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16268728
For a version matter: I have MS Access 2003, Windows XP Pro.
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16268767
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.
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16268809
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?
0
 
LVL 6

Expert Comment

by:bknouse
ID: 16269167
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.

Brent
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16269345
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.
0
 
LVL 6

Expert Comment

by:bknouse
ID: 16269433
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 ";".
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16269521
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.
0
 
LVL 6

Expert Comment

by:bknouse
ID: 16269640
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.

Brent
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16277863
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] & "*"));
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16277979
I don't know but maybe it's better to see the database in real:

http://www.agriplant.cz/attendance.zip

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

0
 
LVL 34

Expert Comment

by:flavo
ID: 16278627
The query at_in_out doesn't work, the Date_ID field is all #Error (well for me it is)
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16278784
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
0
 
LVL 34

Expert Comment

by:flavo
ID: 16278802
ahh.. Didn't see that :P
0
 
LVL 34

Expert Comment

by:flavo
ID: 16278807
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
0
 
LVL 6

Assisted Solution

by:bknouse
bknouse earned 450 total points
ID: 16280281
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.

Brent
0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16280481
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..

0
 
LVL 1

Author Comment

by:Harvester_CZ
ID: 16280528
Oh, sorry, I've switched the accepted/assisted answer - hope it doesn't matter with the poitns..
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question