Update a field from 1 through 10

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

 

[Duane Hookom]

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

 

[megaranid]

Thanks.

But the query still slow and I think is getting stuck in an endless loop.
Can you suggest anything else, like VBA code.

 

[Duane Hookom]

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.

 

[megaranid]

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.

 

[Duane Hookom]

What are your data types? Your values look numeric.
--
Duane Hookom
Microsoft Access MVP

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.

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.

 

[megaranid]

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