I want a query to display a certain number of records. Example: I have a query that usually displays 100-200 records. I want a query so that the user specifies a number like 50, and the query will display the first 50 records in that query of 100-200. Also, I can not use VB code, it will have to be in SQL or access query parameters.
I'm not sure if you can use TOP keyword with a query parameter. I believe to do this, you need to create your sql on the fly. which involves either macro or VB.
Your other option could be instead of letting user to choose result number, you could create different queries with template numbers like 10, 25, 50, 100 and let user choose query from a list. thats all I can think.
What about this:
I have a user that enters in new info daily. I need that user to be able to run an update query that assigns 20% of those records to 5 different groups so that in the end, each days records (no matter how many) will be assigned evenly.
That was my original idea. But I don't think it would work though.
Query has 100 records
1st run-displays 20 records
2nd run-displays 16 records
3rd run-displays 13 records
4th run-displays 11 records
5th run-displays 8 records
My query is set up so that it displays all records with a certain field that are null. Once the query is ran and it updates the top 20% of the records, those will fall off of the query.
If there is another way around this, let me know
you know doing this without VB is really sweating . I assume you don't delete any records, instead you sign them proccessed and keep outside of the query next time.
I think I found a way but I need to cook dinner. if you can do it please leave a note here. if you don't I will answer in few hours.
alright you need 2 queries:
1st Query pulls up %20 of unmarked records, you can also use this one to show records going to be updated.
and 2nd Query is an update query, which uses 1st query as source and updates records as marked.
that should do the job.
1st query get list of %20 of uncompleted records, lets assume this is saved as Query1:
SELECT TOP 20 PERCENT ExampleID, ExampleField, Complete
FROM Table1 WHERE (((Complete)<>True));
2nd Query uses Query1 as source and updates [Complete] field to true:
Say I have 5 different people to assign accounts to and there are 100 records(which this number will flucuate daily)
My query shows all accounts that are unmarked with a persons name.
Once I run that second query, my 100 accounts goes down by 20 since it was marked with a persons name so it will show 80 now. The next time I run it, it will take 20% of the 80.
I will need to assign 20% accounts to each of the 5 people which would be 20.
So the first 20 accounts go to Bob
The next 20 to Suzy