?
Solved

update query

Posted on 2009-07-03
10
Medium Priority
?
172 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 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