Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

update query

Posted on 2009-07-03
10
Medium Priority
?
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 143

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

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

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

610 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