Solved

update query

Posted on 2009-07-03
10
170 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
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
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…

717 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