Randomly select records for a report

[SherryLynn]

Hello all,

I have created a report which I will base on a query. I want to be able to select a number of records based on their File# (not an autonumber field). I would put criteria in the query, but there isn't any specific criteria to use. I will be picking a list of File#'s and there could be 3 one time and 10 the next. Is there a way to do this? And how would I go about it?

Thanks very much,
Sherry

 

[SherryLynn]

I thought maybe I should clarify a little bit. The records I am pulling are to go into an Agenda Report. I might have 30 files on my desk which I would go through. Based on what is needed at the meeting, I would choose say 10 of those files. I would want to be able to "punch in" those ten File#'s and have those results go into the Agenda report to create my Agenda. Any ideas would be greatly appreciated.

Thanks Sherry

 

[lespaul]

create your query:

SELECT * FROM TABLENAME WHERE [FILE#] IN ([Please enter the files to select])

when you open this query you will be prompted to enter the files. You can enter a single number or a commma separated list of file numbers. Make this query the source for your report. Opening the report will open the query which will prompt you for your file numbers.

That should do it!!

leslie

Leslie

 

[SherryLynn]

Hello,

I tried what you suggested, but when I use a comma to separate the file#'s, I don't get any records. If I type in only one file#, then the query works. I am posting the SQL so that maybe someone can help me figure this out. Thanks.

SELECT tblFamily.Family, tblFamily.File, tblFamily.Worker, tblFamily.ServiceTeam, tblFamily.Status, tblFamily.DateSeen, tblFamily.DateCreated, tblChildren.Name, tblChildren.DOB, tblOffender2.[LastName-O], tblOffender2.[FirstName-O], tblOffender2.[DOB-O], tblOffender2.Address, tblOffense.Relationship, tblOffense.Trauma, tblOffense.StatArea
FROM tblOffender2 INNER JOIN (tblFamily INNER JOIN (tblChildren INNER JOIN tblOffense ON tblChildren.ChildID = tblOffense.VictimID) ON tblFamily.FamilyID = tblChildren.FamilyID) ON tblOffender2.[Offender ID] = tblOffense.Offender
WHERE tblFamily.[File] IN ([Pease enter the file numbers]);

Thank you,
Sherry

 

[PHV]

I don't think you can use a list in a parameter input box.
Take a look here for a discussion why:

http://www.tek-tips.com/viewthread.cfm?SQID=885370&SPID=701&newpid=701&page=1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SherryLynn]

PHV,

Do you have any ideas on how I can accomplish what I have described in my initial post?

Thanks very much!
Sherry

 

[PHV]

I will be picking a list of File#'s
How and where ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SherryLynn]

Hello,

As I described (in my second post), depending on what files are required at the meeting, I choose those files, and each has a unique File#. They are random, based on who might be available to present the case at that particular meeting. I might have 20 files sitting waiting to go to the meeting at any given time. If I had a common "criteria" it would make it a lot easier. Is there no way to be able to type in the File#'s and have the query or whatever produce those records? I thought there might be some what to do it.

Congrats by the way on your "TipMaster" status.

Sherry

 

[JonFer]

Create a table that lists the file numbers and add a checkbox field called "Include". Link this table in your report's query and add a criteria that Include=True.

Then you just check those files in the new table that you want to include.

 

[SherryLynn]

Thanks JonFer. I am going to try that out and let you know how it works.

Sherry

 

[SherryLynn]

Hello JonFer,

Ok, I am trying to play around with this and running into a snag (due to my inexperience I'm sure). When you say "create a table that lists the file numbers...", how do you mean create it. Do I create a make table query pulling that field from my original table? If so, will I have to re-run this each time I want to do this?

Thanks for you response!
Sherry

 

[JonFer]

You can use a MakeTable query to get the filenumbers (Select Distinct FileNumber...) for the first run but then you would either add new Filenumbers manually or switch to an Append query in the future. If you use the MakeTable query, you'll have to go to the design view of the table and add the Include field that is a "Yes/No" type.