If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
update query
Dear Team,
I have created 2 queries which contain 2 difference result set, in fact this 2 queries from same table and this table linked from SQL database. I already lable 1st query as A and 2nd query as B, each query only have 4 column, as: A.Column1 (Site A) A.Cloumn2 (SN) A.Column3 (Date) A.Column4 (Value) B.Column1 (Site B) B.Column2 (SN) B.Column3 (Date) B.Column4 (Value) I wish to update the B.Column2 = A.Column2, but I have only 1 same creteria which is date,however the date can be repeated. Please advise on how to create a scipt / SQL statement to update the result set, I have to update row by row manually at this moment, Example of result set: Query A: Site SN Date Value A 001 01/07/2009 1000 A 002 01/07/2009 2000 A 003 01/07/2009 1500 A 004 01/07/2009 3400 A 005 01/07/2009 4500 Query B: Site SN Date Value B 021 01/07/2009 4000 B 025 01/07/2009 7000 B 029 01/07/2009 8500 B 030 01/07/2009 4400 B 035 01/07/2009 5500 Expected result set: Query B: Site SN Date Value B 001 01/07/2009 4000 B 002 01/07/2009 7000 B 003 01/07/2009 8500 B 004 01/07/2009 4400 B 005 01/07/2009 5500 Anybody's advise and assistance is much appreciated. Thanks & regards, fresher |
#2
|
|||
|
|||
update query
Is SN unique in your table? Or is it a least unique for any specific date?
It appears that you are matching the smallest value in query A with smallest value in query B and then the next smallest with the next smallest, etc. Is that true? I'm not sure you can do it directly with one update query. You would probably need to create a temporary table and then use that as the source for an update. It would help if you posted the SQL of your two current queries. And even then this is going to be pretty complex to solve and slow for any large set of records. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === fresher wrote: Dear Team, I have created 2 queries which contain 2 difference result set, in fact this 2 queries from same table and this table linked from SQL database. I already lable 1st query as A and 2nd query as B, each query only have 4 column, as: A.Column1 (Site A) A.Cloumn2 (SN) A.Column3 (Date) A.Column4 (Value) B.Column1 (Site B) B.Column2 (SN) B.Column3 (Date) B.Column4 (Value) I wish to update the B.Column2 = A.Column2, but I have only 1 same creteria which is date,however the date can be repeated. Please advise on how to create a scipt / SQL statement to update the result set, I have to update row by row manually at this moment, Example of result set: Query A: Site SN Date Value A 001 01/07/2009 1000 A 002 01/07/2009 2000 A 003 01/07/2009 1500 A 004 01/07/2009 3400 A 005 01/07/2009 4500 Query B: Site SN Date Value B 021 01/07/2009 4000 B 025 01/07/2009 7000 B 029 01/07/2009 8500 B 030 01/07/2009 4400 B 035 01/07/2009 5500 Expected result set: Query B: Site SN Date Value B 001 01/07/2009 4000 B 002 01/07/2009 7000 B 003 01/07/2009 8500 B 004 01/07/2009 4400 B 005 01/07/2009 5500 Anybody's advise and assistance is much appreciated. Thanks & regards, fresher |
#3
|
|||
|
|||
update query
Hi John Sprence,
Thanks for your advise, SN not the unique key and not specific at any date, Wish to share the way I handle it now after think properly, I create a table C table, 2 column, 1st column as autonumber and 2nd column as text field, beside that, i add 1 more column as column 5 for query A & query B. step 1: I copy query A's SN and paste into table C's 2nd column, step 2: update query A, set A.column5 = tableC.colum1 (autonum) where A. column2 (SN) = tableC.column2(SN) I repeat step 1 & 2 for query B, so that at the end column5 for query A & B have a set of autonum (1,2,3,4 ...total row num) Then, I update query B, set B.column2(SN) = A.column2(SN) where B.column5 = A. column5 I can get the result as I want, but not sure any better solution on this issue, please advise. Thanks & regards, fresher John Spencer wrote: Is SN unique in your table? Or is it a least unique for any specific date? It appears that you are matching the smallest value in query A with smallest value in query B and then the next smallest with the next smallest, etc. Is that true? I'm not sure you can do it directly with one update query. You would probably need to create a temporary table and then use that as the source for an update. It would help if you posted the SQL of your two current queries. And even then this is going to be pretty complex to solve and slow for any large set of records. '================================================ ==== John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================ ==== Dear Team, [quoted text clipped - 52 lines] Thanks & regards, fresher -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200907/1 |
Thread Tools | |
Display Modes | |
|
|