Solved

update query

Posted on 2009-07-03
10
166 Views
Last Modified: 2012-05-07
I have two tables: concediu_zile and  V03. I want a query to update table V03 (please see the example). Example: For CNP 112 because I have in zile concediu(table: concediu_zile) the valu1-jul-2009 the query must put "1" in the column 1_jul(table V03) for CNP 112 and so on. Please see the example:

example

concediu_zile                                                V03
CNP      zileconcediu                                       CNP       1_jul    2_jul     3_jul    
112       1-jul-2009                                          112                                
112       2-jul-2009                                          114                                                    
114       2-jul-2009                                          115                                  
114       3-jul-2009
115       2-jul-2009


V03 (after I run the query)
CNP               1_jul         2_jul                3_jul        
112                  1              1
114                                   1                     1                
115                                  1

Thank you
0
Comment
Question by:Qw M
  • 6
  • 4
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24771099
what about this:
UPDATE V03
   SET [1_jul] = DMAX(1, "concediu_zile", "CNP=" & CNP & " AND zileconcediu = '1-jul-2009' " )
     , [2_jul] = DMAX(1, "concediu_zile", "CNP=" & CNP & " AND zileconcediu = '2-jul-2009' " )
     , [3_jul] = DMAX(1, "concediu_zile", "CNP=" & CNP & " AND zileconcediu = '3-jul-2009' " )

Open in new window

0
 

Author Comment

by:Qw M
ID: 24771247
When I run the query I get .... didn't update the fils due to a type conversion failture... 1_jul, 2_jul, 3_jul must be text or numeric filds. The filds were text but now I changed the filds and are number with filds size integer.
Please help me...
0
 

Author Comment

by:Qw M
ID: 24771301
I think it is the CNP because the CNP fild is text.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Qw M
ID: 24771375
no... is it not the CNP fild..... Please help me....
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24771389
ok, let's see:
UPDATE V03
   SET [1_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = ""1-jul-2009"" " )
     , [2_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = ""2-jul-2009"" " )
     , [3_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = ""3-jul-2009"" " )

Open in new window

0
 

Author Comment

by:Qw M
ID: 24771420
I get the same error .....didn't update the fils due to a type conversion failture...    1_jul, 2_jul, 3_jul must be text or numeric filds? I changed form number to text and I get the same mesage........    didn't update the fils due to a type conversion failture... And the CNP must be a fild text....

Please hepl me.
Thank you
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24771440
what data type is zileconcediu ?
0
 

Author Comment

by:Qw M
ID: 24771457
date/time with format medium date
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24771496
ok
UPDATE V03
   SET [1_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = #2009-06-01# " )
     , [2_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = #2009-06-02# " )
     , [3_jul] = DMAX(1, "concediu_zile", "CNP=""" & CNP & """ AND zileconcediu = #2009-06-03# " )

Open in new window

0
 

Author Comment

by:Qw M
ID: 24771723
It's working. Thank you so much!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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