Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random Records (2000)

    Hi is there a way to see how many records you have selected without a primary key? I have to pick a random 15 records out of a random 5 days of the month (I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Random Records (2000)

    I don't understand your first question, but here is a method to select 15 records from 5 days at random. It uses three queries:

    1. Create a query based on your table. Add only the date field to the query grid and set the Unique Values property of the query to Yes. If necessary, set criteria to select dates from the month you need. Save this query as (for example) qryDates.

    2. Create a query based on qryDates. Add the date field and a calculated column Rnd([DateField]) where DateField is the name of the date field. Clear the Show check box for the calculated column and set its sort order to Descending. Set the Top Values property of the query to 5. This query will return 5 random dates - different ones each time you run the query. Save it as (for example) qry5Dates.

    3. Create a query based on your table and on qry5Dates, joined on the date field. Add the fields from the table you need in the result, and a calculated column Rnd([AnyField]) where AnyField can be any number field (currency and date/time fields count as number fields too). Clear the Show check box for the calculated column and set its sort order to Descending. Set the Top Values property of the query to 15. This query will return 15 random records from 5 random dates - a different selection each time you run the query.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Records (2000)

    I'm not very good as explaining things. I open up the access table with the infomation. I pick a random 15 records out of over 300 records in the table for lets say June2,2005. I copy them into excel then go back into access and pick another random 15 records out of over 300 records from lets say June 6, 2005 copy and paste them into excel. I do this with 5 random days of a the month.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Random Records (2000)

    Keep the first and second query. Open the second one (qry5Dates) and note the 5 dates. These are the 5 dates you're going to use.
    Change the third query as follows:

    3. Create a query based on your table. Add the fields from the table you need in the result, and set the Criteria for the date field to [Enter Date] (this will become a parameter prompt). Aa a calculated column Rnd([AnyField]) where AnyField can be any number field (currency and date/time fields count as number fields too). Clear the Show check box for the calculated column and set its sort order to Descending. Set the Top Values property of the query to 15. This query will return 15 random records from the specified date.

    Run this query 5 times, and enter each of the dates returned by the first query in turn. Copy the records into Excel.

    If you need to do this frequently, there are ways to automate this using VBA code. Post back if you need that, preferably with details about the way the records for the 5 dates are being pasted into Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •