UPDATE query

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 :-)

(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?

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')).

Brent

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

It should not matter as long as they are in [] brackets...but I'll try..

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?

Brent

For a version matter: I have MS Access 2003, Windows XP Pro.

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.

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.

Brent

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 ";".

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.

Brent

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] & "*"));

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".

The query at_in_out doesn't work, the Date_ID field is all #Error (well for me it is)

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

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..

Oh, sorry, I've switched the accepted/assisted answer - hope it doesn't matter with the poitns..