Solved

update query

Posted on 2009-07-03
10
165 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now