Replies

For Age 0 and Category = “A”:

=MEDIAN(IF(($A$2:$A$140)*($B$2:$B$14=”A”),$A$2:$A$14))

as an array formula (confirm with Ctrl+Shift+Enter). Adjust the ranges as needed.

Thank you! I was missing the asterisk * and Excel didn’t like that.

Yes, I can use Access. I’ll try that. Thanks for the suggestion. I suspect I’ll visit you again in the Access forum.

Got it. Thanks. Erik

One last (?!) question. In the SQL — or elsewhere — can I specify that VisitDate should be in date format? Right now it comes out 39084 etc. I tried to specify date format in the query’s Design View (Properties, Format) but Date wasn’t one of the options available (general number, currency, Euro etc.)

Here is the SQL:

SELECT qryParticipantsFirstVisitChooseDateChooseSite.unitID, qryParticipantsFirstVisitChooseDateChooseSite.protocol, qryParticipantsFirstVisitChooseDateChooseSite.ParticipantID, qryParticipantsFirstVisitChooseDateChooseSite.EnrollmentDate, Int([FirstVisitDate]) AS VisitDate
FROM qryParticipantsFirstVisitChooseDateChooseSite
WHERE (((Val(DCount(“*”,”qryParticipantsFirstVisitChooseDateChooseSite”,”UnitID=” & Chr(34) & [UnitID] & Chr(34) & ” AND FirstVisitDate<=#" & Format([FirstVisitDate],"mm/dd/yyyy hh:nn:ss") & "#")))<=20))
ORDER BY qryParticipantsFirstVisitChooseDateChooseSite.unitID, Int([FirstVisitDate]);

Thanks,
Erik

A dirty trick is the best kind of trick! Thanks.

One good lesson for me is to remember the connection between “tie” and “rank”…

Thanks for the instructions. Database, stripped & zipped, is attached.

Hans–if you have time to look at it, my data is attached…disguised everything except the dates, but consistently.

Excel file with one worksheet of data per query. SQL of each query is in a text box on the respective worksheet. My question is why the second query yields 15 records and not 20.

With many thanks in advance,
Erik

If I can figure out how best to anonymize it (without destroying my problem!) I’ll post an example. But in any event I really appreciate your guidance thus far.

LOL, that was my first thought too. But no, I’ve rechecked the data and there were 119 unique participants.

Sorry, one more question:

When I modify qryParticipants to restrict to only one clinic, the second query returns only 15 records, not 20 (although the first query returns 119 records). Do you know why this is? Here is the modified qryParticipants:

SELECT tblVisits.unitID, tblVisits.ParticipantID, Min(tblVisits.CompletedVisitDate) AS FirstVisitDate
FROM tblVisits
WHERE (((tblVisits.CompletedVisitDate)>=#1/1/2007#))
GROUP BY tblVisits.unitID, tblVisits.ParticipantID
HAVING (((tblVisits.unitID)=”052-001″));

Thanks again–
Erik

That did it!

HansV, many thanks again for your time and magic.

Erik

CompletedVisitDate is Date/Time (as expected) but unitID is text — a typical value is 034-001, with the hyphen in the middle. Can the query handle text, or should I add a field for numeric IDs to my units table?

Thanks

Thanks — safe to say I’d have never come up with this on my own…

But there’s a problem. Working with my data, the first query returns 1914 results…but so does the second query. The problem seems to be somewhere in

Val(DCount(“*”,”qryParticipants”,”unitID=” & [unitID] & ” AND FirstVisitDate<=#" & Format([FirstVisitDate],"mm/dd/yyyy") & "#"))

because I exposed it as a field and the value was 0 every time (not 1-20 for each unitID as I expected…and as it comes back in your example). I know it's hard to diagnose without seeing my data, but do you know why that kind of thing might happen?

Thanks again
Erik

I was afraid you’d say that.

Thanks for the advice.

Please disregard this problem. My mistake.

Sincerely,
Erik “Think Before You Post” Schwab