Display multiple records on same table

[ErikRP]

Hopefully this is in the right forum - the issue is pretty equally queries and forms...

I have a table set up as follows: POLICY, DIVISION, PROCEDURE, FREQUENCY, ISSUE. There can be multiple divisions with multiple procedures for the same policy but only one frequency and issue per procedure. What I want to do is to select a policy and division and show only 3 specific procedures from this table on one form.

I'm not sure how best to go about this. I am kind of thinking that maybe I could try 3 queries and use sub-forms to display the results but I'm not sure if that is all that efficient (this is a LARGE table, 9.5 million records).

Hopefully I've given sufficient background information. Any help is much appreciated!

 

[jdjewell]

Select Top 3 Policy, Division, Procedure
From YourTable
Where Policy = YourPolicy and Division = YourDivision

Note: This will return the top three based on ordering by the primary key. To get a different result set, try using Order By and the field you want to order on.

Cheers!

 

[jdjewell]

...

P.S. I hope I understood your question.

 

[ErikRP]

I'm not sure if that's quite what I need, and that may be my fault. Here's a sample of the data:

POLICY, DIVISION, PROCEDURE, FREQUENCY, ISSUE
012345,001,Exam,Once per 6 months,Children not eligible
012345,001,Fluoride Treatment,Once per 6 months,Adults not eligible
012345,001,Test,Once per lifetime,None
012345,001,Polishing,Once per 9 months,Children under 18 not eligible

What I want is to pick out only the records for 'Exam', 'Fluoride Treatment' and 'Polishing' and have them display on the same form. I have tried writing a query that selects everything from the table where PROCEDURE equals one of these 3 codes, but obviously I get three separate rows. Is it possible to have those 3 separate rows appear on one form?

 

[jdjewell]

How about a tabular form?

Would displaying the data in a tabular form be of any help? You would be able to display all of the data that you want, but the data would be separated into rows on the form.

 

[ErikRP]

I think I have solved my problem. Or at least this particular one!

I've created 3 "sub" queries, each of which looks for a specific PROCEDURE. Each of the sub queries is its own subform on the main form. I don't know how I managed it but it works and is exactly what I want.

Thanks!