A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

update query



 
 
Thread Tools Display Modes
  #1  
Old July 5th, 2009, 11:58 AM posted to microsoft.public.access.queries
fresher
external usenet poster
 
Posts: 1
Default 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  
Old July 5th, 2009, 06:23 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 2,364
Default 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  
Old July 10th, 2009, 05:03 AM posted to microsoft.public.access.queries
fresher via AccessMonster.com
external usenet poster
 
Posts: 1
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.