update query

[fresher]

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

 

[John Spencer]

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 via AccessMonster.com]

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

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