filtering a subreport

[notaloser]

hah, now that i have my report working, i need to limit one of the subreports..

i have a subreport called ENCOUNT, for encounters (or visits) to the doctor. in this subreport, there are the fields Date, ClinicType, and Notes.

what i need to do is limit the number of rows that are displayed to 5 or 6 like this

*if the ClinicType is Clinic-NEW, then display that record first, then display the MOST NEWLY added 5.
*if there is no record with ClinicType as Clinic-New, then just display the MOST NEWLY added 5

ive tried a few ways but i have no idea how to really start

thanks,
nota

 

[llkhoutx]

How are you going to determine the newest records? Are they date and time stamped? Entry order is not the same as indexing order.

I would display five records in a continuous form wihtout vertical scroll. Indexing will determine which five, unless you've otherwise ordered them in some manner.

I thjink that there's a "select" method for selecting a specific number of records. However, I've never used it.

 

[notaloser]

each record is dated in "30-Jan-06" a format like that, DD-Month-YY.. and even then, they are in order, because each day it is updated with the visits from that day.

and yes, i do know the SQL syntax, like "SELECT * FROM table ORDER BY field LIMIT 5 ASC", but that doesn't seem to work here.

and even then im not quite sure about where and how i need to work..

 

[pbaldy]

yes, i do know the SQL syntax, like "SELECT * FROM table ORDER BY field LIMIT 5 ASC", but that doesn't seem to work here.

Actually it would be:

SELECT TOP 5 ... ORDER BY Field

 

[notaloser]

well i work with PHP too (and that is correct in php/mysql webwork), and it may not be 100% the same. mind helping me on my problem?

 

[pbaldy]

I thought I did help. You said you knew the syntax but it didn't work. I gave you the correct syntax for Access. What else do you need help with?

 

[llkhoutx]

You say

each record is dated in "30-Jan-06" a format like that, DD-Month-YY.. and even then, they are in order, because each day it is updated with the visits from that day.

That has nothing to do with your table indexing.

 

[notaloser]

my bad there is an indexed ID column that is AutoNumber which is what i was referring to when i said in order

ive been informed that if i am unable to get it to do the Clinic-New records and the last 5, then i can just do the last 6 instead.. but i would like to try the first idea. any idea?

 

[pbaldy]

How about a UNION query, TOP 1 of the New and TOP 5 of the others? If there are no new, you'll just get the 5, otherwise 6.

 

[notaloser]

well i tried this, and it doesnt work. once i get the selection right, i still need to know how to limit the results to 1 or to 5 or 6. where do i place this query when its finished too, because it doesnt work in the Filter property of my Encounters report.

SELECT ENCOUNT.Type
FROM ENCOUNT
WHERE (((ENCOUNT.Type)="CLINIC-NEW"))
UNION
SELECT ENCOUNT.*
FROM ENCOUNT;

 

[pbaldy]

Answered here:

SELECT TOP 5 ... ORDER BY Field

 

[notaloser]

i keep getting syntax errors using TOP

 

[llkhoutx]

Your posted union isn't correct. First you select one field and then union it will all fields f the same table. I can't imagine what you're doing.

On another note, why isn't ENCOUNT.Type an index? It looks like it should be.

 

[notaloser]

why would i index ENCOUNT.Type? it is only the value of the type of visit (i.e. Clinic-New for the first visit).

i guess i am really mixed up here in my head. can someone redirect me in the direction of what i said i wanted in the first post?

 

[llkhoutx]

Pbaldy's suggest was correct.

How do you know what order Encount.Type is in? I'd wager that you don't.

 

[notaloser]

this report is filtered to 1 patient at a time. what i want to do is display the Clinic-New value first (because there is only 1 time you can visit the doctor and it be a "new" visit), and from then on, select the last 5 visits using the Date field.

how do i format this to work then? because i tried it and i was getting errors

SELECT TOP 5 ... ORDER BY Field