limiting results in record source

[notaloser]

hey there, i figured out that my problem was not what i previously thought it was.. i needed to change my record source rather than try to filter my report. thanks to the user who suggested this..

right now, this is the record source i have for my report Encounters.

SELECT ENCOUNT.* 
FROM ENCOUNT 
WHERE (((ENCOUNT.Type)="CLINIC-NEW")) 
UNION SELECT TOP 5 ENCOUNT.* 
FROM ENCOUNT 
ORDER BY ENCOUNT.Date ASC;

right now, the above record source ONLY returns records where ENCOUNT.Type = "CLINIC-NEW" .. rather than those PLUS the TOP 5 records...

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

and when i remove the TOP 5 from the second part of the union query, it returns all records where ENCOUNT.Type = "CLINIC-NEW" and all the other records (as it should).. but i need to limit "all the other records" being returned to "only 5 records" returned.. see?

can anyone help me? i have a feeling im really close

 

[notaloser]

can anyone help me? its real important for me

 

[Adeptus]

Maybe the top 5 are already included in the CLINIC-NEW?

Try UNION ALL
Also try putting brackets around the two UNIONed queries
(select blah blah) UNION ALL (select blah blah);

 

[notaloser]

well, there will only be one Clinic-New for each patient (and the report is filtered to 1 patient at a time).. so i know that isnt right.

however, the TOP 5 still doesn't work. it is either in and only shows Clinic-New records, or out and shows all

 

[Rich]

What defines the Top5 records?

 

[notaloser]

i was told that in order to select the record where Type = "Clinic-New" and the most recent 5 records using the Date field, i should create a union query using top.. am i headed in the right direction?

 

[Rich]

I would try the Top5 part on its own first and I'm sure the OrderBy clause has to go in the first part of the query

 

[notaloser]

thanks for all your help guys, i figured it out..

turns out, it was selecting the top 5 of ALL the records, and nothing came up because the records that i was testing with were not part of those top 5. what i needed to do is use WHERE Encount.HistNum = the filtered record.. thanks