How to make report based on random selections from query

[MLS31612]

I'm trying to select records from a query to make a report, but I need the records to remain in a particular order (random) as I choose them.

This is a report for a small laboratory. We get a list of maybe 10-12 patients that a physician wants information about. Currently to make the report for the doctors, we have all of our information in an Excel sheet and we cut data from that sheet and paste it into another, with the names in the same order that they appear on the original list.

Can anyone think of a way to do this with Access? I have a query with every patient and relevant data listed. I just need to figure out how to select them and keep them in the correct order (it is a completely random order though-not alphabetical or numerical). I did try adding an extra column to "rank" each record manually as I select it, thinking I could then run another query and sort by those rankings, but my query is too complex to be updateable and I couldn't add in the numbers. I also tried a form with two listboxes and arrows between them to move records back and forth, but the code is really over my head. I tried to copy several examples but couldn't get any of them to work for me.

Any ideas as to what else I could try? Thanks in advance to anyone that can help me!

[June7]

Access needs a criteria to sort on.

How is this random? Why do records have to be in this selected order? You must have some rationale for this.

[MLS31612]

The list of names the Doctors give us has about 10 names in a particular order, that is not numerical or alphabetical (it has to do with a particular scoring system that they use) We can't change the order in which names fall on the list, and the end report needs have the data in the same order that it is given to us (so it isn't really random, but doesn't follow a typical sorting pattern. When choosing names to pull out of the main query, I can't choose them alphabetically or anything-I have to stick to the order from the doctor's list-so it is like I'm choosing them randomly from the query)

[MLS31612]

I've been continuing to look for solutions for this. Is there anyway to use something like a SELECT INTO or INSERT INTO to choose only one record at a time (with a button on a continuous form) and then "paste" into a temporary table?

[June7]

Yes, can do that and thought about mentioning it earlier but wanted to know more about the rationale. The temp table would need field that could be used to order the records. The Autonumber datatype would serve the purpose.

It would be easier if you could input the doctor's ranking into the primary table. Perhaps a field in the patient info table. This table should have unique record for each patient and by joining this table to other tables, that field could dictate the sort order. This ranking would be changed as needed to produce the desired output.

If you want to provide project for analysis, will look at. Make copy, remove confidential data, run Compact & Repair, zip if large, attach to thread or upload to fileshare site and post link to file.

[MLS31612]

Well, I did try putting the doctor's ranking in (by adding a column for numbers) but like I said, I wasn't allowed to enter them. But it is very possible that I wasn't doing it correctly (this is my first real database). The simplified database with a few fake entries is attached New Compressed (zipped) Folder.zip. The query I'm trying to get data out of is the "On Call Query". Thanks for the offer to take a look at it. I really welcome your input.

[June7]

You need to do two distinct tasks:

1. input a rank value in the Demographics table for the patients

2. produce a report that ranks the records by the above value

Cannot use the same query that will be the basis for report as the data entry vehicle. You are given a list of names in the specified order, so why not open a form bound to the Demographics table and enter values in Rank field (or go straight to the table if you want)? Include that field in the query. If all patients are not included in every report, run an UPDATE query to reset the Rank field to null or 0 before entering the new ranking. Use only the records that are not null or are greater than 0, whichever you prefer to go with.

I believe that On Call Query and its subqueries are by far the most complicated query structure I have yet encountered.

[MLS31612]

I believe that On Call Query and its subqueries are by far the most complicated query structure I have yet encountered.

Aren't they awful. I don't really know how else to do them though.
Thank you so much for working on this. Your suggestions make sense, and I will tackle them next week when I return to the lab.

[MLS31612]

June7, your suggestion works wonderfully. I was seriously overthinking how to do this. Thanks for your help.