Go to the absoluteposition in a table via a query

[dirkg]

Hi all,<br><br>I'm wondering what the best way is to access a certain record in a table via a query.<br>I need this because I have to make for every record in a certain table a lot of calculations. I know I can use a recordset to access every single recordset but a loaded recordset takes space in the memory and I need all the memory there is to perform the other calculations. Therefore I would like to retrieve the data from that table record by record by just making a counter run and then access the record with the number of the counter. I can do this with the following query where I change the last TOP-value by the counter: <br>SELECT&nbsp;&nbsp;top 1<br>Field1<br>FROM

where Field1 not in (select top 23 Field1 from [Table1]);<br><br>I was wondering if this is the best way to don it? Can't you inser in a query-statement something like absoluteposition which you can use with recordsets? Is there a possibility to put this in a dlookup-function (I only need one field from the table)? <br>So in short: what is the best way to do this in terms of performance and memory-usage?<br><br>Thank you very much in advance for your advise!<br><br><br>Greetings,<br><br>Dirk<br><br><A HREF="mailto:dirk.news@yucom.be">dirk.news@yucom.be</A><br><br>

 

[DougP]

The best way to &quot;Access&quot; a relational database is by a value in a field&quot;<br><br> Dim db as database, rst as recordset, SQL as string<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dim Somevalue as variant<br> Set db = CurrentDb<br> ' SQL string.<br> SQL = &quot;SELECT * FROM Orders WHERE OrderDate &gt;= #1-1-95#;&quot;<br> Set rst = db.OpenRecordset(SQL)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Somevalue = rst!Orderdate<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.close<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db.close<br>&nbsp;&nbsp;<br>SQL (Structured Query Language) is what Access uses it’s ANSI SQL 89<br>You just find what you want. Forget pointer and record numbers.<br>SQL is very fast. Read about it in Help<br><br>A relational database has no position number like dBase or others.<br><br>OK <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[dirkg]

yes I know but the problem is that I don't know the value of the field since it's that value that I have to retrieve.<br>Here's what I want:<br><br>I make a query which collects a number of suppliers from my database of suppliers which fullfil certain criteria (that's why I use the query). Now I want to do some calculations for every supplier in that list (but I can't select them the way you told me because there is no unique criterium for these suppliers except for the supplier ID which is exactly what I need) I hope you still follow me...<br><br>Now since that list of suppliers can be quite large (about 3000 of them) I woulden't like to make a recordset for this list to run through all these suppliers since I guess this would take too much memory. That's why I want to access my list just by telling which record has to be accessed. Does this sound a bit logic?<br><br>Greetings,<br><br>Dirk<br>

 

[DougP]

Yes, but don't asume 3000 records is too much until you try it. I would test it first.<br>Now if you have a Pentium 133 with 32 meg of memory then yes you need a bigger box.<br><br>I always do anything in Access in steps. <br>Create query to return your records. See how long it takes.<br>Is there nothing you can search on or use as a criteria like a date from another table?<br><br>I would then create a simple report that has a totals field in the Footer and let it go and see what happens.<br>If it takes longer than you want hit &quot;Ctrl-Break&quot;<br><br><br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.