Update a field from 1 through 10

  • Thread starter Thread starter megaranid
  • Start date Start date
M

megaranid

Hi,

I have 2 tables:

Table1
ID SS
1 111
2
3

Table2
ID SS FROM TO
4 111 1 3

And I want to Update field SS from Table1 where Table1.ID > Table2.FROM AND
Table1.ID <= Table2.TO

The result should be:

Table1
ID SS
1 111
2 111
3 111

The thing is that Table1 has 7498790 records and the Query that I'm doing is
extremely slow. Can you suggest anything else.

Thanks.
 
Try something like
UPDATE Table1
SET SS = DLookup("SS","Table2",Table1.ID & " BETWEEN FROM and TO")
WHERE SS Is Null;
 
Thanks.

But the query still slow and I think is getting stuck in an endless loop.
Can you suggest anything else, like VBA code.
 
You could try to create an array from table2 and then use a function to
return the proper SS value.

=== modFillArrayFromTable ===
Option Compare Database
Public arSS() As Variant
Function FillarSS()
'array = recordset.GetRows( Rows, Start, Fields )
Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT [SS], [FROM], [TO] FROM TABLE2 ORDER BY [FROM] DESC"
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
arSS = rs.GetRows
Set rs = Nothing
End Function

Function GetSS(intID As Integer) As Variant
On Error GoTo errGetSS
Dim intArrCount As Integer
intArrCount = UBound(arSS, 2)
For intArrCount = 0 To intArrCount
If intID >= arSS(1, intArrCount) Then
GetSS = arSS(0, intArrCount)
Exit For
End If

Next
exitGetSS:
Exit Function

errGetSS:
Select Case Err
Case 9 'out of range need to fill array
Call FillarSS
Resume
Case Else
Resume exitGetSS
End Select
End Function

Then you can create a query like:

UPDATE Table1 SET Table1.SS = GetSS([ID]);

BTW: "FROM" is not a good name for a field since it is a reserved word. I
don't care for other non-descriptive field names like TO and ID.
 
Again, Thank you very much.

The Update query is giving me the following error:

Compile.error in query expression 'GetSS([ID])'

That is error: <Message> in query expression <expression>. (Error 3075)

Thanks again for all you're help.
 
What are your data types? Your values look numeric.
--
Duane Hookom
Microsoft Access MVP


megaranid said:
Again, Thank you very much.

The Update query is giving me the following error:

Compile.error in query expression 'GetSS([ID])'

That is error: <Message> in query expression <expression>. (Error 3075)

Thanks again for all you're help.



megaranid said:
Hi,

I have 2 tables:

Table1
ID SS
1 111
2
3

Table2
ID SS FROM TO
4 111 1 3

And I want to Update field SS from Table1 where Table1.ID > Table2.FROM AND
Table1.ID <= Table2.TO

The result should be:

Table1
ID SS
1 111
2 111
3 111

The thing is that Table1 has 7498790 records and the Query that I'm doing is
extremely slow. Can you suggest anything else.

Thanks.
Click to expand...
Click to expand...
 
Thanks. I fix it.

Sorry to bother so much, but now the problem is that it updates everything
with the same SS. for example:

Before:

Table1
ID SS
1 111
2
3
4 222

After:

Table1
ID SS
1 111
2 111
3 111
4 111 -> its suppose to be 222
 
Back
Top