Query A specified date range for each customer

[Ronald Burke]

My database is designed to register complaints (for lack of a better word). I have a table for the initial complaint entry. I have a second table for follow-up notes. The criteria for the follow up notes is date of entry, person who entered and the note. I want to be able to run a query that will show me only the 3 most recent dates/notes for each registerd complaint. I tried to use a Min Max Totals query but the results I get are for the top 3 dates for the entire follow-up notes table. I need the top 3 dates for each complaint registered not overall. Any suggestions?

[ranman256]

make 2 queries:

make Q1 to get the dates, turn on Grouping summation, and use TOP 3 property:
select TOP 3 PersonID, Max(Date) from table where PersonID = forms!myform!cboPerson

Q2:
that gets you the recent Dates, so now join your data table to Q1 to get all the data:
select * from tData,Q1 where tData.PersonID = Q1.PersonID

if the PersonID is chosen from a form,

run Q2.

[Ronald Burke]

ranman256 thank you for the information. Unfortunately, I am not an advanced user so I'm not clear on how to proceed.

Complaint table has REQID (means request ID), Follow-Up Table as follow-Up date. If I turn on the grouping in the query for the date, I get a result that show the top 3 follow-up entries in the follow-up table. I want the system to tell me what the top 3 follow-up entries are for each REQID in the complaint table.

[ranman256]

thats more complicated. Without a lot of VB, you could do this:
a form with a listbox of all the ReqID.
select 1st one,

run Q2, where Q1 looks at the item selected

select TOP 3 ReqD, Max(Date) from table where ReqID = forms!myform!lstReq
Q2 would be an append query to add the results to a 'report' table,

move to next ReqID, run Q2 again
until all ReqID in list have run.

[ssanfu]

Need more info.
You said:

I have a table for the initial complaint entry. I have a second table for follow-up notes.

How are the two tables related?



See: TOP n records per group

[Ronald Burke]

Thank you everyone for your advice and assistnace. I was able to get it to work.