Displaying a Set number of records

[CharlieT302]

Hi Folks

I would like my query to return only the first 100 records. It would be nice if it could also remove the remaining records.

Any Ideas?

 

[PWise]

select top 100 * from tablename

 

[CharlieT302]

Thats a good idea. However, as a protocol, the records must remain as random as possible. I can not have the program perform any type of sorting.

I can only use the first 100 in their original entered order.

 

[PWise]

if you table has a Primary Key

then try this

create a function

Function newguid(Primarykey)
Dim TypeLibb
    Set TypeLibb = CreateObject("Scriptlet.TypeLib")
    newguid = Left(TypeLibb.guid, 38)
End Function

and use this sql

Select top 100 * 
from tablename
order by newguid(Primarykey)

 

[CharlieT302]

I am not following what this function is doing. However, it looks as if it is ordering the records by the Primary Key? Is that correct? If so, I cannot use it. I can only take the first 100 (from the foreign table) as they were entered.

What about simply selecting the records beyond the first 100 and deleting them?

 

[jrbarnett]

Are you sure you actually want to delete the remaining records? They won't be available for further processing.

John

 

[PWise]

NO! It is not ordering the records by the Primarykey. It is ordering the records by a GUID created on the fly. The reson you have to pass the Primarykey is beacuse if you dont access will only run the function once and it will be the same Guid for all the records and will not limit it to 100 records.

Did you try it?

 

[CharlieT302]

jrbarnett,

Deleting the remaining records is fine because they can be easily extracted again with an existing query.

Pwise,

I am just sitting down to try it now, but am confused as to where I place the code.

 

[PWise]

Place the code in a module

try this optimized code

Function newguid(Primarykey)
    With CreateObject("Scriptlet.TypeLib")
       newguid = Left(TypeLibb.guid, 38)
    end with
End Function

 

[CharlieT302]

Pwise,

I see where the code is going. The problem is that, as I stated to jbarnett, this is a foreign table without a primary key value to use. I am getting an error message: Enter Parameter Value: Primary key

All I really need to do is to delete every record beyond 100. Is there an easy way of handling this? The code for this in Excel is fairly simple. I just can't seem to find an equivalent in Access.

 

[jrbarnett]

Hang on, you said that:
"this is a foreign table without a primary key", yet you also say you don't mind deleting records because they
"can easily be extracted again with an existing query"

Therefore I can conclude that this is some sort of temporary table built by your application.

If so, how to you determine which records get put in in the first place? there has to be some sort of record selection there to pull them from the source data.
Putting a primary key in this table, making this a foreign key relationship to the original data will make it far easier to carry out this task, as its a simple delete on a left join searching for null values.

John

 

[CharlieT302]

JBarnett,

To answer your question. Records are extracted from a foreign table and placed into a temporary table. Records are pulled from the last three months. We need to audit a predetermined number of records from the set; not all records.

Therefore, I would like to have an automated system to move to record 101, select it and all remaining records, and then delete them; leaving only the first 100.

 

[CharlieT302]

Thanks to all,

O.K. I think I have it. At first I couldn't get Pwise's solution to work. I ended up with a parameter value. I just got it working. I wasn't modifying the SQL statement behind the query correctly.

All I had to do was add
Select top 100 * to the beginning of the SQL statement that was already in place. However, it apparently was not necessary for me to reference the function Pwise had me create? Is there something I am missing?

Thanks for all the help.

 

[jrbarnett]

Without pwise's function, you will always get the first 100 records recorded from the query in the same order. The function adds a random sorting order to it.

John

 

[CharlieT302]

Hey,

I am sorry to ask this again. After reviewing the solution I have, I realize it is not exactly what I need. Basically, all I have done is create a top values query using SQL instead of the Top Values property. That is fine, and it displays the records I need.

However, I need the user to have the ability to enter the desired number of displayed records without opening the query's design view, which they will not be allowed to do.

I would like to provide the user with a field into which they could enter the number of desired records and then have the query filter for that number. Basically, I need to utilize a feature similiar to "Max Records" property.

Or, perhaps, delete records beyond the first 100 (or a different number if it changes in the next quarter).

 

[jrbarnett]

Use a form to input the number.

Once you have validated this user input as sensible, (ie they aren't entering alphanumeric text, zero or negative numbers or a number which it is greater than total number of records in the table), create a query (or modify existing one), save to the database and open it in query view.

John